Application analysis of SQL application in SQL2008-blocking (Blocking) _mssql2005

Typically, short time blocking is not a problem and is required by a busier application. However, poorly designed applications can lead to prolonged blocking, which unnecessarily locks resources and blocks other sessions from reading and updating them.

In SQL Server, a blocked process remains blocked indefinitely, or until it times out (according to set lock_timeout), server shutdown, process is killed, connection completes, or other operations that occur on the original transaction cause it to release the lock on the resource.

The reasons for prolonged blocking occur are as follows:

1. Excessive row locks on an index-less table can cause SQL Server to get a lock, blocking other transactions.

2. The application opens a transaction and asks the user for feedback or interaction when the transaction remains open. This usually occurs when the end user enters data on the GUI and keeps the transaction open. At this point, any resources referenced by the transaction will be occupied.

3. Data queried after the start of a transaction may be invoked before the transaction begins

4, the query improper use of locking tips. For example, an application uses only a small number of rows, but uses a table lock hint

5. Applications use long-running transactions to update many rows or tables in one transaction (turning a large number of updated transactions into multiple updates with fewer transactions helps to improve concurrency)

First, find and resolve the blocking process

Here we demonstrate the use of SQL Server dynamic management view Sys.dm_os_waiting_tasks to find the blocking process that replaces system stored procedures in earlier versions of SQL Server sp_who

After we find the blocked process, we use the Sys.dm_exec_sql_text dynamic management function and the sys.dm_exec_connections (DMV) to find the SQL text of the query being executed, and then force the end of the process.

To force the end of the process, we use the KILL command. For use of Kill, refer to Msdn:http://msdn.microsoft.com/zh-cn/library/ms173730.aspx

The command has three parameters:

The session ID of the process to terminate with the sessionsID . The session ID is a unique integer (int) assigned to each user connection when the connection is established. During a connection, the session ID value is bundled with the connection. At the end of the connection, the integer value is freed and can be reassigned to a new connection. Use the KILL session ID to terminate a regular, distributed transaction and distribution transaction that is associated with the specified conversation ID.
UOW identifies the unit of work (UOW) ID of the distributed transaction. UOW is a GUID that can be obtained from the Request_owner_guid column in the sys.dm_tran_locks dynamic management view. UOW can also be obtained from the error log or through the MS DTC Monitor. For more information about monitoring distributed transactions, see the MS DTC documentation. Use the KILL UOW to terminate an orphaned distributed transaction. These transactions are not associated with any real session IDs, and are associated with virtual session id = ' 2 '. You can make it easier to identify orphaned transactions by querying the session ID columns in sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests dynamic management views.
The WITHstatusonly generates a progress report for the specified session ID or UOW that is being rolled back due to an earlier KILL statement. KILL with STATUSONLY does not terminate or rollback the session ID or UOW, which displays only the current rollback progress.

In the first query window:

Copy Code code as follows:

BEGIN TRAN
UPDATE production.productinventory
SET Quantity = 400
WHERE ProductID = 1 and
LocationID = 1

Second window:

Copy Code code as follows:

UPDATE production.productinventory
SET Quantity = 406
WHERE ProductID = 1 and
LocationID = 1

A third window:

Copy Code code as follows:

SELECT blocking_session_id, Wait_duration_ms, session_id
From Sys.dm_os_waiting_tasks
WHERE blocking_session_id is not NULL

/*
blocking_session_id Wait_duration_ms session_id
52 23876 54
*/

As you can see, a session with a sessionid of 52 blocks SessionID 54.

So, what is 52 doing? Execute in a third window:

Copy Code code as follows:

SELECT T.text
From Sys.dm_exec_connections C
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE c.session_id = 54

/*
Text
(@1 int,@2 tinyint,@3 tinyint) UPDATE [Production]. [ProductInventory] set [Quantity] = @1 WHERE
[Productid]=@2 and [locationid]=@3
*/

Note: This is not the original SQL statement in the First Query window, and SQL Server has an automatic parameterized plan cache (precompiled).
We force the termination of the session. Execute in a third window:

Copy Code code as follows:

Kill 52

Note: The statement of window one and the statements of window two are terminated.

Tip: In the third statement, the most_recent_sql_handle column with session ID 53 is returned using the Sys.dm_exec_connections (DMV) . This is the pointer to the SQL text in memory. Used as an input parameter for the Sys.dm_exec_sql_text dynamic management function. The text column is returned from Sys.dm_exec_sql_text, which shows the SQL text that blocked the process. if the string is blocked, each blocking process must be carefully viewed through the blocking_session_id and session_id columns until the original blocking process is found.

Second, the configuration statement waits for the lock to release the time length

If a transaction or statement is blocked, it means that the lock on the waiting resource is freed. We can set the time to wait through the set lock_timeout beforehand.

The syntax is as follows: SET lock_timeout time_period

The parameter is in milliseconds. A lock error is returned when it is exceeded. Example:

Executes in the first window:

Copy Code code as follows:

Use AdventureWorks
BEGIN TRAN
UPDATE production.productinventory
SET Quantity = 400
WHERE ProductID = 1 and
LocationID = 1

Execute in the second window:

Copy Code code as follows:

Use AdventureWorks
SET Lock_timeout 1000
UPDATE production.productinventory
SET Quantity = 406
WHERE ProductID = 1 and
LocationID = 1

/*
1 seconds to execute results
MSG 1222, level, state Wuyi, line 3
Lock request time out period exceeded.
The statement has been terminated.
*/

Parsing: In this example, we set the lock timeout time to 1000 milliseconds, or 1 seconds. This setting does not affect the time that the resource is occupied by the process, only the time that waits for another process to release the resource access.