Enabling Partition Level Locking in SQL Server 2008

Author: Thomas Kejser
Reviewers: Juergen Thomas, Sanjay Mishra, Stuart Ozer, Lubor Kollar, Kevin Cox, Kun Cheng

In this blog, we will provide additional details on a well-hidden feature in SQL Server 2008 – partition-level locking. But before we illustrate the feature, here’s a bit of background about lock escalation and lock granularities.

Background

SQL Server has a lock manager that tracks which database objects are locked. Typically, a query will take locks at the row or page level, but after the numbers of locks become too large to manage, SQL Server will try to escalate the locks to the table level to preserve memory. Lock escalation can sometimes cause blocking or deadlock situations – so it is feature that DBAs should be aware of. You can trace lock escalation in SQL Server profiler with the Lock:Escalation event.

In SQL Server 2005, you can disable lock escalation completely, at the instance level, using trace flag 1211 – this is described in KB 323630. However, in SQL Server 2008, you get a new option: disabling lock escalation at the table level using the command:

ALTER TABLE SET (LOCK_ECALATION = AUTO | TABLE | DISABLE)

DISABLE mimics the behavior of Trace flag 1211. The TABLE setting is the default, to maintain backward compatibility with SQL Server 2005. However, consulting SQL Server Books Online, we see that there is an interesting behavior for the AUTO setting, when the table is partitioned:

“If the table is partitioned, lock escalation will be allowed to partition. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity.”

Lock Escalation on a Partitioned Table with the AUTO Setting

Let’s put the AUTO setting to the test with this partitioned table:

CREATE PARTITION FUNCTION pf_year(INT)
AS RANGE RIGHT FOR VALUES (2007,2008,2009,2010)

CREATE PARTITION SCHEME ps_year
AS PARTITION pf_year ALL TO ([PRIMARY])

CREATE TABLE Data (    
  [year] INT NOT NULL
, UpdateValue FLOAT NOT NULL
, PayLoad CHAR(200) NOT NULL
) ON ps_year([year])

-- Cluster the table (also test on heap)
CREATE CLUSTERED INDEX CIX ON Data(Year) ON ps_year(Year)

The attached file has a script that will populate this table with data. You need around 10,000 rows in each partition to make sure lock escalation will happen.

We can now run a statement like this to test the escalation behavior:

ALTER TABLE Data SET (LOCK_ESCALATION = AUTO) /* Vary this */
BEGIN TRAN
  UPDATE Data SET Payload = RAND()
WHERE Year = 2008 /* Touch only one partition */
  /* Query locks here */
COMMIT TRAN

On both cluster indexes and heaps we get the following results.

The HOBT granularity is a “Heap or B-tree”. This means that in the AUTO case, only the partition touched is exclusively locked. This means that other partitions can be read or updated concurrently, while the update on the year 2008 partition is running.

Impact of LOCK_ESCALATION Setting on the Locking Behavior in SERIALIZABLE Isolation Level

So far, we have seen how the AUTO setting will give you partition-level locking when lock escalation kicks in. This is particularly interesting in data warehouse scenario.

However, this is not the end of the story. There is an interesting side effect of the AUTO setting. Try turning the partitioned table into a heap:

DROP INDEX Data.CIX

Now, execute this statement on the heap:

ALTER TABLE Data SET (LOCK_ESCALATION = TABLE) /* default */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
 SELECT COUNT(*) FROM Data
WHERE Year = 2008 /* Touch one partition only */
/* Query locks here */
COMMIT TRAN

Output:

Notice the S-lock on the table (OBJECT). Check your profiler for Lock:Escalation event– no lock escalation happened – yet there is still a lock on the table level? What is going on here?

Notice we are asking for the SERIALIZABLE isolation level. Unlike a cluster index – where a range lock can be taken to ensure SERIALIZABLE reads – heaps have no order of their rows.  Hence, SQL Server realizes that a table-level lock is the only strategy feasible to enforce the isolation level. This is not lock escalation; it is a behavior of the optimizer under the given isolation level.

Now, try changing the LOCK_ESCALATION setting to AUTO or DISABLE. DISABLE will still take the table (TAB) level lock. But with the AUTO setting, a partition-level (HOBT) lock is taken. So even though there is this no lock escalation in this case, the LOCK_ESCALATION setting of the table still affects the locking behavior when the isolation level is SERIALIZABLE.

Summary

If you have a partitioned table with concurrent access to different partitions, you may want to experiment with the LOCK_ESCALATION = AUTO setting in SQL Server 2008. Because this setting enables SQL Server to use a finer grain of lock escalation, it may improve concurrency in your environment.

Test Locking of Partitions.sql