SQL Server Locks and Blocks

Locking is unavoidable in SQL Server and occurs when a session holds a lock on a resource and other sessions attempt to acquire conflicting locks on the same resource. The second session will wait on one of the LCK_M wait types and depending on conditions, severe performance degradation can occur. Severe locking issues are typically due to one of the following:

• A session holds locks for an extended period of time before releasing them. This can be caused by a session that acquires locks and then executes other inefficient SQL before releasing the locks. This type of locking typically resolves itself over time, but can cause significant waits depending on execution times of the inefficient SQL statements.
• A session holds locks and never releases them. This type of blocking may never resolve itself and prevent access to resources indefinitely. Terminating the session is typically the method used for releasing these locks.

Common blocking scenarios and solutions

1. Inefficient and long running SQL statements while locks are being held.
   1. Optimize the poorly performing SQL statements so locks will be held for the shortest time possible.
   2. Determine if the locks can be released before the session executes the poorly performing SQL statement. For example, if a DELETE statement is executed (and locks acquired) and is followed by an SELECT statement that performs a full table scan, determine if a COMMIT could be executed between them so locks are released sooner.
2. Sleeping sessions that have lost track of the transaction nesting level. For example, an application cancels a SQL statement or gets timed out but does not issue a ROLLBACK or COMMIT. Until that occurs, resources could remain locked.
   1. In the error handler of the client application, submit an IF @@TRANCOUNT > 0 ROLLBACK TRAN following any error.
   2. Use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client.
   3. If connection pooling is being used in an application that opens the connection and runs a small number of queries before releasing the connection back to the pool, such as a Web-based application, temporarily disabling connection pooling may help alleviate the problem until the client application is modified to handle the errors appropriately. By disabling connection pooling, releasing the connection will cause a physical logout of the SQL Server connection, resulting in the server rolling back any open transactions.
   4. If connection pooling is enabled and the destination server is SQL Server 2000, upgrading the client computer to MDAC 2.6 or later may be beneficial. This version of the MDAC components adds code to the ODBC driver and OLE DB provider so that the connection would be “reset” before it is reused. Note that the connection is not reset until it is reused from the connection pool, so it is possible that a user could open a transaction and then release the connection to the connection pool, but it might not be reused for several seconds
   5. If inefficient SQL statements are causing the query timeouts to occur, tuning those SQL statement as suggested in #1 above may also help.
3. Applications that do not completely fetch all result rows.
   1. After sending a query to the server, all applications must immediately fetch all result rows to completion, otherwise locks can be left on the tables, blocking other users.
4. Session that are rolling back data. A data modification query that requires to be rolled back can take considerable time. A data modification query often cannot be rolled back any faster than the changes were initially applied. Because this must happen, SQL Server marks the session in a rollback state (which means it cannot be KILLed or selected as a deadlock victim). The Status column of sysprocesses will indicate a ROLLBACK status and this will also appear in sp_who output.
   1. There is no real resolution for this other than waiting for the session to finish rolling back the changes that were made.
5. Sessions that become orphaned. If the client application crashes or the client workstation is restarted, the network session to the server may not be immediately canceled under some conditions. From the server’s perspective, the client still appears to be present, and any locks acquired may still be retained.
   1. If the client application has disconnected without appropriately cleaning up its resources, you can terminate the session by using the KILL command.

Locking analysis with SolarWinds DPA

SolarWinds’ DPA constantly monitors the SQL Server instance and collects detailed information about locking issues. Blocking problems will typically manifest themselves as sessions and SQL statements accumulating significant wait time for LCK_M* wait types. Information collected by DPA reagarding locking includes:

• Blocker session details – SQL statements executed (maybe none in the case of an idle blocker) and wait time accumulated while blocking other sessions.
• Waiter session details – SQL statements being blocked and associated wait time

When attempting to solve blocking issues, the following list of tasks should be performed within DPA to capture key information:

1. Drill into the day and timeframe where blocking occurred.
2. The “Blockers” tab will display the session/SPID at the head of the blocking tree. If there are several top level blockers, this indicates that more than one session has been a blocker during the selected timeframe. The “Details” link for the blocking session(s) will display the top SQL statements executed by the session while it was blocking others. If excessive wait time is found for one or more statements, tuning them will reduce the time the session holds onto the locks. The faster these statements execute the sooner the blocking session can release locks. The blocker session could also be idle which typically means it was sleeping during the timeframe and may have to be terminated to release the locks.
3. Expand the main blocker session and a list of all sessions waiting for it during the timeframe will be given. The SQL link next to each session specifies the statements attempting to acquire locks held by the main blocker session.