Generating Deadlock scenario is very much similar to blocking the only difference is , here sql server interfere and kill one of processes using mechanism explained here
Here i am creating scenario for deadlock, you can try this in your local machines.
/* First Recycle Errorlog to get all details without scrolling whole errorlog*/
EXEC msdb.dbo.sp_cycle_errorlog
/* Enable deadlock trace if you want to see deadlock details in error log else you can use profiler*/
/*Verify trace flags (1222 / 1204 , 3604 , 3605)*/
dbcc tracestatus
/* First run this code to create the tables*/
use tempdb
GO
create table ##Table1 (col1 int)
create table ##Table2 (col1 int)
GO
insert ##Table1
Select 1 union select 2 union select 3
GO
insert ##Table2
Select 1 union select 2 union select 3
GO
select * from ##Table1
select * from ##Table2
GO
/* Now we will Create deadlock */
/*Paste below codes in two seperate QA windows connected to same Server\Db */
-----------------QA window #1---------------
begin tran
update ##Table1 set col1 = 4 where col1 = 3
--delay long enough to lock ##Table1 in this process
--and allow ##Table2 to be locked in other process
waitfor delay '0:0:05'
--this proc is holding lock on ##Table1 waiting for ##Table2 to be released
update ##Table2 set col1 = 4 where col1 = 3
commit tran
---------------------------------------------
----------------Lets say QA window #2--------------------
begin tran
update ##Table2 set col1 = 4 where col1 = 3
--delay long enough to lock ##Table2 in this process
--and allow ##Table1 to be locked in other process
waitfor delay '0:0:05'
--this proc is holding lock on ##Table2 waiting for ##Table1 to be released
update ##Table1 set col1 = 4 where col1 = 3
commit tran
----------------------------------------------
/*Now execute code in both QA windows one by one without delay*/
/**************OUTPUT:************/
/*
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 9
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
*/
/*Great success deadlock occured and now you can verify errorlog also*/
xp_readerrorlog
/*Deadlock output will be almost similar as below*/
----------------------------------------------------------------------------------------------------------
2014-09-10 20:44:07.320 spid20s deadlock-list
2014-09-10 20:44:07.320 spid20s deadlock victim=process35a31c8
2014-09-10 20:44:07.320 spid20s process-list
2014-09-10 20:44:07.320 spid20s process id=process35a31c8 taskpriority=0 logused=208 waitresource=RID: 2:1:189:2 waittime=3931 ownerId=18133 transactionname=user_transaction lasttranstarted=2014-09-10T20:43:58.370 XDES=0x5d33bd8 lockMode=U schedulerid=4 kpid=7292 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-09-10T20:43:58.370 lastbatchcompleted=2014-09-10T20:03:29.500 clientapp=Microsoft SQL Server Management Studio - Query hostname=SAURABHSINHA-PC hostpid=5332 loginname=SaurabhSinha-PC\saurabh isolationlevel=read committed (2) xactid=18133 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-09-10 20:44:07.320 spid20s executionStack
2014-09-10 20:44:07.320 spid20s frame procname=adhoc line=9 stmtstart=38 sqlhandle=0x0200000016ea1517116cb206943b357ad8385f6577f7b55e
2014-09-10 20:44:07.320 spid20s UPDATE [##temp1] set [col1] = @1 WHERE [col1]=@2
2014-09-10 20:44:07.320 spid20s frame procname=adhoc line=9 stmtstart=540 stmtend=626 sqlhandle=0x02000000d07c332281f3dbc7a1d87dd4bf64d10ec728c2d2
2014-09-10 20:44:07.320 spid20s update ##temp1 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s inputbuf
2014-09-10 20:44:07.320 spid20s begin tran
2014-09-10 20:44:07.320 spid20s update ##temp2 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s --delay long enough to lock ##temp2 in this process
2014-09-10 20:44:07.320 spid20s --and allow ##temp1 to be locked in other process
2014-09-10 20:44:07.320 spid20s waitfor delay '0:0:05'
2014-09-10 20:44:07.320 spid20s --this proc is holding lock on ##temp2 waiting for ##temp1 to be released
2014-09-10 20:44:07.320 spid20s update ##temp1 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s commit tran
2014-09-10 20:44:07.320 spid20s process id=process3509000 taskpriority=0 logused=208 waitresource=RID: 2:1:176:2 waittime=6702 ownerId=18124 transactionname=user_transaction lasttranstarted=2014-09-10T20:43:55.600 XDES=0x9ee2c08 lockMode=U schedulerid=3 kpid=7704 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-09-10T20:43:55.600 lastbatchcompleted=2014-09-10T20:43:36.437 clientapp=Microsoft SQL Server Management Studio - Query hostname=SAURABHSINHA-PC hostpid=5332 loginname=SaurabhSinha-PC\saurabh isolationlevel=read committed (2) xactid=18124 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-09-10 20:44:07.320 spid20s executionStack
2014-09-10 20:44:07.320 spid20s frame procname=adhoc line=9 stmtstart=38 sqlhandle=0x0200000025654b079306c876e8ed2a3e2deab6d53c04a8b4
2014-09-10 20:44:07.320 spid20s UPDATE [##temp2] set [col1] = @1 WHERE [col1]=@2
2014-09-10 20:44:07.320 spid20s frame procname=adhoc line=9 stmtstart=540 stmtend=626 sqlhandle=0x02000000f3721022fb64edeb1fa96cc95511b42c037ba25a
2014-09-10 20:44:07.320 spid20s update ##temp2 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s inputbuf
2014-09-10 20:44:07.320 spid20s begin tran
2014-09-10 20:44:07.320 spid20s update ##temp1 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s --delay long enough to lock ##temp1 in this process
2014-09-10 20:44:07.320 spid20s --and allow ##temp2 to be locked in other process
2014-09-10 20:44:07.320 spid20s waitfor delay '0:0:05'
2014-09-10 20:44:07.320 spid20s --this proc is holding lock on ##temp1 waiting for ##temp2 to be released
2014-09-10 20:44:07.320 spid20s update ##temp2 set col1 = 4 where col1 = 3
2014-09-10 20:44:07.320 spid20s commit tran
2014-09-10 20:44:07.320 spid20s resource-list
2014-09-10 20:44:07.320 spid20s ridlock fileid=1 pageid=189 dbid=2 objectname=tempdb.dbo.##temp1 id=lock5801c40 mode=X associatedObjectId=1729382257853202432
2014-09-10 20:44:07.320 spid20s owner-list
2014-09-10 20:44:07.320 spid20s owner id=process3509000 mode=X
2014-09-10 20:44:07.320 spid20s waiter-list
2014-09-10 20:44:07.320 spid20s waiter id=process35a31c8 mode=U requestType=wait
2014-09-10 20:44:07.320 spid20s ridlock fileid=1 pageid=176 dbid=2 objectname=tempdb.dbo.##temp2 id=lock5802140 mode=X associatedObjectId=1801439851932090368
2014-09-10 20:44:07.320 spid20s owner-list
2014-09-10 20:44:07.320 spid20s owner id=process35a31c8 mode=X
2014-09-10 20:44:07.320 spid20s waiter-list
2014-09-10 20:44:07.320 spid20s waiter id=process3509000 mode=U requestType=wait
----------------------------------------------------------------------------------------------------------
Recommended Reads:
How Sql server Deadlock Mechanism works
Enable Deadlock Trace
Capture Deadlock Using Profiler in SQL Server
No comments:
Post a Comment