Product Suggestion: CANCEL TRANSACTION ON CONNECTION nnn

Cancels a transaction running on a different connection.

Syntax

CANCEL TRANSACTION ON CONNECTION connection-id

Remarks

The CANCEL TRANSACTION ON CONNECTION statement cancels a transaction running on another connection by executing a ROLLBACK and raising an exception with SQLCODE -xxx.

The connection-id parameter is an integer constant. You can obtain the connection-id using the sa_conn_info system procedure.

CANCEL TRANSACTION ON CONNECTION is an alternative to DROP CONNECTION when you want to release all the locks held by another connection without completely disrupting the work in progesss on that connection; e.g., when one connection is blocking other connections from getting any work done.

CANCEL TRANSACTION ON CONNECTION is also useful when trying to gather diagnostic information about an errant connection without allowing that connection to continue behaving badly, and it may reduce the sturm und drang effect of DROP CONNECTION when connections operate in a pool. Applications can be designed to catch a CANCEL TRANSACTION ON CONNECTION exception and perform a variety of actions: display a message, terminate, try again, etc.

Permissions

DBA authority

Side effects

None.

See also

• DROP CONNECTION statement
  
• sa_conn_info system procedure
  
• Using exception handlers in procedures and triggers
  

Standards and compatibility

SQL/2008 Vendor extension.

Examples

The following procedure cancels a transaction running on a connection identified by its connection number.

CREATE PROCEDURE cancel_transaction ( IN conn_number INTEGER )
BEGIN
   EXECUTE IMMEDIATE STRING ( 'CANCEL TRANSACTION ON CONNECTION ' || conn_number );
END;

The following statement cancels the transaction running on the connection with ID number 4.

CANCEL TRANSACTION ON CONNECTION 4;

---