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.
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.