16th chapter-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:

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:

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:

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:

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