Creating a Deadlock Reporting Event in Interactive SQL

Create a table and a system event for obtaining information about deadlocks.

Prerequisites

SAP Sybase IQ server has a simplex database.

Task

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 sends an e-mail notification when a deadlock occurs.

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 ='John Smith',
                     mail_password ='mypwd' );
 CALL xp_sendmail( recipient='DBAdmin',
                   subject='Deadlock details added to the DeadlockDetails table.' );
 CALL xp_stopmail ( );
END;

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

Set the log_deadlocks option on.
```
SET OPTION PUBLIC.log_deadlocks = 'On';
```

Enable logging of the most-recently executed statement.

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