CODEWITHSUNDEEP
sql-serverlockingdeadlock
Avias
Avias

Reputation: 354

How to efficiently use LOCK_ESCALATION in SQL Server 2008

I'm currently having troubles with frequent deadlocks with a specific user table in SQL Server 2008. Here are some facts about this particular table:

  1. Has a large amount of rows (1 to 2 million)
  2. All the indexes used on this table only have the "use row lock" ticked in their options Edit: There is only one index on the table which is its primary Key
  3. rows are frequently updated by multiple transactions but are unique (e.g. probably a thousand or more update statements are executed to different unique rows every hour)
  4. the table does not use partitions.

Upon checking the table on sys.tables, I found that the lock_escalation is set to TABLE

I'm very tempted to turn the lock_escalation for this table to DISABLE but I'm not really sure what side effect this would incur. From What I understand, using DISABLE will minimize escalating locks from TABLE level which if combined with the row lock settings of the indexes should theoretically minimize the deadlocks I am encountering..

From what I have read in Determining threshold for lock escalation it seems that locking automatically escalates when a single transaction fetches 5000 rows..

What does a single transaction mean in this sense? A single session/connection getting 5000 rows thru individual update/select statements?

Or is it a single sql update/select statement that fetches 5000 or more rows?

Any insight is appreciated, btw, n00b DBA here

Thanks

Upvotes: 12

Views: 16334

Answers (2)

Mark
Mark

Reputation: 1554

I found this article after a quick Google of disabling table lock escalation. Although not a real answer for the OP I think it is still relevant for one off scripts and note worthy here. There's a nice little trick you can do to temporarily disable table lock escalation.
Open another connection and issue something like.

BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

as

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

from microsoft kb

Upvotes: 1

Roji P Thomas
Roji P Thomas

Reputation: 550

LOCK Escalation triggers when a statement holds more than 5000 locks on a SINGLE object. A statement holding 3000 locks each on two different indexes of the same table will not trigger escalation.

When a lock escalation is attempted and a conflicting lock exists on the object, the attempt is aborted and retried after another 1250 locks (held, not acquired)

So if your updates are performed on individual rows and you have a supporting index on the column, then lock escalation is not your issue.

You will be able to verify this using the Locks-> lock escalation event from profiler.

I suggest you capture the deadlock trace to identify the actual cause of the deadlock.

Upvotes: 9

Related Questions