Troubleshooting and Error Messages Guide

Home

Troubleshooting and Error Messages Guide

Chapter 3 Error Message Writeups

Lock Manager Errors

Error 1205

Severity

Error message text

Your server command (family id #%d, process id #%d) encountered a deadlock situation. Please re-run your command.

Explanation

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 the 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 via 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. Thus, 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.

Action

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.

Application Error Handling

Each application should have deadlock handling routines. Refer to the dbsetuserdata entry in the Open Client DB-Library Reference Supplement for a sample deadlock handling routine.

Use Well-Constructed Transactions

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.

Additional information

Refer to "Lock Manager" in the System Administration Guide.

Versions in which this error is raised

All versions


	Expand Search