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 deadlock
s.
> 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
>
>

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
>
>

reproducing deadlock

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?
thanksHere are two scripts:
<Script1>
-- Step 1
begin tran
update authors
set
au_fname = 'Joe'
where
au_lname = 'White'
-- Step 3
select
*
from
authors
where
au_lname = 'Green'
-- rollback tran
</Script1>
<Script2>
-- Step 2
begin tran
update authors
set
au_fname = 'Joe'
where
au_lname = 'Green'
select
*
from
authors
where
au_lname = 'White'
-- rollback tran
</Script2>
Note the embedded comments. Open 2 separate sessions and bring up Script1
in one session and Script2 in the other. In Script1, highlight and execute
Step 1. Next, run Script2 in its entirety (Step 2). It will not finish
executing, since it's waiting on Script1. Go back to Script1 and highlight
Step 3. Execute it. Within a few seconds, it will throw error 1205 - a
deadlock. It will also rollback the transaction.
Script2 will now complete. However, it still has an open transaction. Be
sure to rollback the transaction in Script2.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"vicky" <v@.v.com> wrote in message
news:%23LdGE6FjGHA.4748@.TK2MSFTNGP04.phx.gbl...
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