Design Locking and Granularity levels

Question 1

What does lock escalation always escalate to?

Answer 1

Table Lock

Question 2

What does lock escalation never escalate to?

Answer 2

page lock

Question 3

What happens if a transaction attempts to escalate a lock but is unable to because there is an incompatible lock on the table?

Answer 3

It attempts to acquire locks at its original more granular level (row, key, page) periodically while checking for escalated locks

Question 4

How can you disable lock escalation?

Answer 4

Enable trace flag 1211

Question 5

How does SQL Server determine which transaction to kill in a deadlock scenario?

Answer 5

Deadlock Priority

Rollback Cost

Question 6

What is Deadlock Priority?

Answer 6

The number given to a transaction to show it’s importance to complete the transaction

Question 7

What is Rollback Cost?

Answer 7

If two transactions have the same priority, then SQL determines the lowest cost to roll back and kills that session.