Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of the object which the two queries are contending over?

share|improve this question
    
add comment

migrated from stackoverflow.com Feb 3 '12 at 22:04

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote accepted

In management studio you can right click the server node and select Reports -> Standard Reports -> Activity - all Blocking Transactions to see blocking currently ongoing.

The query that report runs can be traced through Profiler and is pasted below.

declare @tab_tran_locks as table(
        l_resource_type nvarchar(60) collate database_default 
,       l_resource_subtype nvarchar(60) collate database_default 
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default 
,       l_blocked_request_mode nvarchar(60) collate database_default
,       l_blocking_tran_id bigint
,       l_blocked_tran_id bigint   
);
declare @tab_blocked_tran as table (
        tran_id bigint
,       no_blocked bigint
);
declare @temp_tab table(
        blocking_status int
,       no_blocked int
,       l_resource_type nvarchar(60) collate database_default 
,       l_resource_subtype nvarchar(60) collate database_default 
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default 
,       l_blocked_request_mode nvarchar(60) collate database_default 
,       l_blocking_tran_id int
,       l_blocked_tran_id int   
,       local1 int
,       local2 int
,       b_tran_id bigint
,       w_tran_id bigint
,       b_name nvarchar(128) collate database_default 
,       w_name nvarchar(128) collate database_default 
,       b_tran_begin_time datetime
,       w_tran_begin_time datetime
,       b_state nvarchar(60) collate database_default 
,       w_state nvarchar(60) collate database_default 
,       b_trans_type nvarchar(60) collate database_default 
,       w_trans_type nvarchar(60) collate database_default 
,       b_text nvarchar(max) collate database_default 
,       w_text nvarchar(max) collate database_default 
,       db_span_count1 int
,       db_span_count2 int 
);

insert into @tab_tran_locks 
select  a.resource_type
,               a.resource_subtype
,               a.resource_associated_entity_id
,               a.request_session_id as blocking 
,               b.request_session_id as blocked
,               a.request_mode
,               b.request_mode 
,               a.request_owner_id
,               b.request_owner_id  
from sys.dm_tran_locks a 
join sys.dm_tran_locks b on (a.resource_type = b.resource_type and a.resource_subtype = b.resource_subtype and a.resource_associated_entity_id = b.resource_associated_entity_id and a.resource_description = b.resource_description)
where a.request_status = 'GRANT' and (b.request_status = 'WAIT' or b.request_status = 'CONVERT') and a.request_owner_type = 'TRANSACTION' and b.request_owner_type = 'TRANSACTION'

insert into @tab_blocked_tran 
select  ttl.l_blocking_tran_id
,       count(distinct ttl.l_blocked_tran_id)
from @tab_tran_locks ttl   
group by ttl.l_blocking_tran_id
order by count( distinct ttl.l_blocked_tran_id) desc 

insert into @temp_tab 
select  0 as blocking_status
,       tbt.no_blocked
,       ttl.*
,       st1.is_local as local1
,       st2.is_local as local2
,       st1.transaction_id as b_tran_id
,       ttl.l_blocked_tran_id as w_tran_id
,       at1.name as b_name,at2.name as w_name
,       at1.transaction_begin_time as b_tran_begin_time
,       at2.transaction_begin_time as w_tran_begin_time
,       case when at1.transaction_type <> 4 
                then case at1.transaction_state 
                        when 0 then 'Invalid' 
                        when 1 then 'Initialized' 
                        when 2 then 'Active' 
                        when 3 then 'Ended' 
                        when 4 then 'Commit Started' 
                        when 5 then 'Prepared' 
                        when 6 then 'Committed' 
                        when 7 then 'Rolling Back' 
                        when 8 then 'Rolled Back' 
                end 
                else case at1.dtc_state 
                        when 1 then 'Active' 
                        when 2 then 'Prepared' 
                        when 3 then 'Committed' 
                        when 4 then 'Aborted' 
                        when 5 then 'Recovered' 
        end end b_state
,       case when at2.transaction_type <> 4 
                then case at2.transaction_state 
                        when 0 then 'Invalid' 
                        when 1 then 'Initialized' 
                        when 2 then 'Active' 
                        when 3 then 'Ended' 
                        when 4 then 'Commit Started' 
                        when 5 then 'Prepared' 
                        when 6 then 'Committed' 
                        when 7 then 'Rolling Back' 
                        when 8 then 'Rolled Back' 
                end 
                else case at2.dtc_state 
                        when 1 then 'Active' 
                        when 2 then 'Prepared' 
                        when 3 then 'Committed' 
                        when 4 then 'Aborted' 
                        when 5 then 'Recovered' 
                end 
        end w_state
,       at1.transaction_type as b_trans_type
,               at2.transaction_type  as w_trans_type
,       case when r1.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r1.statement_start_offset+2)/2, (case when r1.statement_end_offset = -1   then (len(convert(nvarchar(MAX),text))*2) else r1.statement_end_offset  end - r1.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r1.sql_handle)) end as b_text
,       case when r2.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r2.statement_start_offset+2)/2, (case when r2.statement_end_offset =-1 then len(convert(nvarchar(MAX),text))*2  when r2.statement_end_offset =0  then len(convert(nvarchar(MAX),text))*2  else r2.statement_end_offset  end - r2.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r2.sql_handle)) end as w_text 
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st1.transaction_id ) as db_span_count1
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st2.transaction_id ) as db_span_count2  
from @tab_tran_locks ttl 
inner join sys.dm_tran_active_transactions at1 on(at1.transaction_id = ttl.l_blocking_tran_id) 
inner join @tab_blocked_tran tbt on(tbt.tran_id = at1.transaction_id)  
inner join sys.dm_tran_session_transactions st1 on(at1.transaction_id = st1.transaction_id) 
left outer join sys.dm_exec_requests r1 on(at1.transaction_id = r1.transaction_id ) 
inner join sys.dm_tran_active_transactions at2 on(at2.transaction_id = ttl.l_blocked_tran_id) 
left outer join sys.dm_tran_session_transactions st2  on(at2.transaction_id = st2.transaction_id)  
left outer join  sys.dm_exec_requests r2 on(at2.transaction_id = r2.transaction_id ) 
where st1.is_user_transaction = 1
order by tbt.no_blocked desc;

with Blocking(
        blocking_status
,       no_blocked
,       total_blocked
,       l_resource_type
,       l_resource_subtype
,       l_resource_associated_entity_id
,       l_blocking_request_spid
,       l_blocked_request_spid
,       l_blocking_request_mode
,       l_blocked_request_mode
,       local1
,       local2
,       b_tran_id
,       w_tran_id
,       b_name
,       w_name
,       b_tran_begin_time
,       w_tran_begin_time
,       b_state
,       w_state
,       b_trans_type
,       w_trans_type
,       b_text
,       w_text
,       db_span_count1
,       db_span_count2
,       lvl
)
as( select blocking_status
        ,       no_blocked
        ,       no_blocked
        ,       l_resource_type
        ,       l_resource_subtype
        ,       l_resource_associated_entity_id
        ,       l_blocking_request_spid
        ,       l_blocked_request_spid
        ,       l_blocking_request_mode
        ,       l_blocked_request_mode
        ,       local1
        ,       local2
        ,       b_tran_id
        ,       w_tran_id
        ,       b_name
        ,       w_name
        ,       b_tran_begin_time
        ,       w_tran_begin_time
        ,       b_state
        ,       w_state
        ,       b_trans_type
        ,       w_trans_type
        ,       b_text
        ,       w_text
        ,       db_span_count1
        ,       db_span_count2
        ,       0
        from @temp_tab
   union all
        select E.blocking_status
        ,       M.no_blocked
        ,       convert(int,E.no_blocked + total_blocked)
        ,       E.l_resource_type
        ,       E.l_resource_subtype
        ,       E.l_resource_associated_entity_id
        ,       M.l_blocking_request_spid
        ,       E.l_blocked_request_spid
        ,       M.l_blocking_request_mode
        ,       E.l_blocked_request_mode
        ,       M.local1
        ,       E.local2
        ,       M.b_tran_id
        ,       E.w_tran_id
        ,       M.b_name
        ,       E.w_name
        ,       M.b_tran_begin_time
        ,       E.w_tran_begin_time
        ,       M.b_state
        ,       E.w_state
        ,       M.b_trans_type
        ,       E.w_trans_type
        ,       M.b_text
        ,       E.w_text
        ,       M.db_span_count1
        ,       E.db_span_count2
        ,       M.lvl+1
        from @temp_tab as E
    join Blocking as M on E.b_tran_id = M.w_tran_id
)

select (dense_rank() over (order by no_blocked desc,b_tran_id))%2 as l1 
,       (dense_rank() over (order by no_blocked desc,b_tran_id,w_tran_id))%2 as l2 
,       * 
from Blocking 
order by no_blocked desc,b_tran_id,w_tran_id ;
share|improve this answer
add comment

The first step would be to turn on trace flag 1222 with “DBCC TRACEON (1222, -1)”. Once you do that you'll be able to view more information about the deadlock in the error log, including which object is the source of contention.

Bart Duncan has a good series on troubleshooting deadlocks on his blog at: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

share|improve this answer
1  
The question is about blocking, not deadlocks. –  Martin Smith Feb 3 '12 at 22:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.