Database Reference
In-Depth Information
■
as you will observe, in order to be granted, a lock needs to be compatible with all of the locks on that
resource—granted or not.
Important
■
the first scenario, when the third session ran in
READ COMMITTED
transaction isolation level and did not acquire
the lock on the resource, can be considered an internal optimization. In some cases, SQL Server still acquires another
shared (S) lock on the resource in reaD CoMMItteD mode, even if there is another shared (S) lock held. In such a case,
the query would be blocked similar to the
REPEATABLE READ
transaction isolation level.
Note
Lock Partitioning
The behavior we just observed means that lock requests are serialized and requests on the same object should not
deadlock each other. Unfortunately, there is another factor that complicates matters. When a system has 16 or more
logical processors, SQL Server starts to use a technique called
lock partitioning
. This term is a bit confusing as it has
nothing to do with table partitioning nor lock escalation. When lock partitioning is enabled, SQL Server starts to store
the information about locks on a per-scheduler (logical CPU) basis. In this mode, intent shared (IS), intent exclusive (IX),
and schema stability (SCH-S) locks are acquired and stored on a single partition based on the CPU (scheduler) where
the batch is executing. All other lock types need to be acquired on all of the partitions. This does not change anything
from a lock compatibility standpoint. When a session needs to acquire exclusive (X) table lock, for example, it would
go through all of the lock partitions and be blocked if any partition held an incompatible intent lock on
the table.
There are two consequences about which you need to be aware:
First, SQL Server needs more memory to store lock information. Non-partitioned locks are stored separately in
every partition and if, for example, a system has 20 CPUs, it would maintain 20 lock structures instead of just one.
All lock types that can be acquired on the row level are non-partitioned.
The second issue is more complicated. Lock partitioning increases the chances of deadlocks when object-level
locks are involved.
Let's look at the example and assume that the first session updates the row in the system that uses lock
partitioning. If this batch is executing on CPU 2, the session acquires an intent exclusive (IX) table lock, which is
partitioned and stored on CPU 2 only. It also acquires row-level exclusive (X) lock, which is not partitioned and is
stored across all CPUs. (I am omitting page-level intent locks again for simplicity sake.)
The second session is trying to alter the table, and it needs to acquire the schema modification (Sch-M) lock. This
lock type is non-partitioned, so the session needs to acquire it on every CPU. It successfully acquires and holds the
locks on CPUs 0 and 1, and it is blocked on CPU 2 due the lock's incompatibility with the intent exclusive (IX) lock.
Figure
23-8
illustrates this condition.
