Sql-server – Why isn’t partition-level lock escalation the default

lock-escalationpartitioningsql serversql-server-2008

In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added – partition level.

However, this isn't automatically enabled for partitioned tables – by default, the table is set to skip partition locking and go right from row or page -> table. Why would this be the case? Is there a reason why I wouldn't want to switch all my tables from TABLE to AUTO so that they escalate locks to partition level instead of to table level?

Since the default is still TABLE, I figure there must be something I'm missing about the downside of AUTO.

Best Answer

BOL has answer -- It is not ON by default as it may increases the potential for deadlock.

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.

Related Solutions

Sql-server – Checking lock granularity

After comments on questions

You can query sys.indexes, looking at the allow_row_locks and allow_page_locks columns. Also, sys.tables has a column lock_escalation

I think that will allow you to find all non-default options about locks.

Sql-server – How to force the use of row locks

The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this:

CREATE TABLE dbo.LockTest
(
    col1    integer IDENTITY NOT NULL,
    col2    integer NOT NULL,
    col3    integer NOT NULL,

    CONSTRAINT PK_LockTest
        PRIMARY KEY CLUSTERED (col1)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_LockTest
ON dbo.LockTest (col2);
GO
ALTER TABLE dbo.LockTest SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX PK_LockTest ON dbo.LockTest SET (ALLOW_PAGE_LOCKS=OFF);

Using the clustered index as the access method:

-- Error 651:
-- Cannot use the PAGE granularity hint on the table "dbo.LockTest"
-- because locking at the specified granularity is inhibited.
SELECT col1
FROM dbo.LockTest WITH (INDEX(PK_LockTest), PAGLOCK);

Using the nonclustered index:

-- Succeeds: page locks allowed on index UQ_LockTest
SELECT col2 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Succeeds: UQ_LockTest also includes col1
-- NC indexes always include the clustering key
SELECT col1 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Fails: cannot retrieve col3 without touching the clustered index
SELECT col3 FROM dbo.LockTest AS lt WITH (PAGLOCK);

Note that disabling page locking can have unexpected side-effects, such as preventing index reorganization (since this process works at the page level):

-- Error 1943:
-- The index "PK_LockTest" on table "LockTest" cannot
-- be reorganized because page level locking is disabled.
ALTER INDEX PK_LockTest ON dbo.LockTest REORGANIZE;

-- Succeeds (page locking available):
ALTER INDEX UQ_LockTest ON dbo.LockTest REORGANIZE;

Best Answer

Related Solutions

Sql-server – Checking lock granularity

Sql-server – How to force the use of row locks

Related Question