6

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.

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Laurent
  • 5,953
  • 14
  • 43
  • 59
  • 1
    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"? – ChrisW Jul 10 '09 at 01:45
  • 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! – Laurent Jul 10 '09 at 03:57
  • 1
    A deadlock never delays other processes it stops the other process dead - I would read more on deadlocks as you havn't shown understanding – mmmmmm Sep 25 '09 at 10:05
  • 1
    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. – Laurent Oct 02 '09 at 00:48

3 Answers3

8

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.

Todd Pierce
  • 806
  • 5
  • 8
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.

Jim Jones
  • 287
  • 3
  • 14
0

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
  • Actually, today I had the same problem again, with a stored procedure that contains only a single statement (an INSERT)... – Laurent Aug 04 '09 at 00:29