blocking

One session (or thread) needs access to a piece of data and has to wait for another session’s lock to clear.

Identify blocking in SQL Server

Run this T-SQL statement to identify current blockings in the current database:

SELECT *
FROM sys.sysprocesses
WHERE
        dbid = DB_ID()
        AND blocked > 0

Too see T-SQL that blocks, just take the value of spid column returned above and run the following command:

DBCC INPUTBUFFER(<spid>);

In order to see the blocked T-SQL command, take the value of column returned above and run:

DBCC INPUTBUFFER(<blocked>);

Tips to avoid database blocking

In a multiuser database application, you must minimize blocking among concurrent transactions.

Keep transactions short
• Perform the minimum steps/logic within a transaction.
• Do not perform costly external activity within a transaction, such as sending acknowledgment email or performing activities driven by the end user.

Optimize queries using indexes
• Create indexes as required to ensure optimal performance of the queries within the system.
• Avoid a clustered index on frequently updated columns. Updates to clustered index key columns require locks on the clustered index and all nonclustered indexes (since their row locator contains the clustered index key).
• Consider using a covering index to serve the blocked SELECT statements.

Consider partitioning a contended table

Use query timeouts or a resource governor to control runaway queries

Avoid losing control over the scope of the transactions because of poor error-handling routines or application logic
• Use SET XACTABORT ON to avoid a transaction being left open on an error condition within the transaction.
• Execute the following SQL statement from a client error handler (TRY/CATCH) after executing a SQL batch or stored procedure containing a transaction:

IF @@TRANCOUNT > 0 ROLLBACK

Use the lowest isolation level required
• Use the default isolation level (Read Committed).
• Consider using row versioning to help reduce contention.

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.

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.
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.
Subscribe to RSS - blocking