Written By: Guy Glantser 18/11/2017
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):
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 tries 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: