Wednesday, March 14, 2012

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
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;


3 comments:

Justin Willey said...

Very nice idea, great for letting essential processes complete.

Anonymous said...

That's such a well-thought suggestion, why don't you put it on the forum and let it get its bunch of up-votes?

Volker

BTW: The CAPTCHTAs seem to get really hard to solve...

Jeff Albion said...

I should add that there is also the "official" channel to request product enhancements - the (free) "Create an Enhancement Request Case" option underneath Case-Express:

http://case-express.sybase.com/cx/

(If you're scared off by the selection provided on the menus here, here's how to get to the 'SQL Anywhere' feature requests:)

* Base Product: SQL Anywhere

* Component Product: 17330 - SQL Anywhere OEM for Windows

(this option always allows a selection for all of current SQL Anywhere versions)

* Version: 1201 (this category should also show all historic SQL Anywhere versions)

* Priority: 4 (Information Request)

---

I have gone ahead and created a support and engineering case (#11728867 / CR #703272 ) for this feature to be considered in a future release - thank you for the enhancement suggestion Breck!