Database Reference
In-Depth Information
Key points here are:
•
Intent (IU/IX) locks are compatible with each other. This means that multiple sessions can
have intent locks on the object/page levels simultaneously.
•
Exclusive (X) locks are incompatible with each other. This means that multiple sessions
cannot hold exclusive (X) locks on the same resource. For example, multiple sessions cannot
update the same row simultaneously.
•
Update (U) locks are incompatible with each other as well as with exclusive (X) locks. This
means that the session cannot acquire update (U) lock on the row that has been updated by
another session and held exclusive (X) lock.
The last point leads us to the one of the typical blocking scenarios. Imagine we have the session that
held exclusive (X) lock on the single row. In another session we are trying to update a different row running a
nonoptimized update statement that introduces the scan. SQL Server would acquire update (U) lock on every row it is
scanning and eventually would be blocked trying to read the row with exclusive (X) lock held. It does not matter that
we want to update a completely different row—SQL Server still need to acquire update (U) lock on the rows it scan to
evaluate if they need to be updated.
In every system, we can separate query activities into the two separate groups: writers and readers. Writers are
the queries that modify the data, such as
INSERT
,
UPDATE
,
DELETE
, and
MERGE
statements. Readers are the queries that
read the data—basically SELECT statements. Also, with the readers, we have another lock type in the game:
shared
(S) lock
. As you can guess by the name, multiple sessions can acquire and held shared (S) lock on the same resource
simultaneously. You can see the shared locks in Figure
17-4
.
Figure 17-4.
Shared (S) locks
In this example, we can see two different sessions selecting (reading) the same row. Both sessions acquired
shared (S) locks on the database, intent shared (IS) locks on the table, page (1:955), and shared (S) locks on the row
without blocking each other.
Let's add shared (S) locks to our compatibility matrix, as shown in Table
17-2
.
