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

sql stored-procedures sap-ase database-deadlocks
Laurent picture Laurent · Jul 10, 2009 · Viewed 17.2k times · Source

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.

Answer

Todd Pierce picture Todd Pierce · Jul 25, 2009

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.

Related questions

Formatting RAISERROR messages in SYBASE ASE 12.5

In a stored procedure, I am trying to test if a parameter is null or less then 1, and if so, raise an error with a friendly message, and the value of the parameter. Here is my code: IF @ID IS …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More