|
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 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.
SQL Anywhere automatically rolls back the last statement 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.
The database server provides more detailed deadlock reporting using the log_deadlocks option and sa_report_deadlocks system procedure. When you turn on the log_deadlocks option, the database server records information about the blocked connections in an internal buffer.
Turn on the log_deadlocks option.
SET OPTION PUBLIC.log_deadlocks='On'
The database server records information about deadlocks in an internal buffer.
For more information, see log_deadlocks option [database].
Retrieve the deadlock information using sa_report_deadlocks.
CALL sa_report_deadlocks()
For more information, see sa_report_deadlocks system procedure.
When you are connected to a database in Sybase Central, you can see a diagram of any deadlocks that have occurred in the database since the log_deadlocks option was set to On.
Select the database in the left pane of Sybase Central, and then choose File > Options.
The Database Options dialog appears.
Turn on the log_deadlocks option:
The database server records information about deadlocks in an internal buffer.
Select log_deadlocks in the Options list.
Type On in the value field.
Click Set Temporary Now.
Click Close.
For more information, see log_deadlocks option [database].
Click the Deadlocks tab in the right pane.
A deadlock diagram appears if there are any deadlocks in the database.
Each node in the deadlock diagram represents a connection and gives details about which connection was deadlocked, the user name, and the SQL statement the connection was trying to execute when the deadlock occurred. There are two types of deadlocks: connection deadlocks and thread deadlocks. Connection deadlocks are characterized by a circular dependency for the nodes. A thread deadlock is indicated by nodes that are not connected in a circular dependency, and the number of nodes is equal to the thread limit on the database plus one.