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.

Answer 2

Update (U) lock is automatically placed on the data in DB on UPDATE statement. The main task is to protect the data in the database from simultaneous changes with multiple transactions and to avoid deadlock.

Update statement consists of 3 parts: reading data, calculating new values, writing data. We can't apply Exclusive (X) Locks for the reading part. So Update locks are not really a separate kind of lock, but rather are a hybrid of SHARED and EXCLUSIVE locks.

Assume two processes were both searching for the same resource to modify (for example, the same customer row in the Customers table), using different access paths, and they could both reach the desired resource at the same time. If they both were acquiring SHARED locks on the data they were examining, they could both lock the resource they wanted to change, but before they made the modification they would have to convert their lock to an EXCLUSIVE lock. Since the other process would have a SHARED lock, no EXCLUSIVE lock could be granted. Each process would have a SHARED lock, and each would try to change it to an EXCLUSIVE lock, but neither could proceed because of the presence of the other. This is a deadlock situation, called a ‘conversion deadlock’.

If SQL Server uses UPDATE locks, a deadlock will NOT occur.

UPDATE locks are compatible with SHARED locks, but are not compatible with EXCLUSIVE locks or other UPDATE locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an UPDATE lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change.

Shared (S) Locks - occurs when the object needs to be read.

Exclusive (X) Locks - occurs to prevent other transactions to modify or access a locked object.