SQL Server 2008: Partition-level lock escalation details and examples
Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see
A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have a partitioned table with queries going against different partitions, then table-level escalation is a pain because the whole table is suddenly locked and concurrent queries against distinct partitions can't run. SQL Server 2008 gives the ability to escalate to a parttition lock, which won't affect the queries on the other partitions.
The lock escalation policy can only be set with ALTER TABLE after a table has been created, and the policy can only be set at the table level. The syntax is
ALTER TABLE TableName SET (LOCK_ESCALATION = TABLE | AUTO | DISABLE);
The options mean:
- TABLE – escalation will always be to the table level. This is the default.
- AUTO – escalation will be to the partition level if the table is partitioned; otherwise it will be to the table level
- DISABLE – escalation will be disabled. This does not guarantee that it will NEVER occur – there are some cases where it is necessary (Books Online gives the example of scanning a heap in the SERIALIZABLE isolation level)
The only way I could find to check what the escalation policy for a table is set to is to use the sys.tables catalog view:
SELECT lock_escalation_desc FROM sys.mytables WHERE name = 'TableName';
Let's try it out. Here's a script that creates a database with an example table with 3 partitions. The partition ranges are negative infinity to 7999, 8000 to 15999, 16000 to positive infinity.
CREATE DATABASE LockEscalationTest;GO
USE LockEscalationTest;GO
– Create three partitions: -7999, 8000-15999, 16000+CREATE PARTITION FUNCTION MyPartitionFunction (INT) AS RANGE RIGHT FOR VALUES (8000, 16000);GO
CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionFunctionALL TO ([PRIMARY]);GO
– Create a partitioned tableCREATE TABLE MyPartitionedTable (c1 INT);GO
CREATE CLUSTERED INDEX MPT_Clust ON MyPartitionedTable (c1)ON MyPartitionScheme (c1);GO
– Fill the tableSET NOCOUNT ON;GO
DECLARE @a INT = 1;WHILE (@a < 17000)BEGININSERT INTO MyPartitionedTable VALUES (@a);SELECT @a = @a + 1;END;GO
Now I'm going to explicitly set the escalation to TABLE and start a transaction that should cause lock escalation.
ALTER TABLE MyPartitionedTable SET (LOCK_ESCALATION = TABLE);
GO
BEGIN TRAN
UPDATE MyPartitionedTable SET c1 = c1 WHERE c1 < 7500;GO
We should be able to see the locks being held:
SELECT [resource_type], [resource_associated_entity_id], [request_mode],
[request_type], [request_status] FROM sys.dm_tran_locks WHERE [resource_type] <> 'DATABASE';GO
resource_type resource_associated_entity_id request_mode request_type request_status
————— —————————– ————– ————– —————-METADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTOBJECT 2105058535 X LOCK GRANT
Just as we expected – an X table lock. Trying any query against the table fails now. Now I'll rollback that transaction, set the escalation to partition-level and try again.
ROLLBACK TRAN;
GO
ALTER TABLE MyPartitionedTable SET (LOCK_ESCALATION = AUTO);
GO
BEGIN TRAN
UPDATE MyPartitionedTable SET c1 = c1 WHERE c1 < 7500;GO
SELECT [partition_id], [object_id], [index_id], [partition_number]
FROM sys.partitions WHERE object_id = OBJECT_ID ('MyPartitionedTable');GO
SELECT [resource_type], [resource_associated_entity_id], [request_mode],
[request_type], [request_status] FROM sys.dm_tran_locks WHERE [resource_type] <> 'DATABASE';GO
partition_id object_id index_id partition_number
——————– ———– ———– —————-72057594039042048 2105058535 1 172057594039107584 2105058535 1 272057594039173120 2105058535 1 3
resource_type resource_associated_entity_id request_mode request_type request_status
————— —————————– ————– ————– —————-HOBT 72057594039042048 X LOCK GRANTMETADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTOBJECT 2105058535 IX LOCK GRANT
Excellent – the object lock is now IX rather than X, and the X lock is at the partition (HOBT) level for partition 1 (see the bold highlighting to match the partition ID with the lock resource). (For an explanation of HOBTs, see my post Inside The Storage Engine: IAM pages, IAM chains, and allocation units.) So now we should be able to do something with another partition – let's see if we can cause another partition level X lock in another connection:
USE LockEscalationTest;
GO
BEGIN TRAN
UPDATE MyPartitionedTable set c1 = c1 WHERE c1 > 8100 AND c1 < 15900;GO
SELECT [partition_id], [object_id], [index_id], [partition_number]
FROM sys.partitions WHERE object_id = OBJECT_ID ('MyPartitionedTable');GO
SELECT [resource_type], [resource_associated_entity_id], [request_mode],
[request_type], [request_status] FROM sys.dm_tran_locks WHERE [resource_type] <> 'DATABASE';GO
partition_id object_id index_id partition_number
——————– ———– ———– —————-72057594039042048 2105058535 1 172057594039107584 2105058535 1 272057594039173120 2105058535 1 3
resource_type resource_associated_entity_id request_mode request_type request_status
————— —————————– ————– ————– —————-HOBT 72057594039107584 X LOCK GRANTHOBT 72057594039042048 X LOCK GRANTMETADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTMETADATA 0 Sch-S LOCK GRANTOBJECT 2105058535 IX LOCK GRANTOBJECT 2105058535 IX LOCK GRANT
Now we have two partition X locks, for partitions 1 and 2 (as expected – use the color coding above to match up the IDs), plus two table-level IX locks (one for each connection, as expected). Very cool!
Now I'm going to force a deadlock – by having each connection try to read a row from the other locked partition:
Connection 1:SELECT * FROM MyPartitionedTable WHERE c1 = 8500;
GO
Connection 2:SELECT * FROM MyPartitionedTable WHERE c1 = 100;
GO
Connection 2 succeeds but on connection 1 we get (as expected)
(local)\SQLDEV01(SQLHAVPC\Administrator): Msg 1205, Level 13, State 18, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
This illustrates a potential problem with this new mechanism – applications that used to rely on the blocking nature of X table locks may now exhibit deadlocks if partition-level escalation is turned on in production without any testing, this mode was specifically chosen NOT to be the default setting for new tables because some trial workloads exhibited deadlocks during testing. Don't just turn it on in production without testing – as with any other option or feature.