ASA SQL User's Guide
Using Transactions and Isolation Levels
Transaction blocking and deadlock
Transaction blocking
Transaction blocking can lead to deadlock, a situation in which a set of transactions arrive at a state where none of them can proceed.
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 database 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.
Adaptive Server Anywhere automatically rolls back the last transaction that became blocked (eliminating the deadlock situation), and returns an error to that transaction indicating which form of deadlock occurred.
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.
You can use the sa_conn_info system procedure to determine which connections are blocked on which other connections. This procedure returns a result set consisting of a row for each connection. One column of the result set lists whether the connection is blocked, and if so which other connection it is blocked on.
For more information, see sa_conn_info system procedure.