Database Reference
In-Depth Information
Figure 23-9.
Deadlock due to lock partitioning (Step 2)
While this could happen with any object-level non-intent lock type, one of the most common scenarios is
partitioned table-related operations, such as partition switch or partition function alteration. Those operations
require schema modification (Sch-M) locks that often lead to deadlocks on busy systems when many sessions are
accessing the same object. Unfortunately, there is very little you can do about it. Lock partitioning cannot be disabled
through documented approaches. There is the undocumented trace flag T1229 that does the trick, however using
undocumented trace flags is dangerous and not recommended in production.
With lock partitioning in place, the best option that you have is implementing retry logic using
TRY/CATCH
around
DDL statements. A
SET DEADLOCK_PRIORITY
boost could also help reduce the chance that a DDL session will be
chosen as the deadlock victim.
In cases where you have a dedicated data access tier and full control around it, you can also use application
locks, which are not subject to lock partitioning to serialize access to the table. With such an implementation, all DML
queries would need to acquire shared (S) application lock while DDL code would use exclusive (X) application lock.
Obviously, this method introduces a fair amount of extra work for the implementation.
Information about lock partitions is available in the
sys.dm_tran_locks
DMV via the
resource_lock_partition
column and also in the
BigIntData1
column in the SQL Trace Locks events. It is also available in the deadlock graph.
