There are two scopes to this discussion - Lock modes & Lock granularity.
But before it, we begin with a prelude to Lock Escalation.
The process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention, is known as
Lock Escalation.
As the Database Engine acquires low-level locks, it also places intent locks on the objects containing the lower-level objects:
1. When locking rows or index key ranges, the Database Engine places an intent lock on the pages containing the rows or keys.
2. When locking pages, the Database Engine places an intent lock on the higher level objects containing the pages. In addition to placing an intent object (OBJECT) lock on the table, intent heap or B-tree (HOBT) locks are requested on:
a.The nonclustered index if the pages are nonclustered index pages.
b. The clustered index if the pages are clustered index pages. This includes data pages in tables that have a clustered index.
c. The heap of data pages if the pages are data pages in a table that does not have a clustered index.
Lock escalation is initiated at either of these times:
1. When a single Transact-SQL statement acquires 5,000 locks on a single table or index.
2. When the number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
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 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 the 5,000 row locks in TableB, it attempts to escalate all locks held by the session in TableB. It also attempts to escalate all locks held by the session in 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.
1. The simplest and safest way to prevent lock escalation is to keep transactions short and to reduce the lock footprint of expensive queries so that the lock escalation thresholds are not exceeded.
Break up large batch operations into several smaller operations.
2. Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks.
3. Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN
This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries
May disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server.
Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks.
To reduce the amount of locking by:
1. Using an isolation level that does not generate shared locks for read operations.
2. READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database option is ON.
3. SNAPSHOT isolation level.
4. READ UNCOMMITTED isolation level. This can only be used for systems that can operate with dirty reads.
Using the PAGLOCK or TABLOCK table hints to have the Database Engine use page, heap, or index locks instead of row locks. Using this option, however, increases the problems of users blocking other users attempting to access the same data and should not be used in systems with more than a few concurrent users.
You can also use trace flags 1211 and 1224 to disable all or some lock escalations.
Lock Modes
1. Shared (S)
Used for read operations that do not change or update data, such as a SELECT statement. No other transactions can modify the data while shared (S) locks exist on the resource.
2. Update (U)
Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.
3. Exclusive (X)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.
4. Intent
Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Intent locks serve two purposes:
To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.
For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.
5. Schema
Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Schema modification (Sch-M) locks are used when performing a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time the schema modification (Sch-M) lock is held, concurrent access to the table is prevented. This means that all operations outside of the schema modification (Sch-M) lock will be blocked until the lock is released.
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.
6. Bulk Update (BU)
Used when bulk copying data into a table and the TABLOCK hint is specified.
7. Key-range
Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.
Now the granularity:
1. RID - A row identifier used to lock a single row within a heap.
2. KEY - A row lock within an index used to protect key ranges in serializable transactions.
3. PAGE - An 8-kilobyte (KB) page in a database, such as data or index pages.
4. EXTENT - A contiguous group of eight pages, such as data or index pages.
5. HOBT - A heap or B-tree. A lock protecting an index or the heap of data pages in a table that does not have a clustered index.
6. TABLE - The entire table, including all data and indexes.
7. FILE - A database file.
8. APPLICATION - An application-specified resource.
9. METADATA - Metadata locks.
10. ALLOCATION_UNIT - An allocation unit.
11. DATABASE - The entire database.
