Monday, July 09, 2007

Creating Deadlocks

I have have been trying to re-create deadlocks in my database and after a few try I came up with a scripts which does it for me.

CREATE TABLE createDeadlock1 (deadLockId int)
go
Create Table createDeadlock2 (deadlockId int)
-- now populate the table with some data
insert into createDeadlock1
values (1)
insert into createDeadlock2
values(1)
-- now open two connections
steps 1 : In connection 1 run :
begin tran
update createDeadlock1
set deadlockId = 1

step2 : Then in connection 2 run
begin tran
update createDeadlock2
set deadlockId = 1
update createDeadlock1
set deadlockId = 1

step 3 - go back to connection 1 and run

update createDeadlock2
set deadlockId = 1


The above three steps will create a deadlock. Do it many times and you will see the counter value always increasing. To view the counter use sysperfinfo table as shown below.
select * from master.dbo.sysperfinfo
where instance_name = '_total' and counter_name like '%deadlock%'

No comments: