Lock only one row in update (lock a row)

Question

Post reply

Lock only one row in update (lock a row)

abitguru

Ten Centuries

Points: 1170
More actions
March 25, 2013 at 4:40 am

#275545

Hello!!!
The question is (if it is possible without any workaround, like add a state column)
How can i achieve this... I understand that I can't lock a row with select statement, but can I achive this:
--TRANS 1 while a transation is executing an update statement modifing one row, lock this row. Nobody can select this row while transaction is running.
--TRANS 2 while TRANS1 is running I can update other ROWS (not row in TRANS 1)
--example 1
--TRANS 1 => update table set field = 1 where id = 1 (LOCK THIS ROW ONLY THE ROW!!!)
--TRANS 2 => select * from table where id = 1 =====> MEANWHILE TRANS 1 IS RUNNING, HAS TO FAIL BECAUSE TRANS 1 is updating...
--example 2
--TRANS 1 => update table set field = 1 where id = 1
--TRANS 2 => select * from table where id = 2 =====> OK !
--example 3
--TRANS 1 => update table set field = 1 where id = 1
--TRANS 2 => update table set field = 1 where id = 2 =====> OK ! no one is locking this row!!!
* I disable lock escalation on the table (LOCK_ESCALATION = DISABLE)
* No cluster index on the table.
Hope you understand, i try a lot of query hints but noone get the results I spected (rowlock, xlock) (updlock)
when I use hits I test the lock results with
use master
SELECT * from sys.dm_tran_locks where request_session_id in (55,60) order by request_session_id
and see a PAGE lock for the resuouce, trans id 55 and 60 both takes differentes rows,
for a more especific example i post the las example i try.
TRANS 55
begin transaction
update reque_pa with (rowlock) set MINIMO = 2 WHERE CEREQ = 1
--with (rowlock,updlock)
WAITFOR DELAY '00:00:10';
commit transaction
The waitfor is to simulate a more long transaction to force the lock.
TRANS 60
begin transaction
SELECT * FROM Reque_pa with (rowlock) WHERE CEREQ = 2
commit transaction
hope you can help me, thanks in advance.!!!

Viewing 15 posts - 1 through 15 (of 52 total)

You must be logged in to reply to this topic. Login to reply

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 1

You can use the WITH UPDLOCK clause in your update query.

There's a good thread on it here;

http://stackoverflow.com/questions/1921091/thread-safe-sql-transaction-how-to-lock-a-specific-row-during-a-transaction

abitguru Ten Centuries Points: 1170 More actions · Answer 2

Thanks McSQL

Will read the thread!

I try (with updlock) but dont work in my case, cause transaction 1 has a page lock that prevents transation 2 to takes a row

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 3

You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

ALTER INDEX <INDEX_NAME>

SET (

ALLOW_PAGE_LOCKS = OFF

)

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You want the ROWLOCK hint. Do note that just tells SQL to start with row locks, it can and will still escalate if need be. It will also still take the intent locks at page and table level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abitguru Ten Centuries Points: 1170 More actions · Answer 5

McSQL (3/25/2013)
You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;
ALTER INDEX <INDEX_NAME>
SET (
ALLOW_PAGE_LOCKS = OFF
)
GO

Will try with this, in unique index, I only set (LOCK_ESCALATION = DISABLE) on the table

GilaMonster (3/25/2013)
You want the ROWLOCK hint. Do note that just tells SQL to start with row locks, it can and will still escalate if need be. It will also still take the intent locks at page and table level.

GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

abitguru (3/25/2013)
GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)

You can. So you'd prefer SQL to run out of lock memory, fail any pending queries with an error (out of lock memory) or possibly even crash rather than escalating locks?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

McSQL (3/25/2013)
You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;
ALTER INDEX <INDEX_NAME>
SET (
ALLOW_PAGE_LOCKS = OFF
)
GO

I would recommend not touching the locking hints on tables (allow page locks, allow row locks or lock escalation) as they can have some side effects including various operations failing, SQL running out of lock memory or even more serious problems than that. There are very few good reasons to change the locking options on indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abitguru Ten Centuries Points: 1170 More actions · Answer 8

GilaMonster (3/25/2013)
abitguru (3/25/2013)
GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)
You can. So you'd prefer SQL to run out of lock memory, fail any pending queries with an error (out of lock memory) or possibly even crash rather than escalating locks?

I think its my mistake for not to explain me well (and my out of practice english)

I have a parameter table to number invoices for example, with around 20 records,

this records are independent each other, so if I read parameter number 1 I want noone can read this row. I know that this action is imposible in sql server (I research a lot), because it hasn't something like select for update...

Now I want to know if it is posible to lock one row meanwhile I am updating it. I see that a transaction that updates one row affects most of the records because SQL Server takes a PAGE lock.

I try varios ways to disable lock escalation in this particulary small table, but with no results. (LOCK_ESCALATION = DISABLE, ISOLATION LEVEL 3,4, Hints.)

I understand what you say GilaMonster, but I want only disable lock escalation in this table.

Am I explain myself right?

sorry if you dont understand my point, I'll try my best.

Thanks a lot!

abitguru Ten Centuries Points: 1170 More actions · Answer 9

Look at this image,

connection 57 has this query

update reque_pa with (updlock) set MINIMO = 2 WHERE CEREQ = 1

connection 58 has this query

SELECT * FROM Reque_pa WHERE CEREQ = 2

To complete query 2, query 1 has to finish (i put a wait.... to test it)

lock the locks in image, connection 57 makes a row lock!!! for every row I have in the table,

resouce description

1:1702673:0 (this is the row I am updating in query 1)

1:1702673:1

1:1702673:2

1:1702673:3

1:1702673:4

I can't explain why sql behaves this way..

Hope this helps!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

abitguru (3/25/2013)
this records are independent each other, so if I read parameter number 1 I want noone can read this row. I know that this action is imposible in sql server (I research a lot), because it hasn't something like select for update...

Definitely not impossible. Very easy actually.

Now I want to know if it is posible to lock one row meanwhile I am updating it. I see that a transaction that updates one row affects most of the records because SQL Server takes a PAGE lock.

SQL will take page locks if it estimates that it needs to lock enough of the table that page locks are cheaper than row locks. Probably because there's no useful index for the query and hence SQL has to read the entire table. Check your indexes.

I try varios ways to disable lock escalation in this particulary small table, but with no results. (LOCK_ESCALATION = DISABLE, ISOLATION LEVEL 3,4, Hints.)

The LOCK_ESCALATION = DISABLE option will absolutely disable lock escalation, no question on that. I suspect you're misunderstanding what lock escalation is.

Lock escalation is when SQL takes row or page locks to start and part way through running the query converts those to table locks because it's taking too many locks or because lock memory is getting low. LOCK_ESCALATION = DISABLE will prevent the conversion of locks to table locks and SQL will keep with the row or page locks it originally selected.

Now, maybe instead of asking possibly the wrong questions about locks, explain what it is that you're trying to do. It's entirely possible that there's an easier way...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abitguru Ten Centuries Points: 1170 More actions · Answer 11

Lock escalation is when SQL takes row or page locks to start and part way through running the query converts those to table locks because it's taking too many locks or because lock memory is getting low. LOCK_ESCALATION = DISABLE will prevent the conversion of locks to table locks and SQL will keep with the row or page locks it originally selected.

GilaMonster, yes I understand this concept you clearly explain.

Now, maybe instead of asking possibly the wrong questions about locks, explain what it is that you're trying to do. It's entirely possible that there's an easier way...

Two main questions, (that I post before, posible confused like you say)

1 - I understand that I can't lock (for selecting, updating) a row with a select statement. Am I right?

2 - Now assuming point 1 is true (I can't lock a row with a select statemenet) I want to lock a particulary row with an update statement.

I want to do that, to protect this row to be selected while the update commnad is running.

Scenario.

Query 1

use desarrollo
begin transaction
update reque_pa set MINIMO = 2 WHERE CEREQ = 1
WAITFOR DELAY '00:00:10';
commit transaction

Query 2

use desarrollo
begin transaction
SELECT * FROM Reque_pa WHERE CEREQ = 2
commit transaction

With this scenario I have to wait until query 1 finish to continue query 2, I want query 1 and query 2, to be independent because they are selecting diferents rows.

This is the reason I think to lock a row, not a page or table (and disable lock escalation)

Thanks GilaMonster you are very kind.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

abitguru (3/25/2013)
1 - I understand that I can't lock (for selecting, updating) a row with a select statement. Am I right?

No, you've done exactly that several times in this thread, that's what the UPDLOCK hint does, lock a row against selecting and updating with a select statement

Let me ask again, what are you exactly trying to do here? Not a makeshift example, what is the problem that you are trying to solve?

If you find SQL is defaulting to page locks when you think it should be taking row locks, as I mentioned before you should check your indexes as you probably don't have indexes to support the query, hence SQL has to read (and lock) the entire table, not the single row that it's returning.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abitguru Ten Centuries Points: 1170 More actions · Answer 13

Not a makeshift example, what is the problem that you are trying to solve?

I have a parameter table to number invoices for example, with around 20 records, this records are independent each other, so if I read parameter number 1 I want noone can read this row until transaction ends

sorry for not starts the thread in this way, My question is I can do this in SQL Server, Lock a particulary row.

Hope we understant eachother.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Cool, please post the table definition, the index definition and the code that contains that transaction (at least the begin tran part through to the selection of the parameter).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass