Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002

    Question Unanswered: Newbie with questions...

    Hello everyone.

    I am new to this board, and frankly, I need help. I develop the applications that run on the Win/UNIX against Sybase server.

    Currently, my problem deals with deadlocking while deleting rows from the database. When I execute a simple statement like:

    delete TABLE_A
    where ID_A = 'ABC'
    and ID_B = ' 1'
    and (NUM_COL_1 = 0 or NUM_COL_2 = 3)

    I am getting a standard deadlocking error. This is not a part of the transaction, it is a single SQL query.

    Any help would be appreciated.
    Thank you.

  2. #2
    Join Date
    Mar 2001
    Lexington, KY
    It is probably table scanning (or scanning many rows) due to insufficient indexing.

    Also, what is the lockscheme of the table? It sounds like you are deleting many rows multiple times causing deadlocks.


  3. #3
    Join Date
    Dec 2002
    Thank you for your answer.

    The lock scheme is all pages.

    There are few indexes on the table, nut none of them includes all three of these elements.

    If the table scheme is as follows:


    the indexes are:
    ID_A, ID_B, REC_TYPE, CUST_ID, REC_DATE => clustered, unique
    NUM_COL => nonclustered
    ID_A, ID_B => nonclustered
    CUST_ID => nonclustered
    NUM_COL => nonclustered

    So, if I create another index that contains ID_A, ID_B and NUM_COL, it might decrease the deadlocks? I assume I could investigate to change the lock scheme to row based as well.

  4. #4
    Join Date
    Dec 2002
    I don't believe another index will help, may even make it worse. However, do this:

    your DELETE statement

    Check the showplan of the current delete. Make sure it is traversing the clustered index using the first 2 columns as keys. More than likely, you are deadlocking due to the # of non-clustered indexes on the table being maintained by the delete. It would seem that under concurrent activity, the deadlock will be due to someone else banging into your delete as it holds the locks on the pages of the NC-Indexes while the delete occurs.

    One thing not mentioned is - does this table have a delete trigger on it doing anything else?

    Two solutions - one is to make the table ROW or DPO locking so that latches are used on the NC-Index pages and not logical locks.

    Next, you really do not need the non-clustered index on

    ID_A, ID_B => nonclustered

    since your clustered index already has the same two leading columns and this is redundant (do you really need it for a covered index?). At a minimum, removing this index would allow for you to have less maintenance happening on NC-Indexes and possibly help (though not 100%) the deadlocking.

    Deadlocks in ASE are generally not 100% avoidable and so any of the code you write against ASE should do error testing of any SQL that is sent into it for deadlock and retry the unit of work. Don't code stored procedures which could deadlock 1/2 way through and fail. Always code to expect deadlocks even if they never occur.

  5. #5
    Join Date
    Dec 2002

    Thumbs up

    Thanks for your reply, jmc.

    I was playing around with it yesterday...

    I tried extra index, and as you said, it was still deadlocking. Retry loggic is in there, and basically out of 2100 times it deadlocked 30 times. Usually passes on second itteration. There are no delete triggers on the table.

    For now, I'll just let it be, and do more testing with the showplan. The trigger you pointed out is definitely redundant.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts