Cancels a transaction running on a different connection.Syntax
CANCEL TRANSACTION ON CONNECTION connection-idRemarks
The CANCEL TRANSACTION ON CONNECTION statement cancels a transaction running on another connection by executing a ROLLBACK and raising an exception with SQLCODE -xxx.Permissions
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.
DBA authoritySide effects
- DROP CONNECTION statement
- sa_conn_info system procedure
- Using exception handlers in procedures and triggers
SQL/2008 Vendor extension.Examples
The following procedure cancels a transaction running on a connection identified by its connection number.
The following statement cancels the transaction running on the connection with ID number 4.CREATE PROCEDURE cancel_transaction ( IN conn_number INTEGER ) BEGIN EXECUTE IMMEDIATE STRING ( 'CANCEL TRANSACTION ON CONNECTION ' || conn_number ); END;
CANCEL TRANSACTION ON CONNECTION 4;