How do I simulate a deadlock on Sybase?

Posted on 2006-05-01
Last Modified: 2008-01-09
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.


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.

Question by:vgirijanaidu
    LVL 24

    Accepted Solution

    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.


    XACT 1:

    [code if the field is not null]

    XACT 2:

    [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.
    LVL 6

    Assisted Solution

    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


        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




    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
    We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now