Home

	Expand Search

Transact-SQL User's Guide

Chapter 18 Transactions: Maintaining Data Consistency and Recovery

Using transactions in stored procedures and triggers

Using transactions in stored procedures and triggers

You can use transactions in stored procedures and triggers just as with statement batches. If a transaction in a batch or stored procedure invokes another stored procedure or trigger containing a transaction, that second transaction is nested into the first one.

The first explicit or implicit (using chained mode) begin transaction starts the transaction in the batch, stored procedure, or trigger. Each subsequent begin transaction increments the nesting level. Each subsequent commit transaction decrements the nesting level until it reaches 0. Adaptive Server then commits the entire transaction. A rollback transaction aborts the entire transaction up to the first begin transaction regardless of the nesting level or the number of stored procedures and triggers it spans.

In stored procedures and triggers, the number of begin transaction statements must match the number of commit transaction statements. This also applies to stored procedures that use chained mode. The first statement that implicitly begins a transaction must also have a matching commit transaction.

Figure 18-1 demonstrates how nested transaction statements work within stored procedures:

rollback transaction statements in stored procedures do not affect subsequent statements in the procedure or batch that originally called the procedure. Adaptive Server executes subsequent statements in the stored procedure or batch. However, rollback transaction statements in triggers abort the batch so that subsequent statements are not executed.

rollback statements in triggers: 1) roll back the transaction, 2) complete subsequent statements in the trigger, and 3) abort the batch so that subsequent statements in the batch are not executed.

For example, the following batch calls the stored procedure myproc, which includes a rollback transaction statement:

begin tran
update titles set ...
insert into titles ...
execute myproc
delete titles where ...

The update and insert statements are rolled back and the transaction is aborted. Adaptive Server continues the batch and executes the delete statement. However, if there is an insert trigger on a table that includes a rollback transaction, the entire batch is aborted and the delete is not executed. For example:

begin tran
update authors set ...
insert into authors ...
delete authors where ...

Different transaction modes or isolation levels for stored procedures have certain requirements, which are described under "Transaction modes and stored procedures". Triggers are not affected by the current transaction mode, since they are called as part of a data modification statement.

Errors and transaction rollbacks

Errors that affect data integrity can affect the state of implicit or explicit transactions:

• Errors with severity levels of 19 or greater

  Since these errors terminate the user connection to the server, any errors of level 19 or greater that occur while a user transaction is in progress abort the transaction and roll back all statements to the outermost begin transaction. Adaptive Server always rolls back any uncommitted transactions at the end of a session.

• Errors in data modification commands that affect data integrity (see Table 18-5):

  • Arithmetic overflow and divide-by-zero errors (effects on transactions can be changed with the set arithabort arith_overflow command)

  • Permissions violations

  • Rules violations

  • Duplicate key violations

Table 18-4 summarizes how rollback affects Adaptive Server processing in several different contexts (such as within a transaction, stored procedure, or trigger):

In stored procedures and triggers, the number of begin transaction statements must match the number of commit statements. A procedure or trigger that contains unpaired begin/commit statements produces a warning message when it is executed. This also applies to stored procedures that use chained mode: the first statement that implicitly begins a transaction must have a matching commit.

With duplicate key errors and rules violations, the trigger completes (unless there is also a return statement), and statements such as print, raiserror, or remote procedure calls are performed. Then, the trigger and the rest of the transaction are rolled back, and the rest of the batch is aborted. Remote procedure calls executed from inside a normal SQL transaction (not using the DB-Library two-phase commit) are not rolled back by a rollback statement.

Table 18-5 summarizes how a rollback caused by a duplicate key error or a rules violation affects Adaptive Server processing in several different contexts:

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.

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.