• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1417
  • Last Modified:

How do I simulate a deadlock on Sybase?

I have a stored proc that inserts a row. If a field is not null, it performs the insertion and it increases the value of that field by 1 , else it inserts a record with that field set to 1.

However this is run as part of a batch job, it ends up in a deadlock sometimes and when it is re-run, I get an error saying that I cannot perform the insertion as a duplicate entry exists. So, I decided to encapsulate the 2 inserts (1 if the field is null and the other insert for the case when the field is not null) inside a transaction as follows.

BEGIN TRANSACTION
code....
COMMIT TRANSACTION

However, I would like to test it now to see if it causes the deadlock still or if I still get the duplicate row error, but I dont know how to simulate it.

Any help will greatly be appreciated.

Many Thanks.

0
vgirijanaidu
Asked:
vgirijanaidu
2 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
You're only touching the same table? If this is the case, then your deadlock is probably data vs index, and if you are using ASE 11.9.2 or above, setting the table's locking scheme to "datapages" will fix this. (Don't bother going all the way to "datarows" - it's more overhead, and I suspect it won't help you here.)

Deadlocks occur because of a circular lock dependency between two or more transactions. So if your code has two different paths, put each one in a separate transaction (in separate connections) and you'll be able to simulate this.

ie.

XACT 1:

BEGIN TRAN
[code if the field is not null]


XACT 2:

BEGIN TRAN
[code if the field IS null]


Note I don't issue the COMMITs - this will hold all write locks open and make it easier to deadlock.
0
 
ChrisKingCommented:
you need two connections (and assuming you want you app to be the deadlock victom)

the easiest way it to use 2 tables
 - tableone (have 100 to 1000 rows)
 - tabletwo (does not really matter how big)

connection1 (use isql or some other query tool)

    BEGIN TRAN
    UPDATE tableone SET columnone = 1      -- this will update all rows of this table and keep the lock

connection2 (your application)

    BEGIN TRAN
    UPDATE tabletwo SET columntwo = 2 WHERE id = 1
    UPDATE tableone SET columnone = 1      -- this will get blocked by connection1

connection1

    UPDATE tabletwo SET columntwo = 2 WHERE id = 1

this will now cause the deadlock and you app should become the vitom (because it has done less I/O)
so now ROLLBACK your query and you should be able to try this many times

connection1

    ROLLBACK

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now