13
Your server command (family id #%d, process id #%d) encountered a deadlock situation. Please re-run your command.
This error occurs when a process tries to acquire a lock on an object that is locked by a second process when the second process is waiting for a lock on an object that has been locked by the first process. This situation is a deadlock, and can involve more than two processes.
Adaptive Server detects this situation, rolls back the transaction that has accumulated the least amount of CPU time, and notifies the application program of this action with error 1205. This allows other users’ processes to move forward.
Deadlocks are caused by a number of situations, including:
Transactions modify tables in different orders. There is a greater chance of deadlock between two transactions if one is processing in the sequence A–B–C while the other runs C–B–A.
Transactions access tables using a nonclustered index. If the optimizer chooses a different nonclustered index for the same table for two different queries, a nonclustered index is not in the physical data sequence and the two processes are acquiring page locks in a random order. Therefore, there is a greater chance that one process will lock a page that the other needs.
Transactions that use the keyword holdlock or use the set isolation level command to hold shared locks. When holdlock is appended to a select transaction it holds the shared lock for the remainder of the transaction. This increases the risk of deadlock.
Transactions that require a long time to run. The longer a transaction runs, the more likely it is that another user will require a resource held by the transaction.
Restart the transaction that has been rolled back. To minimize future occurrences of deadlocks, use any of the following procedures that apply to your site.
Each application should have deadlock handling routines. Refer to dbsetuserdata in the “Routines” chapter of the most recent version of the Open Client DB-Library/C Reference Manual (within the Open Server 15.0, Open Client 15.0 and SDK 15.0 top-level topic on the Sybase Technical Library Product Manuals Web site) for a sample deadlock handling routine.
Using transactions constructed to avoid deadlocks greatly reduces their occurrence. Some techniques for writing transactions that avoid deadlock include:
Access tables in the same order in each transaction. Use coding conventions that require all transactions that access several tables to process them in the same order.
Access tables via a clustered index when possible. If it is not possible to change a nonclustered index to a clustered index to minimize deadlocks, then trap the deadlock error in the application and provide appropriate recovery routines.
If you are using holdlock, decide whether you really need to be using it. Use holdlock only when you require repeatable reads within a transaction.
Avoid long-running transactions. Some ways to avoid long-running transactions are:
Never allow user interaction within a transaction.
Separate logical units of work into transactions. For example, acquiring a sequential key from a key table for use in an insert statement can be separated into transactions similar to the following (in the first set of commands, you acquire the key; in the second set of commands, you use the key to do the insert):
1> declare @key int 2> begin transaction 3> update key_table set key = key + 1 4> select @key = key 5> commit transaction 6> go 1> insert <mytable> 2> values (@key, ...) 3> go
Check whether client applications, including third party tools, allow users to accidentally create long running transactions.
Refer to “Lock management” in the most recent version of Performance and Tuning: Monitoring and Analyzing.
All versions
