Database Reference
In-Depth Information
Figure 17-3.
Update (U) locks and nonoptimized queries
Because there are no indexes on
OrderNum
column, SQL Server needs to scan the entire table and acquire and
release update (U) locks on every row read. Even if in our case we only need to update the single row.
What will happen when multiple sessions try to obtain the locks on the same resources? That leads us to the lock
compatibility matrix, as shown in Table
17-1
.
Table 17-1.
Update (U) and exclusive (X) lock compatibility matrix
(IU)
(U)
(IX)
(X)
(IU)
Yes
No
Yes
No
(U)
No
No
No
No
(IX)
Yes
No
Yes
No
(X)
No
No
No
No