The most common types of locks in SQL Server are the SHARED (S) lock and the EXCLUSIVE (X) lock. The former is used to lock resources (e.g. rows, keys, tables) for read, and the latter is used to lock resources for write operations. The first is called SHARED, because multiple owners can share the same resource for read. In other words, multiple users can read the same data at the same time. The second is called EXCLUSIVE, because a resource can only have a single EXCLUSIVE lock at any given time, so only one user can perform a write operation against a resource. Other users will have to wait.

When a user executes an UPDATE statement against a row, the user is granted EXCLUSIVE lock on that row. If you monitor locks in your system, then you also might encounter UPDATE locks. Why do we need UPDATE locks, if we already have EXCLUSIVE locks for UPDATE operations?

In order to update a row, SQL Server first needs to find that row, and only then it can perform the update. So every UPDATE operation is actually split into two phases – first read, and then write. During the read phase, the resource is locked for read, and then it is converted to a lock for write. This is better than just locking for write all the way from the beginning, because during the read phase, other sessions might also need to read the resource, and there is no reason to block them until we start the write phase. We already know that the SHARED lock is used for read operations (phase 1), and that the EXCLUSIVE lock is used for write operations (phase 2). So what is the UPDATE lock used for?

If we used a SHARED lock for the duration of the read phase, then we might run into a deadlock when multiple sessions run the same UPDATE statement concurrently.

Here is the scenario in a chronological order (this is not how SQL Server works):

1. Session #1 runs an UPDATE statement in order to update some row.
2. Session #1 acquires a SHARED lock while searching for the row and reading it.
3. Session #2 runs the same UPDATE statement.
4. Session #2 also acquires a SHARED lock on the same row. It can share the resource with session #1, because they are currently both reading, and not writing.
5. Session #1 tries to convert the lock to an EXCLUSIVE lock, but it is blocked by the SHARED lock of session #2. Remember that in order to be granted an EXCLUSIVE lock, it should be the only lock.
6. Session #2 also tries to convert its SHARED lock to EXCLUSIVE lock, but it is blocked by session #1 for the same reason.
7. Deadlock!

So in order to prevent this deadlock scenario, the UPDATE lock is used instead of the SHARED lock during the read phase of an UPDATE operation. An UPDATE lock and a SHARED lock can still share the same resource, just like two SHARED locks can, but two UPDATE locks can’t share the same resource at the same time. So when a session reads a resource as part of an UPDATE statement (phase 1), other sessions can still read the resource as part of a SELECT statement. But if another session tried to update the same resource, it has to wait, and it can’t even start the read phase. This way, the deadlock is prevented.

Here is the scenario again, with the UPDATE lock. This is what really happens in SQL Server:

1. Session #1 runs an UPDATE statement in order to update some row.
2. Session #1 acquires an UPDATE lock while searching for the row and reading it.
3. Session #3 runs a SELECT statement against the same row, and acquires a SHARED lock while reading the row.
4. Session #2 runs the same UPDATE statement as session #1.
5. Session #2 also tries to acquire an UPDATE lock on the same row. It is blocked by the UPDATE lock of session #1.
6. Session #1 tries to convert the UPDATE lock to an EXCLUSIVE lock, and succeeds.
7. Session #1 commits the transaction and releases the locks.
8. Session #2 acquires the EXCLUSIVE lock, and completes the UPDAE operation successfully.
9. Everyone is happy!

The post What is the Role of the UPDATE Lock in SQL Server? appeared first on Madeira Data Solutions.