User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
When a transaction attempts to acquire a lock on a row, but is forbidden by a lock held by another transaction, a locking conflict arises and the progress of the transaction attempting to acquire the lock is impeded or blocked.
The next section, The BLOCKING option," describes transaction blocking.
Transaction blocking and deadlock describes deadlock, which occurs when two or more transactions are blocked by each other in such a way that none can proceed.
The BLOCKING optionIf two simultaneously transactions have each acquired a read lock on a single row, the behavior when one of them attempts to modify that row depends on the database setting BLOCKING. To modify the row, that transaction must acquire a write lock, yet it cannot do so while the other transaction holds a lock on the row.
If BLOCKING is ON (the default setting), then the transaction that attempts to write waits until the other transaction releases its read lock. At that time, the write goes through.
If BLOCKING has been set to OFF, then the transaction that attempts to write receives an error.
When BLOCKING is set to OFF, the transaction terminates instead of waiting and any changes it has made are rolled back. In this event, try executing the transaction again, later.
Blocking is more likely to occur at higher isolation levels because more locking and more checking is done. Higher isolation levels usually provides less concurrency. How much less depends on the individual natures of the concurrent transactions.
For information about the BLOCKING option, see BLOCKING option.
Transaction blocking and deadlockTransaction blocking can lead to deadlock, where 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 cancels the last transaction that became blocked (eliminating the deadlock situation), and returns an error to that transaction indicating which form of deadlock occurred.
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