Different Types of SQL Server Locks

Sometimes conflicts occurs in SQL Server due to concurrent transactions which can be resolved by using lock modes. Locking is a very common problem and its a logical issue which means you cannot solve it by adding extra hardware. SQL Server automatically manages and locks the resources at appropriate transaction level e.g. row level or data page level etc.

Here is the list of different Lock Modes:

• Shared (S) Locks
• Update (U) Locks
• Exclusive (X) Locks
• Intent Locks
  • Intent Shared (IS) Locks
  • Intent Exclusive (IX) Locks
  • Shared with Intent Exclusive (SIX) Locks
• Schema Locks
  • Schema Modification (Sch-M) Locks
  • Schema Stability (Sch-S) Locks
• Bulk Update (BU) Locks
• Key-Range Locks

Below is a List of Lock Compatibility Matrix

Credit: Lock Compatibility Matrix images referred from BOL.

Shared (S) Locks

Normally this lock is held for data read like SELECT statements. When this lock is held, other transactions can can read data but cannot modify locked data.

Update (U) Locks

Update lock prevents deadlock to occur when multiple sessions are updating resources or reading. It is combination of shared and exclusive locks.

Exclusive (X) Locks

Exclusive locks are used to lock data being modified by any transaction and hence prevents other concurrent transactions to modify data. Multiple DML operations cannot be performed to the same resource at the same time.

Intent (I) Locks

In Intent locks, a transaction notifies other transaction that it is intending to lock the data or intentionally has locked the data. This prevents other transaction from putting exclusive locks on the table.

Schema (Sch) Locks

It is used when an operation is in execution which is dependent on the schema of a table. Two types of schema locks are observed, Schema Stability lock (Sch-S) and Schema Modification lock (Sch-M).

Bulk Update (BU) Locks

Bulk update locks are used when bulk operations are performed and the TABLOCK hint is specified with import.

Key-Range Locks

Key-range locks protects the range of rows read by a query while using the serializable transaction isolation level. It prevents phantom reads, insertion or deletion into a record set used by a transaction.

We will see each locks in detail with live practical videos in next articles.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]