Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Creating Databases » Using transactions and isolation levels » Transaction blocking and deadlock » Transaction blocking

 

Deadlock

Transaction blocking can lead to deadlock, a situation in which a set of transactions arrive at a state where none of them can proceed.

Reasons for deadlocks

A deadlock can arise for two reasons:

  • A cyclical blocking conflict   Transaction A is blocked on transaction B, and transaction B is blocked on transaction A. Clearly, more time will not solve the problem, and one of the transactions must be canceled, allowing the other to proceed. The same situation can arise with more than two transactions blocked in a cycle.

  • All active database threads are blocked   When a transaction becomes blocked, its database thread is not relinquished. If the server is configured with three threads and transactions A, B, and C are blocked on transaction D which is not currently executing a request, then a deadlock situation has arisen since there are no available threads.

To eliminate a transactional deadlock, SQL Anywhere selects a connection from those involved in the deadlock, rolls back the changes for the transaction active on that connection and returns an error. SQL Anywhere selects the connection to roll back using an internal heuristic that prefers the connection with the smallest blocking wait time left as determined by the blocking_timeout option. If all connections are set to wait forever, then the connection that caused the server to detect a deadlock is selected as the victim connection.

To eliminate a thread deadlock, SQL Anywhere selects the last thread to block, rolls back the changes for the transaction active on that connection and returns an error.

The number of database threads that the server uses depends on the individual database's setting.

For information about setting the number of database threads, see Controlling threading behavior.


Determining who is blocked
Viewing deadlocks from Sybase Central