Transact-SQL User's Guide

Home

Selecting the transaction mode and isolation level

Transact-SQL User's Guide

Chapter 18 Transactions: Maintaining Data Consistency and Recovery

Using transactions

The begin transaction and commit transaction commands tell Adaptive Server to process any number of individual commands as a single unit. rollback transaction undoes the transaction, either back to its beginning, or back to a savepoint. You define a savepoint inside a transaction using save transaction.

Transactions give you control over transaction management. In addition to grouping SQL statements to behave as a single unit, they improve performance, since system overhead is incurred once per transaction, rather than once for each individual command.

Any user can define a transaction. No permission is required for any of the transaction commands.

The following sections discuss general transaction topics and transaction commands, with examples.

Allowing data definition commands in transactions

You can use certain data definition language commands in transactions by setting the ddl in tran database option to true. If ddl in tran is true in a particular database, you can issue commands such as create table, grant, and alter table inside transactions in that database. If ddl in tran is true in the model database, you can issue the commands inside transactions in all databases created after ddl in tran was set to true in model. To check the current settings of ddl in tran, use sp_helpdb.

Warning!

Be careful when using data definition commands. The only scenario in which using data definition language commands inside transactions is justified is in create schema. Data definition language commands hold locks on system tables such as sysobjects. If you use data definition language commands inside transactions, keep the transactions short.

Avoid using data definition language commands on tempdb within transactions; doing so can slow performance to a halt. Always leave ddl in tran set to false in tempdb.

To set ddl in tran to true, enter:

sp_dboption database_name,"ddl in tran", true

Then execute the checkpoint command in that database.

The first parameter specifies the name of the database in which to set the option. You must be using the master database to execute sp_dboption. Any user can execute sp_dboption with no parameters to display the current option settings. To set options, however, you must be either a System Administrator or the Database Owner.

The following commands are allowed inside a transaction only if the ddl in tran option to sp_dboption is set to true:

DDL commands allowed in transactions
alter table (you cannot issue the alter table command with a partition or unpartition clause within a user-defined transaction)	create default create index create procedure create rule create schema create table create trigger create view	drop default drop index drop procedure drop rule drop table drop trigger drop view	grant revoke

System procedures that change the master database or create temporary tables cannot be used inside transactions.

Never use the following commands inside a transaction:

DDL commands not allowed in transactions
alter database alter table...partition alter table...unpartition create database	disk init dump database dump transaction drop database	load transaction load database reconfigure	select into update statistics truncate table

System procedures that are not allowed in transactions

You cannot use the following system procedures within transactions:

sp_helpdb, sp_helpdevice, sp_helpindex, sp_helpjoins, sp_helpserver, sp_lookup, and sp_spaceused (because they create temporary tables)
sp_configure
System procedures that change the master database

Beginning and committing transactions

The begin transaction and commit transaction commands can enclose any number of SQL statements and stored procedures. The syntax for both statements is:

begin {transaction | tran} [transaction_name]

commit {transaction | tran | work} [transaction_name]

transaction_name is the name assigned to the transaction. It must conform to the rules for identifiers.

The keywords transaction, tran, and work (in commit transaction) are synonymous; you can use one in the place of the others. However, transaction and tran are Transact-SQL extensions; only work is SQL standards-compliant.

Here is a skeletal example:

begin tran  
  statement 
    procedure 
    statementcommit tran

commit transaction does not affect Adaptive Server if the transaction is not currently active.

Rolling back and saving transactions

If you must cancel a transaction before it commits--either because of some failure or because of a change by the user--you must undo all of its completed statements or procedures. Refer to Table 18-4 for the effects of rollback during processing.

You can cancel or roll back a transaction with the rollback transaction command any time before commit transaction has been given. Using savepoints, you can cancel either an entire transaction or part of it. However, you cannot cancel a transaction after it has been committed.

The syntax of rollback transaction is:

rollback {transaction | tran | work} 
     [transaction_name | savepoint_name]

A savepoint is a marker that the user puts inside a transaction to indicate a point to which it can be rolled back. You can commit only certain portions of a batch by rolling back the undesired portion to a savepoint before committing the entire batch.

You can insert a savepoint by putting a save transaction command in the transaction. The syntax is:

save {transaction | tran} savepoint_name

The savepoint name must conform to the rules for identifiers.

If no savepoint_name or transaction_name is given with rollback transaction, the transaction is rolled back to the first begin transaction in a batch.

Here is how you can use the save transaction and rollback transaction commands:

begin tran 
    statements               Group
A 
    save tran mytran 
        statements           Group
B 
    rollback tran mytran     Rolls
back group B 
    statements               Group
C 
commit tran                   Commits
groups A and C

Until you issue a commit transaction, Adaptive Server considers all subsequent statements to be part of the transaction, unless it encounters another begin transaction statement. At that point, Adaptive Server considers all subsequent statements to be part of the new, nested transaction. Nested transactions are described under "Nested transactions".

rollback transaction or save transaction does not affect Adaptive Server and does not return an error message if the transaction is not currently active.

You can also use save transaction to create transactions in stored procedures or triggers in such a way that they can be rolled back without affecting batches or other procedures. For example:

create proc myproc as 
begin tran 
save tran mytran statements 
if ... 
    begin 
        rollback tran mytran  
    /* 
    ** Rolls back to savepoint. 
    */
        commit tran 
    /* 
    ** This commit needed; rollback to a savepoint 
    ** does not cancel a transaction. 
    */
   end

else 
commit tran 
    /*
    ** Matches begin tran; either commits 
    ** transaction (if not nested) or 
    ** decrements nesting level.  
    */

Unless you are rolling back to a savepoint, use transaction names only on the outermost pair of begin/commit or begin/rollback statements.

Warning!

Transaction names are ignored, or can cause errors, when used in nested transaction statements. If you are using transactions in stored procedures or triggers that could be called from within other transactions, do not use transaction names.

Checking the state of transactions

The global variable @@transtate keeps track of the current state of a transaction. Adaptive Server determines what state to return by keeping track of any transaction changes after a statement executes. @@transtate may contain the following values:

@@transtate values
Value	Meaning
0	Transaction in progress. A transaction is in effect; the previous statement executed successfully.
1	Transaction succeeded. The transaction completed and committed its changes.
2	Statement aborted. The previous statement was aborted; no effect on the transaction.
3	Transaction aborted. The transaction aborted and rolled back any changes.

Adaptive Server does not clear @@transtate after every statement. In a transaction, you can use @@transtate after a statement (such as an insert) to determine whether it was successful or aborted, and to determine its effect on the transaction. The following example checks @@transtate during a transaction (after a successful insert) and after the transaction commits:

begin transaction
insert into publishers (pub_id) values ("9999")

(1 row affected)

select @@transtate

----------
         0
 
(1 row affected)

commit transaction
select @@transtate

----------
         1
 
(1 row affected)

The next example checks @@transtate after an unsuccessful insert (due to a rule violation) and after the transaction rolls back:

begin transaction
insert into publishers (pub_id) values ("7777")

Msg 552, Level 16, State 1:
A column insert or update conflicts with a rule bound to the column. The command is aborted. The conflict occured in database 'pubs2', table 'publishers', rule 'pub_idrule', column 'pub_id'.

select @@transtate

----------
        2
 
(1 row affected)

rollback transaction

select @@transtate

----------
        3
  
(1 row affected)

Adaptive Server does not clear @@transtate after every statement. It changes @@transtate only in response to an action taken by a transaction. Syntax and compile errors do not affect the value of @@transtate.

Nested transactions

You can nest transactions within other transactions. When you nest begin transaction and commit transaction statements, the outermost pair actually begin and commit the transaction. The inner pairs just keep track of the nesting level. Adaptive Server does not commit the transaction until the commit transaction that matches the outermost begin transaction is issued. Normally, this transaction "nesting" occurs as stored procedures or triggers that contain begin/commit pairs call each other.

The @@trancount global variable keeps track of the current nesting level for transactions. An initial implicit or explicit begin transaction sets @@trancount to 1. Each subsequent begin transaction increments @@trancount, and a commit transaction decrements it. Firing a trigger also increments @@trancount, and the transaction begins with the statement that causes the trigger to fire. Nested transactions are not committed unless @@trancount equals 0.

For example, the following nested groups of statements are not committed by Adaptive Server until the final commit transaction:

begin tran
    select @@trancount
    /* @@trancount = 1 */

 
    begin tran
        select @@trancount
        /* @@trancount = 2 */

 
        begin tran
            select @@trancount
            /* @@trancount = 3 */
        commit tran

 
    commit tran

 
commit tran

 
select @@trancount
/* @@ trancount = 0 */

When you nest a rollback transaction statement without including a transaction or savepoint name, it rolls back to the outermost begin transaction statement and cancels the transaction.

Example of a transaction

This example shows how a transaction might be specified:

begin transaction royalty_change

/* A user sets out to change the royalty split */
/* for the two authors of The Gourmet Microwave. */
/* Since the database would be inconsistent */
/* between the two updates, they must be grouped */
/* into a transaction. */

update titleauthor
set royaltyper = 65
from titleauthor, titles
where royaltyper = 75
and titleauthor.title_id = titles.title_id
and title = "The Gourmet Microwave"

update titleauthor
set royaltyper = 35
from titleauthor, titles
where royaltyper = 25
and titleauthor.title_id = titles.title_id
and title = "The Gourmet Microwave"

save transaction percent_changed

/* After updating the royaltyper entries for */
/* the two authors, the user inserts the */
/* savepoint "percent_changed," and then checks */
/* to see how a 10 percent increase in the */
/* price would affect the authors' royalty */
/* earnings. */

update titles
set price = price * 1.1
where title = "The Gourmet Microwave"

select (price * royalty * total_sales) * royaltyper
from titles, titleauthor, roysched
where title = "The Gourmet Microwave"
and titles.title_id = titleauthor.title_id
and titles.title_id = roysched.title_id

rollback transaction percent_changed

/* The transaction rolls back to the savepoint */
/* with the rollback transaction command. */
/* Without a savepoint, it would roll back to */
/* the begin transaction. */

commit transaction


	Expand Search