Locking Types

snyktp

Hi,

Under what circumstances you would use; page locking, record locking, table locking ?

Thank you

kriki

You don't.

It is SQL Server that decides on the locking method and you have very little control over it.
The only control you have is in File=>Database=>Alter=>tab advanced=>"Always rowlock".

snyktp

Hi ,
First I want to say thank you for answer ...
But, According to what circumstances does SQL decide to use which locking?

kriki

There are no really 'hard' rules.
But SQL generally starts with recordlocks on a table. If you lock more records on the table, at a certain point SQL decides that it is better to do page-locks. If you even do more lock on the table, SQL might decide to lock the whole table. The same goes for indexes.

SQL decides to do that when it thinks it is better for performance.

Each lock (record-,page-,table-lock) uses some memory, so if SQL uses too much memory for recordlocks it goes for page-locks uses less memory and tablelocks even less.

snyktp

That is a great explanation for me... Thank you so much

bbrown

As stated above you have little to no control over how and when SQL escalates locks. But SQL does have criteria on how and when it escalates locks. It may appear random but it's not really.

(the following is random excerpts from BOL)

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks

Assuming lock escalation is not disabled, escalation will occur as follows:

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.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired

--
Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table or index, or, if the table is partitioned, a single reference of a table partition or index partition. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.

Lock escalation only occurs for tables that have been accessed at the time the escalation is triggered. Assume that a single SELECT statement is a join that accesses three tables in this sequence: TableA, TableB, and TableC. The statement acquires 3,000 row locks in the clustered index for TableA and at least 5,000 row locks in the clustered index for TableB, but has not yet accessed TableC. When the Database Engine detects that the statement has acquired at least 5,000 row locks in TableB, it attempts to escalate all locks held by the current transaction on TableB. It also attempts to escalate all locks held by the current transaction on TableA, but since the number of locks on TableA is < 5000, the escalation will not succeed. No lock escalation is attempted for TableC because it had not yet been accessed when the escalation occurred.

---
Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:

If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 40 percent of the memory used by the Database Engine, excluding AWE memory. The data structure used to represent a lock is approximately 100 bytes long. This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.


If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.


The Database Engine can choose any active statement from any session for escalation, and for every 1,250 new locks it will choose statements for escalation as long as the lock memory used in the instance remains above the threshold.

----