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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Transactions and Isolation Levels » Transaction blocking and deadlock » Transaction blocking

The blocking option Next Page


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:

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.

Determining who is blocked

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.

To use deadlock reporting
  1. 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].

  2. Retrieve the deadlock information using sa_report_deadlocks.

    CALL sa_report_deadlocks()

    For more information, see sa_report_deadlocks system procedure.

  3. Viewing deadlocks from Sybase Central

    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.

    To use Sybase Central deadlock reporting
    1. Select the database in the left pane of Sybase Central, and then choose File > Options.

      The Database Options dialog appears.

    2. Turn on the log_deadlocks option:

      The database server records information about deadlocks in an internal buffer.

      1. Select log_deadlocks in the Options list.

      2. Type On in the value field.

      3. Click Set Temporary Now.

      4. Click Close.

      For more information, see log_deadlocks option [database].

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