Pessimistic lock in T-SQL

Question

If i SELECT a row for updating in MS SQL Server, and want to have it locked till i either update or cancel, which option is better :-

1) Use a query hint like UPDLOCK 2) Use REPEATABLE READ isolation level for the transaction 3) any other option.

Thanks, Chak.

Answer 1

Neither. You almost never want to hold a transaction open while your user is inputting data. If you have to implement a pessimistic lock like this, people generally do it by rolling their own functionality.

Consider the full ramifications of what you are doing. I once worked on a system that implemented locking like this. You often run into tons of stale locks, and your users get confused and angry very quickly when you foist this on them. The solution for us in our case was to remove this locking functionality entirely.

Answer 2

If you're waiting on another resource such as an end-user, then take Dave Markle's advice and don't do it.

Otherwise, try the following T-SQL code:

BEGIN TRAN

SELECT *
FROM   authors AU
WITH   (HOLDLOCK, ROWLOCK)
WHERE  AU.au_id = '274-80-9391'

/* Do all your stuff here while the row is locked */

COMMIT TRAN

The HOLDLOCK hint politely asks SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint politely asks SQL Server to lock only this row rather than issuing a page or table lock.

Be aware that if lots of rows are affected, either SQL Server will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing.

Answer 3

just note that despite using ROWLOCK SQL Server might choose to still take a full page lock if it deems needed.