reproducing deadlocks

I have done some research in how to avoid and view trace logs for deadlocks.
now iwant to test this on my test db.
Is there any steps by which deadlock can be reprduced in any test dbsay
authors or pubs?
thanksHi Vicky
You can simulate this using Query Analyser or Management Studio using two
sessions, by starting a transaction and not committing it in one session
while you try to update the same table in the second session you can get
either blocking or a deadlock. This is tested on SQL 2005:
Blocking example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
Window 1
EXEC sp_who2 -- Shows [Windows 1 SPID] blocking [Windows 2 SPID]
ROLLBACK TRANSACTION
Window 2
-- Results have now been returned
ROLLBACK TRANSACTION
Deadlock Example
Window 1
USE PUBS
SELECT @.@.SPID AS [Windows 1 SPID]
Window 2
USE PUBS
SELECT @.@.SPID AS [Windows 2 SPID]
Window 1
BEGIN TRANSACTION
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Returned immediately
Window 2
BEGIN TRANSACTION
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Returned immediately
Window 1
SELECT * FROM TITLES WITH (UPDLOCK) -- Results Not Returned Immediately
Window 2
SELECT * FROM AUTHORS WITH (UPDLOCK) -- Results Not Returned Immediately
In one windows (in my case window 2) you will get a message like:
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 55) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Window 1
ROLLBACK TRANSACTION
Window 2
ROLLBACK TRANSACTION
In the window that had the deadlock you will get the error message
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
HTH
John
"vicky" wrote:
> I have done some research in how to avoid and view trace logs for deadlocks.
> now iwant to test this on my test db.
> Is there any steps by which deadlock can be reprduced in any test dbsay
> authors or pubs?
> thanks
>
>