The SQL Server books online say that UPDATE
locks prevent a common form of deadlock:
Update Locks
Update (U) locks prevent a common form of deadlock.
How do they prevent a common form of deadlock?
A common form of deadlock is when two processes attempt to escalate from a Shared (S) lock (i.e. read lock) to a Exclusive (X) lock:
Process A Process B
======================== ========================
Acquire Shared lock
Acquire Shared lock
Attempt to escalate to X
Escalation waits on B
Attempt to escalate to X
Escalation waits on A
Deadlock. Both processes are waiting on each other.
This is all explained in the BOL:
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.
The Books Online doesn't explain how the update (U) lock prevents this common form of deadlock, all they do say is:
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.
The description is incomplete. If you look at the phrase "only one transaction can obtain an update (U) lock to a resource at a time". That is no different from an exclusive (X) lock - only one transaction can obtain an exclusive (X) lock to a resource at at time. But lets try to figure it out anyway:
First what we assume is a normal update process:
So now add a second process
Process A Process B
======================== ========================
Acquire Shared lock
Acquire Shared lock
Attempt to escalate to U
Escalation waits on B
Attempt to escalate to U
Escalation waits on A
Deadlock. Both processes are waiting on each other.
How does an update (U) lock prevent a common form of deadlock?
Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.
An UPDATE lock is a read lock which means "I intend to update this row/page/table". Readers are not blocked by this lock, but only one process can hold an UPDATE lock on a resource. please simulate a scenario which show where update lock play a key role because we know sql server place a lock when update data.
Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it.
The description is incomplete. If you look at the phrase "only one transaction can obtain an update (U) lock to a resource at a time". That is no different from an exclusive (X) lock - only one transaction can obtain an exclusive (X) lock to a resource at at time.
U-locks are compatible with S-locks which is not the case for X-locks. This means that while the rows to be written to are determined (using U-locks), other readers are still allowed.
So now add a second process...
The misunderstanding here is, that writers upgrade from S to U. This is not the case. They use U from the start. They upgrade from U to X later, but that has no meaning regarding deadlocks in this case.
To make this more clear: Let's assume we run the following statement:
UPDATE T SET SomeCol = 1 WHERE (ID BETWEEN 1 AND 2) AND (SomeOtherCond = 1)
Assume, that this is executed using a range scan on the clustered index on ID
, and that SomeOtherCond = 1
is only true for the row ID = 2
. This will get you U-locks for both rows, and an upgrade to X for the row with ID = 2
. The U-lock for row ID = 2
will be released early.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With