Blocking resolutions

Once you’ve analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few techniques you can use to do this:

Use a covering index on the contended data If the query of one of the processes can be satisfied using a covering index, then it will prevent the process from requesting locks on the contended resource
Optimize the queries executed by blocking and blocked SPIDs E.g. break UPDATE/DELETE statements into multiple batches using the appropriate WHERE clauses. If the individual statements of the batch are executed in separate transactions, then fewer locks will be held on the resource within one transaction, and for shorter time periods.
Decrease the isolation level Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH (NOLOCK) locking hint.
Partition the contended data This way, exclusive locks will be acquired at partition level, so it will increase concurrency.

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Find out waiting tasks - sys.dm_os_waiting_tasks

sys.dm_os_waiting_tasks dmv shows all threads that are currently suspended:

        DB_NAME(ER.database_id) DatabaseName,
        sys.dm_os_waiting_tasks OT
        INNER JOIN sys.dm_exec_sessions ES ON OT.[session_id] = ES.[session_id]
        INNER JOIN sys.dm_exec_requests ER ON ES.[session_id] = ER.[session_id]
        OUTER APPLY sys.dm_exec_sql_text (ER.[sql_handle]) ST
        OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
WHERE ES.[is_user_process] = 1
ORDER BY OT.[session_id], OT.[exec_context_id];

Deadlock victim selection

The deadlock victim is chosen based on the following criteria:

  • The DEADLOCK_PRIORITY of the two sessions is compared and the lowest priority session is selected as the victim.
  • If both sessions have the same DEADLOCK_PRIORITY value, the transaction that is the least expensive to rollback (based on the log records that have been generated) is selected as the victim.

SQL Server - lock compatibility matrix

If a resource is already locked when a transaction requests a lock on it, the new lock can only be acquired if it is compatible with the existing lock on resource.

Requested lock mode Existing lock mode
Intent Shared - IS Yes Yes Yes Yes Yes No
Shared - S Yes Yes Yes No No No
Update - U Yes Yes No No No No
Intent Exclusive - IX Yes No No Yes No No
Shared with Intent Exclusive - SIX Yes No No No No No
Exclusive - X No No No No No No

A full compatibility matrix is available in SQL Server Books Online.

Understanding SQL Server Lock Modes

Shared S - Allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. It is used for READ-ONLY operations.
- The lock is released as soon as the read operation completes (except the case when isolation level is repeatable reads (or higher) or hints are used.
- It doesn’t prevent other read-only queries from accessing the data simultaneously because the integrity of the data isn’t compromised by the concurrent reads.
Update U - Indicates that the data is read for modification (associated with an UPDATE statement).
- Only one transaction can acquire an update lock at a time.
- Instead of acquiring an exclusive right while reading the data, the data to be modified is read and an (U) lock is acquired on the data. When data is modified, the (U) lock is converted to an exclusive lock for modification. If no modification is required, then the (U) lock is released.
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.
Intent IS IX IU SIX SIU UIX - The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction.
- If the intent locks were not used, then a transaction trying to acquire a lock at a higher level would have to scan through the lower levels to detect the presence of lower level locks.
Schema modification Sch-M Used when a DDL or DML operation is performed.
Schema stability Sch-S Used when queries are being compiled. Other transactinal locks are not blocked (S, U, X), but a Sch-M lock cannot be acquired.
Bulk Update Used when you are bulk copying data into a table. Allow multiple threads to copy data concurrently into the same table while preventing access to that table by any other process.
Key-Range Lock index rows in case of serializable transactions. No rows whose key falls withing the range of the locked index keys can be inserted, updated or deleted.
RangeS-S - Shared range, shared resource lock; serializable range scan.
RangeS-U - Shared range, update resource lock; serializable update scan.
RangeI-N - Insert range, null resource lock; used to test ranges before inserting a new key into an index.
RangeX-X - Exclusive range, exclusive resource lock; used when updating a key in a range.

Lock granularity

RID A row identifier used to lock a single row within a heap.
KEY A single row is locked - index key for a single row in an index.
PAGE An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENT A lock on an extent (=a contiguous group of 8 pages, such as data or index pages). Used, for example, when an ALTER INDEX REBUILD command is executed on a table and the pages of the table may be moved from an existing extent to a new extent.
HoBT It acts like a table-level lock, but on a partition instead of on the table itself.
TABLE The entire table, including all data and indexes => blocks all write requests on the entire table (including indexes), it can significantly hurt database concurrency.
FILE A database file.
APPLICATION An application-specified resource.
METADATA The table schema definition is locked.
ALLOCATION_UNIT An allocation unit.
DATABASE The entire database.

Isolation levels: dirty reads, non-repeatable reads, phantom reads

An image saying everything about transaction isolation levels in SQL Server:

  Description Dirty reads Non-repeatable reads Phantom reads
Read Uncommited (S) locks are not acquired by SELECT statements.
You can also achieve this degree of isolation on a query basis using the NOLOCK locking hint:
Yes Yes Yes
Read Commited (S) locks are requested by the SELECT statements, but (S) locks are acquired only while data is read (not until the end of the transaction). No Yes Yes
Repeatable Read Shared locks are retained by the SELECT statements until the end of the transaction which completely prevents others from updating any data covered by the lock. No No Yes
Serializable Instead of acquiring a lock only on the row to be accessed, the Serializable isolation level acquires a range lock on the row and the next row in the order of the data set requested.
This prevents update and the addition of rows by other transactions in the data set operated on by the first transaction.
No No No
Subscribe to RSS - locks