Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004

    Unanswered: Deadlock in transaction

    I have code having a transaction consisting of :
    a select statement and an insert statement. After these two statements I have a commit transaction.

    My database is sybase 12.5 on HP-UX 11.x
    My database locking scheme is "all pages locking".
    The above transactions is being run at isolation level 3.

    When two threads try to begin the above transaction I get a deadlock.
    Sybase shows that the two threads are waiting to acquire an exclusive lock but the other process is holding a shared lock.

    What is happening is:
    The two threads begin the transaction simultaneously thereby acquiring the shared lock for the select statement in the trasaction.
    Immediately after the select query they try to do the insert operation without leaving the shared lock that they have acquired (since isolation level 3 releases locks only after commiting a transaction) and do not get the exclusive lock. So the deadlock occurs.

    The trasaction is such that:
    my select statement gives me the count of rows in a table and using that count value I want to insert a record in the same table i.e while inserting, the count is one of the values. Therefore I have select and insert quiries as a part of the same transaction.

    What other options do I have to remove deadlock scenario? Does sybase have a workaround for this?
    If I use mutex's in my code then things will be too slow as I have 2000-3000 users simultaneously using my application.

  2. #2
    Join Date
    Aug 2004
    So you are manually creating an "identity" column, right?

    Are your really doing count()? That's a time and I/O consuming task, even if you only do an index scan.

    If you already have rows in the table isn't there one where the value in this column is equal to the number of rows in the table, i.e., the last one you inserted? So don't you want to select max(column_in_quetion) from table? If you have an index on that column then that will be much quicker than counting all the rows and will minimize the chances of deadlock.

    You could also use an indentity column but that doesn't necessarily give you exactly what you have stated (since an identity column could have "holes") but it does give you a unique numeric identifier that increases in value and will avoid the type of deadlocks you are describing.

  3. #3
    Join Date
    Sep 2004

    User another table

    You can user another table which will store the max unique value. Let us say table1.
    Let us assume your actual table is table2

    The following sql code should do the needful.
    declare @serial_no int

    begin transaction.

    update table1
    set unique_id=(isnull (select max(unique_id) from table2),0) +1)

    select @serial_no = unique_id from table1

    insert into table2(unique_id,........)

    commit transaction
    This should avoid your deadlock scenario as exclusive lock is acquired on table1 by one process the other processes waiting.


Posting Permissions

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