Search code examples
sql-serverlockingtransactionspessimistic

Pessimistic lock in T-SQL

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.

Solution

  • 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.