If you have read my previous post "SQL Server: Locking cost, concurrency cost and concurrency issues [Discussion on Transactions - III]", then you have seen lock escalation. Lock escalation is a process that converts fine-grain-locks (row level, page level locks) to fever coarse-grain-locks (table level locks). This increases concurrency cost and reduces the locking cost. This week Brain Basher is based on this, and it is;
What makes Lock Escalation triggered?
Generally, it gets triggered when a single statement obtained at least 5000 locks. If it is not successful, then it attempts the same for every 1,250 new locks. This happens only when Lock Escalation is not disabled on the table by using ALTER TABLE SET LOCK_ESCALATION option. If you need a sample code to see this, please visit above link.
As per MSDN;
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:
- A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
- A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
- The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
For more info, visit: https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx
No comments:
Post a Comment