Reproducing Deadlocks - SQL Server Forums

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

Transact-SQL (2005)

Reproducing Deadlocks

Author

Topic

dev2dev
Starting Member

48 Posts

Posted - 2007-12-26 : 01:16:49

Hello

I have to handle deadlocks from application
in short, my application has to recall the sp when there is an 1205 error (in fact 1205 is just a warning in 2005)

so its very tedious and time consuming to produce DEADLOCK.

If any one can help me writing a script that can produce deadlock will be appreciated

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-26 : 22:20:57

Open two sessions to query tables in open transaction like:

session 1:
begin tran
select * from table1
union all
select * from table2

session 2:
begin tran
select * from table2
union all
select * from table1

dev2dev
Starting Member

48 Posts

Posted - 2007-12-27 : 07:00:42

thanks rmiao,

but i am not able to produce 1205 error. it has been now over 6 1/2 hours and two process are still waiting, no timeout error no deadlock error (btw: am executing this in 2000 server, i think this should not make difference)

what should i do for getting the 1205 error/warning

thanks again in advance

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-27 : 22:25:44

What does 'sp_who2 active' say?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 23:03:55

session 1:
begin tran
update table1 set column1 = 1
update table2 set column2 = 2

session 2:
begin tran
update table2 set column2 = 2
update table1 set column1 = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources