Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 18 Transactions: Maintaining Data Consistency and Recovery

Using transactions in stored procedures and triggers

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 would fail if it called 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 Adaptive Server's 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 Sybase system procedures (except for sp_procxmode) that can be invoked by sessions using any transaction mode. (For information about sp_proxmode, see "Setting transaction modes for stored procedures".) If no transaction is active when you execute a system procedure, Adaptive Server turns off chained mode for the duration of the procedure. Before returning, it resets the mode its original setting.

Adaptive Server 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.

Setting transaction modes for stored procedures

Use sp_procxmode to display or change the transaction mode of stored procedures. For example, to change the transaction mode for the stored procedure byroyalty to "chained," enter:

sp_procxmode byroyalty, "chained"

sp_procxmode "anymode" lets stored procedures run under either chained or unchained transaction mode. For example:

sp_procxmode byroyalty, "anymode"

Use sp_procxmode without any parameter values to get the transaction modes for all stored procedures in the current database:

sp_procxmode

procedure name               transaction mode
-------------------------    --------------------
byroyalty                    Any Mode 
discount_proc                Unchained 
history_proc                 Unchained 
insert_sales_proc            Unchained 
insert_salesdetail_proc      Unchained 
storeid_proc                 Unchained 
storename_proc               Unchained 
title_proc                   Unchained 
titleid_proc                 Unchained 
 
(9 rows affected, return status = 0)

You can use sp_procxmode only in unchained transaction mode.

To change a procedure's transaction mode, you must be a System Administrator, the Database Owner, or the owner of the procedure.


	Expand Search