Lock escalation is the process of converting many fine-grain locks, such as a row, into fewer coarse-grain locks, such as a table. Using lock escalation reduces system overhead.
Microsoft SQL Server Compact 4.0 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold. In SQL Server Compact 4.0, lock escalation can occur from row to table or from page to table, but not from row to page. When the escalation occurs at the table level, no requests can be made for any lock lower than a table.
For example, when a transaction operates on rows from a table, SQL Server Compact 4.0 automatically acquires locks on those rows affected and puts higher-level intent locks on the pages and table which contain those rows. Any relevant index pages are also locked. When the number of locks held by the transaction exceeds its threshold, SQL Server Compact 4.0 tries to change the intent lock on the table to a stronger lock. For example, an intent exclusive (IX) lock would change to an exclusive (X) lock. After acquiring the stronger lock, all page- and row-level locks held by the transaction on the table are released.
Lock escalation occurs on a per-table basis when a request for a lock causes a specific lock escalation threshold to be exceeded. All sub-table level locks, regardless of type, are counted toward the threshold. The escalation threshold should only be considered as an approximate value, because any locks required by internal operations count toward that threshold. Escalation might occur earlier than expected.
If escalation is not possible because of a lock conflict, the transaction will continue and might try to escalate again later.
Intent locks, row locks, and page locks all count toward the escalation count, unless they are temporary table locks. When the total of intent locks, row locks and page locks on a specific table exceeds the escalation threshold, escalation occurs.
You can control the lock escalation per session by setting the lock escalation threshold, as the following code example shows:
SET LOCK_ESCALATION 1000;
This setting affects all tables in the database. The default is 100.