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.

You can also use the Deadlock event to take action when a deadlock occurs. The event handler can use the sa_report_deadlocks procedure to obtain information about the conditions that led to the deadlock. To retrieve more details about the deadlock from the database server, use the log_deadlocks option and enable the RememberLastStatement feature.

The follow procedure shows you how to set up a table and system event that can be used to obtain information about deadlocks when they occur. If you find that your application has frequent deadlocks, you can use application profiling to help diagnose the cause of the deadlocks.

Take action when a deadlock occurs

Create a table to store the data returned from the sa_report_deadlocks system procedure:

CREATE TABLE DeadlockDetails(
  deadlockId INT PRIMARY KEY DEFAULT AUTOINCREMENT,
  snapshotId BIGINT,
  snapshotAt TIMESTAMP,
  waiter INTEGER,
  who VARCHAR(128),
  what LONG VARCHAR,
  object_id UNSIGNED BIGINT,
  record_id BIGINT,
  owner INTEGER,
  is_victim BIT,
  rollback_operation_count UNSIGNED INTEGER );

Create an event that fires when a deadlock occurs.

This event copies the results of the sa_report_deadlocks system procedure into a table and notifies an administrator about the deadlock:

CREATE EVENT DeadlockNotification
TYPE Deadlock
HANDLER
BEGIN
 INSERT INTO DeadlockDetails WITH AUTO NAME
 SELECT snapshotId, snapshotAt, waiter, who, what, object_id, record_id,
        owner, is_victim, rollback_operation_count
    FROM sa_report_deadlocks ();
 COMMIT;
 CALL xp_startmail ( mail_user ='George Smith',
                     mail_password ='mypwd' );
 CALL xp_sendmail( recipient='DBAdmin',
                   subject='Deadlock details added to the DeadlockDetails table.' );
 CALL xp_stopmail ( );
END;

Set the log_deadlocks option to On:
SET OPTION PUBLIC.log_deadlocks = 'On';

Enable logging of the most-recently executed statement:

CALL sa_server_option( 'RememberLastStatement', 'YES' );

Determining who is blocked

Take action when a deadlock occurs

See also