Find blocking statements

This article extends the transaction isolation level with examples in blocking
Performed in the order of Session 1-> session 2, Session 1 (SPID=53) Open transaction Update data not yet committed

--Session 1 Open Transaction Update data not yet committed UseAdventureWorks2008;GOBEGIN TRANSACTION; --Modified 1    --leave time minus 8    UPDATEHumanResources.EmployeeSETVacationHours=VacationHours- 8        WHEREBusinessEntityID= 4;

View Code

Session 2 (SPID=54) reads the modified row in session 1

-- Session 2 reads the modified row in session 1  Use AdventureWorks2008; GO BEGIN TRANSACTION ;     -- Enquiry 1    -- This query will be blocked    by session 1 SELECT BusinessEntityID, VacationHours          from HumanResources.Employee         WHERE = 4;

View Code

View connection information for two sessions

Select Spid,kpid,blocked,waittime,lastwaittype,waitresource,dbid,login_time,last_batch,open_tran,status,loginame  fromwherein (session_id)Select  , Most_recent_session_id,connect_time,last_read,last_write,client_net_addressfromwhere  in (+)

View Code

Session 1 (SPID=53) establishes a connection at 2016-11-11 17:09:30 and the last read (Last_read) is 2016-11-11 17:41:45
Session 2 (SPID=54) establishes a connection at 2016-11-11 17:28:14 and the last read (Last_read) is 2016-11-11 17:42:27
The actual situation is session 1, Session 2 in 2016-11-11 17:09:30, 2016-11-11 17:28:14 to establish a connection; session 1 in 2016-11-11 17:41:45 perform the update operation, session 2 in 2016-11-11 17:42:27 the query operation is performed, session 1 blocks session 2.
Note that the last_batch in the same session sys.sysprocesses looks very close to the last_read of sys.dm_exec_connections, but if there is no go keyword in session 2, the query is canceled at a point that is blocked, Wait a minute to execute the statement of Session 2 again, the Last_batch is the point at which the query is canceled, and Last_read is the time after the minute. If session 2 uses the following statement

Select Top 1 *  fromAdventureWorks2008.dbo.DatabaseLog--Use adventureworks2008r2;--GO--BEGIN TRANSACTION;    --Enquiry 1    --This query will be blocked by session 1    SELECTBusinessEntityID, VacationHours fromAdventureWorks2008.HumanResources.EmployeeWHEREBusinessEntityID= 4;

View Code

Can session 2 return a piece of data from DatabaseLog? The answer is no. There is no discussion here, and you are interested in self-testing.
For the starting Session 2 statement, use the following statement to view blocking information

SELECTec1.session_id asBlockedsessionid, Db.name asDatabaseName, Wt.wait_type asWaitType, Ec1.last_read asBlockedtime, Wt.wait_duration_ms/ +                   as [waitduration (s)], Ec1.client_net_address asblockedclientaddress, H1.text                                    asBlockedsqltext, wt.blocking_session_id asBlockingsessionid, H2.text                                    asBlockingsqltext, Sp.program_name asBlockingprogramname,COALESCE(Sp.loginame, Sp.nt_username) asBlockingloginame fromSys.dm_tran_locks asTl with(NOLOCK)INNER JOINsys.databases asDb with(NOLOCK) ondb.database_id=tl.resource_database_idINNER JOINSys.dm_os_waiting_tasks asWt with(NOLOCK) onWt.resource_address=tl.lock_owner_addressINNER JOINSys.dm_exec_connections EC1 with(NOLOCK) onec1.session_id=tl.request_session_idINNER JOINSys.dm_exec_connections EC2 with(NOLOCK) onec2.session_id=wt.blocking_session_id Left OUTER JOINmaster.dbo.sysprocesses asSp with(NOLOCK) onSp.spid=wt.blocking_session_id CrossAPPLY sys.dm_exec_sql_text (Ec1.most_recent_sql_handle) asH1 CrossAPPLY sys.dm_exec_sql_text (Ec2.most_recent_sql_handle) asH2

View Code

The blocked* represents the blocked connection, and the blocking* represents the "source" of the blockage. Blocking occurs when a session (SPID=53) executes first, another session (SPID=54) executes, and the time that the blocking occurs (blockedtime) should be understood as the last_read time of the session that is executed. Because the connection to the session spid54 has been created (Connect_time) before it is blocked, other statements may have been executed.
After you complete the above operation, we roll back the transaction in Session 1 and conversation 2. Then execute in the order of Session 2-> session 1
Session 2 (SPID=54) Modify isolation level repeatable read, open transaction read data

--Session 2 read data under REPEATABLE readSet Transaction Isolation  Level Repeatable ReadGo UseAdventureWorks2008;GOBEGIN TRANSACTION; --Enquiry 2    --vacation Time is    SELECTBusinessEntityID, VacationHours fromHumanResources.EmployeeWHEREBusinessEntityID= 4;

View Code

Session 1 (SPID=53) opens the row read in transaction update session 2

--Session 1 Opens the row read in transaction update session 2 UseAdventureWorks2008;GOBEGIN TRANSACTION; --Modified 2    --This update will be blocked by session 2    UPDATEHumanResources.EmployeeSETVacationHours=VacationHours- 8        WHEREBusinessEntityID= 4;

View Code

Viewing two session connection information

Session 1 (SPID=53) establishes a connection at 2016-11-11 17:09:30 and the last read (Last_read) is 2016-11-11 18:05:56
Session 2 (SPID=54) establishes a connection at 2016-11-11 17:28:14 and the last read (Last_read) is 2016-11-11 18:04:14
The actual situation is session 1, Session 2 in 2016-11-11 17:09:30, 2016-11-11 17:28:14 to establish a connection; session 2 in 2016-11-11 18:04:14 perform a query operation under the REPEATABLE read isolation level, Session 1 performs an update operation on 2016-11-11 18:05:56, and session 2 blocks session 1.
The following statement can be used to view the blocking information, which references SQL Server monitoring statistics blocking script information modification

SELECTec1.session_id asBlockedsessionid, Db.name asDatabaseName, Wt.wait_type asWaitType, Ec1.last_read asBlockedtime, Wt.wait_duration_ms/ +                   as [waitduration (s)], Ec1.client_net_address asblockedclientaddress, H1.text                                    asBlockedsqltext, wt.blocking_session_id asBlockingsessionid, H2.text                                    asBlockingsqltext, Sp.program_name asBlockingprogramname,COALESCE(Sp.loginame, Sp.nt_username) asBlockingloginame fromSys.dm_tran_locks asTl with(NOLOCK)INNER JOINsys.databases asDb with(NOLOCK) ondb.database_id=tl.resource_database_idINNER JOINSys.dm_os_waiting_tasks asWt with(NOLOCK) onWt.resource_address=tl.lock_owner_addressINNER JOINSys.dm_exec_connections EC1 with(NOLOCK) onec1.session_id=tl.request_session_idINNER JOINSys.dm_exec_connections EC2 with(NOLOCK) onec2.session_id=wt.blocking_session_id Left OUTER JOINmaster.dbo.sysprocesses asSp with(NOLOCK) onSp.spid=wt.blocking_session_id CrossAPPLY sys.dm_exec_sql_text (Ec1.most_recent_sql_handle) asH1 CrossAPPLY sys.dm_exec_sql_text (Ec2.most_recent_sql_handle) asH2

View Code

The blocked "source" does not reflect the transaction isolation level, and how to obtain the transaction isolation level of the connection. The Transaction_isolation_level in sys.dm_exec_requests is only valid for a running connection, and the sleeping state cannot be queried at all. The DBCC USEROPTIONS to the corresponding session will not return the results, if it is the client's connection? Can the isolation level be captured only when the user executes the statement again?

Find blocking statements