CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Ian Boyd
Ian Boyd

Reputation: 256581

How do UPDATE locks prevent a common form of deadlock?

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?

What do i mean by that?

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.

Use an update lock to prevent the deadlock

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?

Upvotes: 8

Views: 3528

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

An update lock is used to find rows that are to be updated, so are used by DELETE and UPDATE statements. Once rows are found for Update then the lock is converted to an X lock.

See Kalen Delany Blog

Upvotes: 2

usr
usr

Reputation: 171178

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.

Upvotes: 7

Related Questions