Sybase ASE: "Your server command encountered a deadlock situation"

Question

When running a stored procedure (from a .NET application) that does an INSERT and an UPDATE, I sometimes (but not that often, really) and randomly get this error:

ERROR [40001] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Your server command (family id #0, process id #46) encountered a deadlock situation. Please re-run your command.

How can I fix this?

Thanks.

Selfseeker

Do you know what a deadlock is, why a deadlock can happen ... and why it is happening with your code? Have you tried Googling for like "sybase" and "deadlock"? – Airway

Yes I know what it is, yes I have googled. The thing is, the deadlock happens very rarely. As the query is simple (an update and an insert), it should at worst be delayed by the server if some other lock is blocking it, not just throw it out. Also, the error does not say what the deadlock was on (what table, row, etc.) which makes it difficult to solve the problem. I can't manually prevent 2 queries from arriving on the server at the same time! – Selfseeker

A deadlock never delays other processes it stops the other process dead - I would read more on deadlocks as you havn't shown understanding – Yoho

Well that's the whole point, Mark. I'm saying it /should/ be delayed, as in, that is the behaviour that is expected. Of course, the problem here is that is not the behaviour I am getting, as instead of being delayed, it is causing a deadlock and being completely blocked. – Selfseeker

Answer 1

Your best bet for solving you deadlocking issue is to set "print deadlock information" to on using

sp_configure "print deadlock information", 1

Everytime there is a deadlock this will print information about what processes were involved and what sql they were running at the time of the dead lock.

If your tables are using allpages locking. It can reduce deadlocks to switch to datarows or datapages locking. If you do this make sure to gather new stats on the tables and recreate indexes, views, stored procedures and triggers that access the tables that are changed. If you don't you will either get errors or not see the full benefits of the change depending on which ones are not recreated.

Seleucia

Answer 2

I have a set of long term apps which occasionally over lap table access and sybase will throw this error. If you check the sybase server log it will give you the complete info on why it happened. Like: The sql that was involved the two processes trying to get a lock. Usually one trying to read and the other doing something like a delete. In my case the apps are running in separate JVMs, so can't sychronize just have to clean up periodically.

Hecto

Answer 3

Assuming that your tables are properly indexed (and that you are actually using those indexes - always worth checking via the query plan) you could try breaking the component parts of the SP down and wrapping them in separate transactions so that each unit of work is completed before the next one starts.

 begin transaction 
   update mytable1 
     set mycolumn = "test"
   where ID=1

 commit transaction
 go

 begin transaction 
  insert into mytable2 (mycolumn) select mycolumn from mytable1 where ID = 1
 commit transaction
 go

Disbursement

Actually, today I had the same problem again, with a stored procedure that contains only a single statement (an INSERT)... – Selfseeker