Transaction Modes and Stored Procedures

Stored procedures written to use the unchained transaction mode may be incompatible with other transactions using chained mode, and vice versa.

For example, here is a valid stored procedure using chained transaction mode:

create proc myproc
as
insert into publishers 
    values ("9996", null, null, null)
commit work

A program using unchained transaction mode fails if it calls this procedure because the commit does not have a corresponding begin. You may encounter other problems:

Applications that start a transaction using chained mode may create impossibly long transactions or may hold data locks for the entire length of their session, degrading SAP ASE performance.
Applications may nest transactions at unexpected times. This can produce different results, depending on the transaction mode.

As a rule, applications using one transaction mode should call stored procedures written to use that mode. The exceptions to that rule are SAP system procedures (except for sp_procxmode) that can be invoked by sessions using any transaction mode. If no transaction is active when you execute a system procedure, SAP ASE turns off chained mode for the duration of the procedure. Before returning, it resets the mode its original setting.

SAP ASE tags all procedures with the transaction mode (chained or unchained) of the session in which they are created. This helps avoid problems associated with transactions that use one mode to invoke transactions that use the other mode. A stored procedure tagged as chained is not executable in sessions using unchained transaction mode, and vice versa.

Triggers are executable in any transaction mode. Since they are always called as part of a data modification statement, either they are part of a chained transaction (if the session uses chained mode) or they maintain their current transaction mode.

Warning! When using transaction modes, be aware of the effects each setting can have on your applications.