Databases Reference
In-Depth Information
Compatibility Matrix
Having looked at the list of lock modes that SQL Server uses, this section considers which of these
are compatible with each other — that is, which locks prevent other locks from being taken out, and
which ones are allowed. Two matrices of compatibility are shown — the i rst is explained, and the
second one you can use for reference. Table 6-3 illustrates the i rst matrix.
TABLE 6-3:
Sample Matrix of Compatibility
EXISTING LOCK MODE
IS
S
U
IX
SIX
X
Requested
Lock
Mode
IS
Y
Y
Y
Y
Y
N
S
Y
Y
Y
N
N
N
U
Y
Y
N
N
N
N
IX
Y
N
N
Y
N
N
SIX
Y
N
N
N
N
N
X
N
N
N
N
N
N
First, note the symmetry of this matrix. The labels “Existing Lock Mode” and “Requested Lock
Mode” could be swapped without any of the Ys or Ns changing. The fact that a shared lock is
incompatible with an intent exclusive lock is true whichever one is taken out i rst.
Next, look at the compatibility of the exclusive lock mode, the row and column marked with an X.
Remember that this is the lock mode used when data is actually being modii ed. It's not compatible with
any other lock modes. Therefore, if data is being modii ed, no other process can do anything
with that data. It can't read it, and it dei nitely can't change it.
At the other end of the matrix, you can see that the intent shared lock mode is compatible with
everything except the exclusive lock. This is the lock that is used on index pages and index objects,
where a shared lock has been taken out at a more granular level. There is no problem taking out a
lock of this type unless the resource itself is being changed. An intent exclusive lock is i ne though —
so if a single page of a table is locked with an exclusive lock, causing an object intent exclusive lock
on the table/index itself, then a different part of the table can still be read. An intent shared lock can
be taken out on the object despite the existence of the intent exclusive lock.
An intent exclusive doesn't prevent another intent exclusive lock from being taken out — two parts
of a table can be changed at the same time without getting in each other's way. However, if part of
a table is being changed, a shared lock cannot be taken out (remember, we're not talking about an
intent shared lock).
Search WWH ::
Custom Search