Friday, May 20, 2005


The UPDLOCK locking hint is a very interesting one.
It prevents other connections from updating a record but allows other connections to still read the information. It assures you that your process will be the next in line for an exclusive lock. SQL Server actually uses update locks before acquiring an exclusive lock (eg. UPDATE with a WHERE clause). It happens so fast most of the times that they probably won't popup in the locking information you get to see.

To prevent conversion deadlocks (typical in a read and update transaction) it is a good idea to use this locking hint (you'll probably want a REPEATABLE READ or SERIALIZABLE isolation level for these kind of transactions too).

No comments: