Lock Escalation: 5000 to 1?

Question

Lock Escalation: 5000 to 1?

Firemander

SSC-Addicted

Points: 412
More actions
April 21, 2016 at 11:44 am

#326587

If a row lock hits its memory threshold for locks, lets say in this case it is exactly 5000, and a table lock is created. I assume it drops all the other row locks, releasing the memory, and adopts a single table lock. Is this a correct assumption?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12314 More actions · Answer 1

No, when SQL Server attempts to escalate to a table lock, the escalation will fail if other processes are holding incompatible locks. It doesn't kill competing processes.

"Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. "

https://support.microsoft.com/en-us/kb/323630

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

That is true, but I think the OP may be asking when many lower-level locks are escalated to a higher-level lock (partition or object), whether those lower-level locks that are being escalated are released.

In that case the answer is yes, the lower level locks are released.

Here's an easy repro script:

SET NOCOUNT ON;

CREATE TABLE dbo.escalate (ID int);

INSERT INTO escalate

SELECT TOP 10000

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN

sys.all_columns ac2;

BEGIN TRANSACTION;

UPDATE escalate SET ID=1 WHERE ID<4000;

--Get lock count after updating 3999 rows

--to confirm that we currently hold many row locks

SELECT resource_type,

request_mode,

current_lock_count=COUNT(*)

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY resource_type, request_mode;

UPDATE escalate SET ID=1 WHERE ID<10000;

--Get lock count after updating 6000 more rows

--We have a new X lock on the object, and no more row locks

SELECT resource_type,

request_mode,

current_lock_count=COUNT(*)

FROM sys.dm_tran_locks

WHERE request_session_id=@@SPID

GROUP BY resource_type, request_mode;

ROLLBACK;

DROP TABLE escalate;

Cheers!

Chinnappan Balan SSC-Addicted Points: 461 More actions · Answer 3

Yes, you are right. Immediately after the Lock Escalation successful the other row locks will be release(Memory hold by the row locks).

To escalate locks, the Database Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), or row-level (RID) locks held by the transaction on the heap or index are released. If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.