Geek Answers Handbook

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

When I run a stored procedure (from a .NET application) that executes INSERT and UPDATE, I sometimes (but not so often, actually) and accidentally get this error:

ERROR [40001] [DataDirect] [Sybase Wire ODBC Protocol Driver] [SQL Server] Your server command (family ID # 0, process ID No. 46) has encountered a deadlock situation. Repeat the command.

How can i fix this?

Thanks.

+6
sql stored-procedures sybase-ase database-deadlocks
source share
3 answers

The best way to solve the deadlock problem is to set "print lock information" when using

sp_configure "print lock information", 1

Each time there is a dead end, it will print information about what processes were involved and what they performed during a deadlock.

If your tables use all page locking. It can reduce locks to switch to datarows or datapages locks. If you do, be sure to collect new table statistics and recreate indexes, views, stored procedures, and triggers that refer to tables that have changed. If you do not, you will either receive errors or you will not see all the benefits of the change, depending on which ones are not recreated.

+6
source share

I have a set of long-term applications that sometimes use access to the lap table, and sybase will generate this error. If you check the sybase server log, it will give you full information on why this happened. For example: sql, in which two processes are involved trying to get a lock. Usually one tries to read and the other does something like delete. In my case, applications run in separate JVMs, so synchronization cannot be synchronized periodically.

+2
source share

Assuming that your tables are correctly indexed (and that you actually use these indexes - it’s always worth checking according to the query plan), you can try to break down the SP components and wrap them in separate transactions so that each unit of work is completed before the next launch.

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
0
source share

More articles:


All Articles