Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Walking the deadlocks

Author  Topic 

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-10-18 : 05:27:33
Hi all,
I'm doing some deadlocking analysis on blocked spids,
from table #blocks I would just want to select the spids and buffers of the spids that are in a deadlock situation.
It seemed easy enough at first...
Does anyone have any bright ideas on how to best achieve this ?

/* simple sql that incorrectly assumes deadlock if spid is both in blocking and blocked columns
this was too simplistic :( */
select
	block_date
	,blocking_spid
	,blocked_spid
	,blocking_inputbuffer
	,blocked_inputbuffer
from
	#blocks
where
	block_date in(	select distinct b1.block_date
			from #blocks b1 join #blocks b2
			on b1.block_date = b2.block_date
			and b1.blocking_spid = b2.blocked_spid)

select * from #blocks order by 1,2,3

-- Manual blocking analysis : 
2005-10-13 18:04:20.150 -- Trivial deadlock
2005-10-13 18:48:21.920 -- Trivial deadlock + 93 deadlocked
2005-10-17 16:11:07.407 -- No deadlock
2005-10-17 20:21:09.147 -- No Deadlock
2005-10-18 01:06:13.973 -- Deadlock, but 63 and 68 not deadlocked


-- Test data
create table #blocks(block_date datetime, blocking_spid int, blocked_spid int, blocking_inputbuffer nvarchar(255), blocked_inputbuffer nvarchar(255) )

insert #blocks values('2005-10-13 18:04:20.150',121,119,'proc:576','proc:576')
insert #blocks values('2005-10-13 18:04:20.150',119,121,'proc:576','proc:576')
insert #blocks values('2005-10-13 18:48:21.920',90,55,'proc:576','proc:576')
insert #blocks values('2005-10-13 18:48:21.920',55,90,'proc:576','proc:576')
insert #blocks values('2005-10-13 18:48:21.920',90,93,'proc:576','proc:576')
insert #blocks values('2005-10-13 19:07:22.153',60,51,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,52,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,53,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,55,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,57,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,58,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',98,59,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',116,60,'proc:608','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,61,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,64,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,66,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,67,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',98,68,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,69,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,70,'proc:862','proc:11')
insert #blocks values('2005-10-13 19:07:22.153',98,71,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,77,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,78,'proc:862','proc:11')
insert #blocks values('2005-10-13 19:07:22.153',60,83,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,84,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,85,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,88,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,93,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,97,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',116,98,'proc:608','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',98,100,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,101,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,102,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,103,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,105,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,106,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,117,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,119,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,120,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,121,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,122,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,124,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',60,125,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,126,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,127,'proc:223','proc:223')
insert #blocks values('2005-10-13 19:07:22.153',98,128,'proc:862','proc:433')
insert #blocks values('2005-10-13 19:07:22.153',98,129,'proc:862','proc:862')
insert #blocks values('2005-10-13 19:07:22.153',60,131,'proc:223','proc:223')
insert #blocks values('2005-10-17 20:21:09.147',83,53,'proc:608','proc:576')
insert #blocks values('2005-10-17 20:21:09.147',56,64,'proc:520','proc:520')
insert #blocks values('2005-10-17 20:21:09.147',53,108,'proc:576','proc:576')
insert #blocks values('2005-10-17 16:11:07.407',106,79,'proc:624','proc:400')
insert #blocks values('2005-10-17 16:11:07.407',57,93,'proc:240','proc:240')
insert #blocks values('2005-10-17 16:11:07.407',93,94,'proc:240','proc:240')
insert #blocks values('2005-10-17 16:11:07.407',93,121,'proc:240','proc:240')
insert #blocks values('2005-10-18 01:06:13.973',81,51,'proc:700','proc:862')
insert #blocks values('2005-10-18 01:06:13.973',81,61,'proc:700','proc:862')
insert #blocks values('2005-10-18 01:06:13.973',81,67,'proc:700','proc:862')
insert #blocks values('2005-10-18 01:06:13.973',63,68,'proc:682','proc:682')
insert #blocks values('2005-10-18 01:06:13.973',81,76,'proc:700','proc:53')
insert #blocks values('2005-10-18 01:06:13.973',81,79,'proc:700','proc:862')
insert #blocks values('2005-10-18 01:06:13.973',58,81,'proc:716','proc:700')
insert #blocks values('2005-10-18 01:06:13.973',81,87,'proc:700','proc:862')
insert #blocks values('2005-10-18 01:06:13.973',87,58,'proc:700','proc:576')

-- drop table #blocks


rockmoose

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-10-19 : 14:58:17
Ok, coded a solution that can resolve blocks up to 5 levels deep.
select distinct
	bl.*
from
	#blocks bl
	join( 	select distinct
			b1.block_date
			,b1.blocking_spid
			,b2.blocking_spid
			,b3.blocking_spid
			,b4.blocking_spid
			,b5.blocking_spid
			,b6.blocking_spid
		from
			#blocks b1
			join #blocks b2
				on b1.block_date = b2.block_date
				and b1.blocked_spid = b2.blocking_spid
			left join #blocks b3
				on b1.block_date = b3.block_date
				and b2.blocked_spid = b3.blocking_spid
			left join #blocks b4
				on b1.block_date = b4.block_date
				and b3.blocked_spid = b4.blocking_spid
			left join #blocks b5
				on b1.block_date = b5.block_date
				and b4.blocked_spid = b5.blocking_spid
			left join #blocks b6
				on b1.block_date = b6.block_date
				and b5.blocked_spid = b6.blocking_spid
		where
			b1.blocking_spid = b2.blocked_spid
			or
			b1.blocking_spid = b3.blocked_spid
			or
			b1.blocking_spid = b4.blocked_spid
			or
			b1.blocking_spid = b5.blocked_spid
			or
			b1.blocking_spid = b6.blocked_spid ) deadlocked(block_date,spid1,spid2,spid3,spid4,spid5,spid6)
	on bl.block_date = deadlocked.block_date
	and(	bl.blocking_spid = deadlocked.spid1
		or bl.blocking_spid = deadlocked.spid2
		or bl.blocking_spid = deadlocked.spid3
		or bl.blocking_spid = deadlocked.spid4
		or bl.blocking_spid = deadlocked.spid5
		or bl.blocking_spid = deadlocked.spid6 )

-- 5 level deep block
insert #blocks values('20000103',1,2,'proc:1','proc:2') -- 0
insert #blocks values('20000103',2,3,'proc:2','proc:3') -- 1
insert #blocks values('20000103',3,4,'proc:3','proc:4') -- 2
insert #blocks values('20000103',4,5,'proc:4','proc:5') -- 3
insert #blocks values('20000103',5,6,'proc:5','proc:6') -- 4
insert #blocks values('20000103',6,1,'proc:6','proc:1') -- 5
insert #blocks values('20000103',7,2,'proc:7','proc:2') -- noblock
--insert #blocks values('20000103',2,7,'proc:2','proc:7')
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site