User's Guide

Part III. Using SQL Anywhere

Chapter 19. 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 transaction attempting to acquire the lock is blocked. The section "Locking conflicts and transaction blocking" describes how SQL Anywhere handles transaction blocking.

"Transaction blocking and deadlock" describes how SQL Anywhere handles a deadlock when transactions cannot become unblocked.

Locking conflicts and transaction blocking

When a locking conflict occurs, one transaction must wait for another transaction to complete. A transaction becomes blocked on another transaction. If two transactions simultaneously have a read lock on a row, the behavior when one of them attempts to modify that row (acquire a write lock) depends on the database setting BLOCKING.

• If BLOCKING is ON (the default setting), then the transaction that attempts to write is blocked 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.

Blocking is more likely to occur with higher isolation levels because more locking and more checking is done. Higher isolation levels provide less concurrency.

For information about the BLOCKING option, see "SET OPTION statement".

Transaction blocking and deadlock

Transaction blocking can lead to deadlock, where a set of transactions get into 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 database is configured with three threads (see the THREAD_COUNT option in "SET OPTION statement") 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.

SQL 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.