Lesson Learned #152: When the transaction context matters in a blocking issue!

Published 12-03-2020 09:53 AM 1,205 Views

Today, we got a service request that our customer reported that the using the following article the blocker and blocked queries haven't relation between them.

 

In this post we are going to explain why?. Normally, when you need to identify blocking issues we’re used to run the following query explained on this URL.

 

 

select conn.session_id as blockerSession,conn2.session_id as BlockedSession,req.wait_time as Waiting_Time_ms,cast((req.wait_time/1000.) as decimal(18,2)) as Waiting_Time_secs,
cast((req.wait_time/1000./60.) as decimal(18,2)) as Waiting_Time_mins,t.text as BlockerQuery,t2.text as BlockedQuery, req.wait_type from sys.dm_exec_requests as req
inner join sys.dm_exec_connections as conn on req.blocking_session_id=conn.session_id
inner join sys.dm_exec_connections as conn2 on req.session_id=conn2.session_id
cross apply sys.dm_exec_sql_text(conn.most_recent_sql_handle) as t
cross apply sys.dm_exec_sql_text(conn2.most_recent_sql_handle) as t2

 

 

We found a situation that the query reported could not be the query that is blocking the another one. In this specify situation is when our customer is running multiple queries in the same transaction:

 

  • If our customer is running a bunch of query in the same execution, you could see all the details, for example, this way.

 

BEGIN TRANSACTION
  UPDATE
  INSERT
COMMIT TRANSACTION

 

 

  • However, if the customer application opens a new transaction and runs a single query per execution as  a single unit within this transaction, you mightn’t see the exact query that is blocking other query. In this specific situation and depending on when you run the DMV to obtain the queries/transaction blocked you may see either of these queries INSERT TABLE1 or UPDATE TABLE2 (below).

 

Time   Operation

11:00 AM Customer open a transaction – BEGIN TRANSACTION

              11:01 AM Customer runs INSERT Table1

              11:02 AM Customer runs UPDATE Table 2

              11:03 AM Customer commit the transaction.

 

Enjoy!

%3CLINGO-SUB%20id%3D%22lingo-sub-1953516%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23152%3A%20When%20the%20transaction%20context%20matters%20in%20a%20blocking%20issue!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1953516%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20got%20a%20service%20request%20that%20our%20customer%20reported%20that%20the%20using%20the%20following%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-22-how-to-identify-blocking-issues%2Fba-p%2F368865%22%20target%3D%22_self%22%3Earticle%3C%2FA%3E%26nbsp%3Bthe%20blocker%20and%20blocked%20queries%20haven't%20relation%20between%20them.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20post%20we%20are%20going%20to%20explain%20why%3F.%26nbsp%3BNormally%2C%20when%20you%20need%20to%20identify%20blocking%20issues%20we%E2%80%99re%20used%20to%20run%20the%20following%20query%20explained%20on%20this%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-22-how-to-identify-blocking-issues%2Fba-p%2F368865%22%20target%3D%22_self%22%3EURL%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20conn.session_id%20as%20blockerSession%2Cconn2.session_id%20as%20BlockedSession%2Creq.wait_time%20as%20Waiting_Time_ms%2Ccast((req.wait_time%2F1000.)%20as%20decimal(18%2C2))%20as%20Waiting_Time_secs%2C%0Acast((req.wait_time%2F1000.%2F60.)%20as%20decimal(18%2C2))%20as%20Waiting_Time_mins%2Ct.text%20as%20BlockerQuery%2Ct2.text%20as%20BlockedQuery%2C%20req.wait_type%20from%20sys.dm_exec_requests%20as%20req%0Ainner%20join%20sys.dm_exec_connections%20as%20conn%20on%20req.blocking_session_id%3Dconn.session_id%0Ainner%20join%20sys.dm_exec_connections%20as%20conn2%20on%20req.session_id%3Dconn2.session_id%0Across%20apply%20sys.dm_exec_sql_text(conn.most_recent_sql_handle)%20as%20t%0Across%20apply%20sys.dm_exec_sql_text(conn2.most_recent_sql_handle)%20as%20t2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20found%20a%20situation%20that%20the%20query%20reported%20could%20not%20be%20the%20query%20that%20is%20blocking%20the%20another%20one.%20In%20this%20specify%20situation%20is%20when%20our%20customer%20is%20running%20multiple%20queries%20in%20the%20same%20transaction%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20our%20customer%20is%20running%20a%20bunch%20of%20query%20in%20the%20same%20execution%2C%20you%20could%20see%20all%20the%20details%2C%20for%20example%2C%20this%20way.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EBEGIN%20TRANSACTION%0A%26nbsp%3B%20UPDATE%0A%26nbsp%3B%26nbsp%3BINSERT%0ACOMMIT%20TRANSACTION%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EHowever%2C%20if%20the%20customer%20application%20opens%20a%20new%20transaction%20and%20runs%20a%20single%20query%20per%20execution%20as%20%26nbsp%3Ba%20single%20unit%20within%20this%20transaction%2C%20you%20mightn%E2%80%99t%20see%20the%20exact%20query%20that%20is%20blocking%20other%20query.%20In%20this%20specific%20situation%20and%20depending%20on%20when%20you%20run%20the%20DMV%20to%20obtain%20the%20queries%2Ftransaction%20blocked%20you%20may%20see%20either%20of%20these%20queries%20INSERT%20TABLE1%20or%20UPDATE%20TABLE2%20(below).%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ETime%20%26nbsp%3B%26nbsp%3BOperation%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E11%3A00%20AM%20Customer%20open%20a%20transaction%20%E2%80%93%20BEGIN%20TRANSACTION%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%3A01%20AM%20Customer%20runs%20INSERT%20Table1%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%3A02%20AM%20Customer%20runs%20UPDATE%20Table%202%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2011%3A03%20AM%20Customer%20commit%20the%20transaction.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1953516%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20got%20a%20service%20request%20that%20our%20customer%20reported%20that%20the%20using%20the%20following%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-22-how-to-identify-blocking-issues%2Fba-p%2F368865%22%20target%3D%22_self%22%3Earticle%3C%2FA%3E%26nbsp%3Bthe%20blocker%20and%20blocked%20queries%20haven't%20relation%20between%20them.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20post%20we%20are%20going%20to%20explain%20why%3F.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Dec 03 2020 09:53 AM
Updated by: