16th-handling locks, blockages, and deadlocks (2)-detect blocking and blocking queries

Original: 16th-handling locks, blockages, and deadlocks (2)-detect blocking and blocking queries

Objective:

If a transaction is waiting for some resources to lock on other transactions. This transaction becomes a "blocked transaction". In turn, the transaction that caused the blocking, that is, the lock resource and cause other transactions to wait is called a "blocking transaction."

Long-running transactions block other transactions and queries, leaving them waiting for long periods of time. In heavy systems, many times we encounter blocking problems if a transaction is not completed because of blocking. Will cause some columns of waiting chains.

This article will show you how to find and solve this problem immediately.

Preparatory work:

This example still uses the ADVENTUREWORKS2012 database on SQLServer2012.

Steps:

1, connected to the SQLServer2012 AdventureWorks2012 database.

2. Create a new window and enter:

Use Adventureworks2012goset TRANSACTION Isolation level repeatable readgo--open transaction begin transaction--Get session  Idselect @@ SPID as Connection1_sessionidselect  *from    sales.salesorderdetailwhere   salesorderdetailid = 121316

3. After the execution, the following is done:

4. Open another window, enter the following code to open another transaction, pay attention to the UPDATE statement, will not be executed, because the transaction in the second step is waiting:

Use adventureworks2012go--open transaction begin transactionupdate  sales.salesorderdetailset     OrderQty = 10WHERE   Salesorderdetailid = 121316COMMIT TRANSACTION

5. Open a transaction, and enter the following code to query for blocked and blocked queries:

SELECT  r.session_id as Blockedsessionid,        s.session_id as Blockingsessionid,        Q1.text as Blockedsession_ TSQL,        Q2.text as Blockingsession_tsql,        C1.most_recent_sql_handle as Blockedsession_sqlhandle,        C2.most_ Recent_sql_handle as Blockingsession_sqlhandle,        s.original_login_name as Blockingsession_loginname,        S.program_name as Blockingsession_applicationname,        s.host_name as Blockingsession_hostnamefrom    sys.dm_exec _requests as R        INNER join sys.dm_exec_sessions as S on r.blocking_session_id = s.session_id        INNER Join SYS.DM_EX Ec_connections as C1 on r.session_id = c1.most_recent_session_id        INNER joins Sys.dm_exec_connections as C2 on S.sessio n_id = c2.most_recent_session_id Cross        APPLY sys.dm_exec_sql_text (c1.most_recent_sql_handle) as Q1        Cross APPLY Sys.dm_exec_sql_text (c2.most_recent_sql_handle) as Q2

6, because the first connection consumes resources, blocking other transactions, so here to end this process:

KILL 68GO

7, change back to the second query interface, found that the update operation has been completed successfully. The process number above depends on the machine.

Analysis:

In this example, the transaction isolation level is set to repeatable READ because in this isolation level, the shared locks on the resource persist until the transaction completes. So when you look up data from a table, a shared lock is added to the value. is not released until the transaction commits or rolls back.

When executing the UPDATE statement for the second connection, it cannot be completed because the first transaction is blocked and the shared lock is not released under Repeatable read.

In order to identify blocked and blocked requests, the following DMO is required:

1, Dm_exec_requests

2, Dm_exec_sessions

3, Dm_exec_connections

4, Dm_exec_sql_text

16th-handling locks, blockages, and deadlocks (2)-detect blocking and blocking queries