Today let us discuss more on SQL Server 20008/2005 Locks and concurrency control……
Locks are very essential to maintain concurrent data access without issues. Say you are trying to update some data row and someone else wants to read that data, to avoid phantom reads (inconsistent view of data or data which is not yet committed) a Exclusive lock is placed on the row. But it does not start nor end there. First thing when you issue a update statement is a lock is placed on database which is called intent exclusive (IX) then same IX on table with EX(Exclusive lock on table) once it available for you.
Some locks are compatible with each other like a simple select queries on data are compatible meaning multiples users can access same data concurrently if the data is available. This lock is called a Shared lock(S).
We can use DMV sys.dm_tran_locks to see the all locks placed in the instance level.
Shared Locks
Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.
To view this lock in the DMV sys.dm_tran_locks, WITH (HOLDLOCK) hint can be used.
Update Locks
Update (U) locks prevent a common form of deadlock.
When a DML statement is executed SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can’t modify the underlying data. It has to be converted to an exclusive lock before the modification takes place.
Example: A typical update pattern consists of a transaction reading a record, acquiring a shared (S) lock on the resource (page or row), and then modifying the row, 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. Otherwise, the lock is converted to a shared-mode lock.
You can also force an update lock with the UPDLOCK hint.
Exclusive Locks
Exclusive (X) locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive (X) lock.
Intent Locks
An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks 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 SQL Server 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.
Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema Locks
Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.
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.
Bulk Update Locks
Bulk update (BU) locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.
Conversion locks
Conversion locks are locks resulting from converting one type of lock to another. There are 3 types of conversion locks:
•Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock
•Shared with Intent Update (SIU). A transaction that holds a Shared lock also has some pages/rows locked with an Update lock.
•Update with Intent Exclusive (UIX). A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock.
SQL Server can lock these resources (listed in order of increasing granularity).
RID (Row identifier): Used to lock a single row within a table.
Key (Row lock within an index): Used to protect key ranges in serializable transactions.
Page ( 8 KB): data page or index page.
Extent: Contiguous group of eight data pages or index pages.
Table: Entire table, including all data and indexes.
DB: Database.
Lock compatibility matrix below shows which locks are compatible with other locks obtained on the same resource from microsoft site below:
simple one:
| IS | S | U | IX | SIX | X | |
| IS | Yes | Yes | Yes | Yes | Yes | No |
| S | Yes | Yes | Yes | No | No | No |
| U | Yes | Yes | No | No | No | No |
| IX | Yes | No | No | Yes | No | No |
| SIX | Yes | No | No | No | No | No |
| No | No | No | No | No | No | No |
More Detailed Lock compatibility matrix:
I will update more on Lock Escalations soon………