You manage a database, Prod, on an instance of SQL Server 2008. You have a partitioned table, TransDetails, which was defined using the following Transact-SQL:
CREATE PARTITION FUNCTION PFunction1 (int)AS RANGE LEFT FOR VALUES (2000, 3000);
CREATE PARTITION SCHEME PScheme1 AS PARTITION PFunction1 TO (fg1, fg2, fg3);
CREATE TABLE TransDetails
(
TransID int,
Description varchar(50),
Region varchar(20),
Date datetime,
TypeID int,
EmpID int,
Status bit
)ON PScheme1(TypeID);
You open a session and execute the following Transact-SQL:
ALTER TABLE TransDetails
SET (LOCK_ESCALATION = TABLE);
GO
BEGIN TRANSACTION
UPDATE TransDetails
SET TypeID = TypeID + 1000
WHERE TypeID > 4000;
GO
Which type of lock will be acquired if lock escalation occurs?
A. an Exclusive (X) lock on the TransDetails table
B. an Update (U) lock on the TransDetails table
C. a partition-level lock on one of the TransDetails table’s partitions
D. an Intent Exclusive (IX) lock on the TransDetails table
Correct Answer: A
Explanation/Reference:
Currently, an Exclusive (X) lock will be acquired on the TransDetails table if lock escalation occurs. In this scenario, you issued an ALTER TABLE statement that set LOCK_ESCALATION for the TransDetails table to TABLE, which is actually the default. Normally, table-level lock escalation is fine. However, with partitioned tables, you may not want to lock the entire table, but only a single partition, such as the partition containing the rows being updated in this scenario. This would allow queries against other partitions in the table to execute successfully without being blocked. By default, SQL Server will escalate locks to the table level as needed.
However, to override this default behavior, or to disable lock escalation altogether, you can set the LOCK_ESCALATION setting for a table. The LOCK_ESCALATION setting can have one of the following values:
TABLE: Lock escalation is performed to the table level, which is the default.
AUTO: Lock escalation is performed to the table level for non-partitioned tables, and to the partition level for partitioned tables.
DISABLE: Lock escalation is disabled, and lock escalation does not typically occur. SQL Server only escalates locks in specific situations where it is absolutely required to ensure data integrity.
In this scenario, you could instruct SQL Server to escalate locks to the partition level only using the following statement:
ALTER TABLE TransDetails SET (LOCK_ESCALATION=AUTO);
Doing so would prevent lock escalation to the table level, and allow other queries to execute successfully on other unaffected partitions of the table. To identify the LOCK_ESCALATION setting for a table, you can query sys.tables. For example, the following statement would display the current LOCK_ESCALATION setting for the TransDetails table:
SELECT lock_escalation_desc FROM sys.tables WHERE name = ‘TransDetails’;
All of the other options are incorrect because an exclusive lock will be held on the TransDetails table because it is a partitioned table and the LOCK_ESCALATION setting is set to escalate locks to the table level.
