Handling locks, blocking, and deadlocks (2)-detecting blocking and blocking queries

Preface:

If a transaction is waiting for a resource that is locked for another transaction. This transaction becomes a "blocked transaction". In turn, a blocking transaction, that is, a transaction that locks resources and causes other transactions to wait is called a "blocking transaction."

Long-running transactions can block other transactions and queries, making them wait 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 to wait for the chain.

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,   new window and enter: [SQL] view plain copy print? use adventureworks2012   go   SET   TRANSACTION &NBSP Isolation   level   repeatable   READ    go     --Opening transactions    BEGIN   TRANSACTION      --get session ID    SELECT   @ @SPID   as  Connection1_SessionID      SELECT    *   from     Sales.SalesOrderDetail   WHERE    SalesOrderDetailID = 121316  

Use AdventureWorks2012
go
SET TRANSACTION isolation level repeatable READ
go

-open transaction
begin TRANSACTION

--Gets the session ID
select  @ @SPID as Connection1_sessionid

Select  *    from Sales.SalesOrderDetail
WHERE   salesorderdetailid = 121316

3. After the execution, the screenshot is as follows:

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

[SQL] view plain copy print? Use AdventureWorks2012 Go--Open transaction begin TRANSACTION UPDATE Sales.salesorderdetai L SET OrderQty = ten WHERE salesorderdetailid = 121316 COMMIT transaction

Use AdventureWorks2012
go

--open transaction
begin TRANSACTION

UPDATE  sales.salesorderdetail
SET     OrderQty = Ten
WHERE   salesorderdetailid = 121316

COMMIT TRANSACTION

5, open a transaction again, enter the following code query blocked and blocking query:[SQL]View Plain copy print?SELECTr.session_id asBlockedsessionid, s.session_id asBlockingsessionid, Q1.text asBlockedsession_tsql, Q2.text asBlockingsession_tsql, C1.most_recent_sql_handle asBlockedsession_sqlhandle, C2.most_recent_sql_handle asBlockingsession_sqlhandle, S.original_login_name asBlockingsession_loginname, S.program_name asBlockingsession_applicationname, S.host_name asBlockingsession_hostname fromSys.dm_exec_requests asRINNERJOIN sys.dm_exec_sessions asS onr.blocking_session_id = s.session_idINNERJOIN sys.dm_exec_connections asC1 onr.session_id = c1.most_recent_session_idINNERJOIN sys.dm_exec_connections asC2 ons.session_id = c2.most_recent_session_id CROSS APPLY sys.dm_exec_sql_text (c1.most_recent_sql_handle) asQ1 CROSS APPLY sys.dm_exec_sql_text (c2.most_recent_sql_handle) asQ2

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_hostname from    sys.dm_exec_requests as R
        INNER JOIN sys.dm_exec_sessions as S on r.blocking_session_id = s.session_id INNER
        JO In Sys.dm_exec_connections as C1 on r.session_id = c1.most_recent_session_id
        INNER JOIN sys.dm_exec_connections as C 2 on s.session_id = c2.most_recent_session_id
        CROSS APPLY sys.dm_exec_sql_text (c1.most_recent_sql_handle) as Q1< C15/>cross APPLY sys.dm_exec_sql_text (c2.most_recent_sql_handle) as Q2

6. Because the first connection takes up resources and blocks other transactions, this is the end of the process:

[SQL] view plain copy print? KILL Go

KILL Go

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

Analysis:

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

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

To identify blocked and blocked requests, you need to use the following DMO:

1, Dm_exec_requests

2, Dm_exec_sessions

3, Dm_exec_connections

4, Dm_exec_sql_text

from:http://blog.csdn.net/dba_huangzj/article/details/8697578