What Update Locks Use For

Question

I'm reading Understanding Locking in SQL Server. But I don't quite understand the purpose of update locks.

Details description as below:

Update Locks

Update (U) locks prevent a common form of deadlock. A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.

Consider below two transaction(both transaction execute at Isolation Level Repeatable Read in order to hold S lock during transaction):

execute below SQL in TRAN1.

BEGIN TRAN
SELECT BrandName FROM dbo.Brand WHERE BrandId=2

now, TRAN1 grant S lock for RID

execute below SQL in TRAN2

 BEGIN TRAN
 SELECT BrandName FROM dbo.Brand WHERE BrandId=2

now, TRAN2 grant S lock for same RID resource as TRAN1

execute below SQL in TRAN1

UPDATE dbo.Brand SET BrandName='YBrand' WHERE BrandId=2

now, TRAN1 S lock convert to U lock and the U lock wait TRAN2 S lock release to convert to X lock

execute below SQL in TRAN2

UPDATE dbo.Brand SET BrandName='ZBrand' WHERE BrandId=2

Then deadlock occurs.

Up deadlock is exactly as description as what U lock use for prevent. But deadlock still occurs.

So my question is: what U lock different with X lock? And which situation make it can prevent deadlock instead of use X lock?

Answer 1

The UPDATE operation is a two-step process:

1. first the existing value is read under with a (U) (update) lock

2. and then that lock is converted into an exclusive (X) lock to write back the new (updated) value.

Because of your REPEATABLE READ isolation level, and because you've arranged your statements like this, yes, you will run into a deadlock. But I don't really understand what this has to do with the update lock... (it's really just because you've arranged your code like this and because you're using REPEATABLE READ).

The main "benefit" of the (U) lock is that other (S) shared locks are still possible in that time. E.g. while one transaction reads the value to be updated with a (U) lock, another transaction can read the same value with a (S) shared lock in a SELECT (this doesn't work if you have an exclusive (X) lock, e.g. when you do a DELETE)

If you had two transactions that both just do the UPDATE alone (no SELECT with REPEATABLE READ) - then the (U) lock taken by the first transaction would prevent the second transaction from also reading that value (because (U) locks aren't compatible - if TRAN1 has an update lock on a row, TRAN2 cannot get it's update lock). This makes the "read existing value, update it, write it back" an atomic operation, and prevents two transactions from starting the update process on the same row at the same time.