Database Reference
In-Depth Information
Figure 17-2.
Update (U) and exclusive (X) locks
As the first step, SQL Server acquires intent exclusive (IX) lock on the table (object) level. After that, it acquires
intent update (IU) locks on the pages and update (U) locks on the rows and then converts them to intent exclusive (IX)
and exclusive (X) locks. Finally, when we rollback the transaction all locks are released.
It is worth mentioning that update (U) locks behavior depending on the execution plan. In some cases, when we
are updating multiple records, SQL Server can decide to acquire update (U) locks on all rows first and convert them
to exclusive (X) locks after that. In the other cases, when, for example, we update only one row based on the clustered
index value, SQL Server can acquire an exclusive (X) lock without update (U) lock at all.
But the key point with update (U) locks is what happens when we run nonoptimized update. Let's try to update
the single row from the table based on the column that does not have any indexes. This is shown in Figure
17-3
.
