Defining Transactions

You can carry out transaction processing with Microsoft SQL Server in three ways:

AutoCommit ”Every Transact -SQL statement is its own transaction and automatically commits when it finishes. This is the default mode in which SQL Server operates.
Explicit ”This approach provides programmatic control of the transaction using the BEGIN TRAN and COMMIT / ROLLBACK TRAN / WORK commands.
Implicit ”SQL Server is placed into a mode of operation in which issuing certain SQL commands automatically starts a transaction. The developer must finish the transaction by explicitly issuing the COMMIT / ROLLBACK TRAN / WORK commands.

Each of these methods is discussed in the following sections.

NOTE

The terms for explicit and implicit transactions can be somewhat confusing as to which is which. The way to keep them straight is to think of how a multistatement transaction is initiated, not how it is completed. AutoCommit transactions are in a separate category because they are both implicitly started and committed. Implicit and explicit transactions have to be explicitly ended, but explicit transactions must also be explicitly started with the BEGIN TRAN statement, whereas no BEGIN TRAN is necessary to start a multistatement transaction when in implicit transaction mode.

AutoCommit Transactions

AutoCommit transactions are the default transaction mode for SQL Server. Each individual Transact-SQL command automatically commits or rolls back its work at the end of its execution. Each SQL statement is considered to be its own transaction, with begin and end control points implied .

 [implied begin transaction]  UPDATE account SET balance = balance + 1000 WHERE account_no = "123456789" [implied commit or rollback transaction]

If an error is present within the execution of the statement, the action is undone (rolled back); if no errors occurred, the action is completed and the changes are saved.

Now consider the banking transaction again and write the T-SQL statements to move money from the savings account to the checking account. Assume it was written as follows :

 declare @checking_account char(10),          @savings_account char(10) select @checking_account = '0003456321',        @savings_account = '0003456322' update account    set balance = balance - 00    where account_number = @checking_account update savings_account    set balance = balance + 00    where account_number = @savings_account

What would happen if an error occurred updating the savings account? With AutoCommit, each statement is implicitly committed after it completes successfully, so the update for the checking account has already been committed. You would have no way of rolling it back except to write another separate update to add the $1,000 back to the account. If the system crashed during the updates, how would you know which if any completed, and whether you would need to undo any of the changes because the subsequent commands were not executed? You would need some way to group the two commands together as a single logical unit of work so they complete or fail as a whole. SQL Server provides transaction control statements that allow you to create multistatement user -defined transactions.

Explicit User-Defined Transactions

To have complete control of a transaction and define logical units of work that consist of multiple data modifications, you need to write explicit user-defined transactions. Any SQL Server user can make use of the transaction control statements; no special privileges are required.

To start a multistatement transaction, use the BEGIN TRAN command, which optionally takes a transaction name :

 BEGIN TRAN[SACTION] [  transaction_name  [WITH MARK ['  description  ']]]

This name is essentially meaningless as far as transaction management is concerned , and if transactions are nested (which will be discussed later in this chapter), the name is only useful for the outermost BEGIN TRAN statement. Rolling back to any other name, besides a savepoint name, will generate an error and not roll back the transaction. The statements can only be rolled back when the outermost transaction is rolled back.

Naming transactions is really only useful when using the WITH MARK option. If the WITH MARK option is specified, a transaction name must be specified. WITH MARK allows for restoring a transaction log backup to a named mark in the transaction log. (For more information on restoring database and log backups , see Chapter 16, "Database Backup and Restore.") This option allows you to restore a database to a known state, or to recover a set of related databases to a consistent state. However, be aware that BEGIN TRAN records are only written to the log if an actual data modification occurs within the transaction.

A transaction is completed successfully by issuing either a COMMIT TRAN or COMMIT [WORK] statement, or can be undone using either ROLLBACK TRAN or ROLLBACK [WORK] . The syntax of these commands is as follows:

 COMMIT [TRAN[SACTION] [  transaction_name  ]]  [WORK]  ROLLBACK [TRAN[SACTION] [  transaction_name   savepointname  ]]  [WORK]

The COMMIT statement marks the successful conclusion of the transaction. This statement can be coded as COMMIT , COMMIT WORK , or COMMIT TRAN . It makes no difference ”other than that the first two versions are SQL-92 ANSI compliant.

The ROLLBACK statement unconditionally undoes all work done within the transaction. This statement can also be coded as ROLLBACK , ROLLBACK WORK , or ROLLBACK TRAN . The first two commands are ANSI-92 SQL compliant and do not accept user-defined transaction names . ROLLBACK TRAN is required if you want to roll back to a savepoint within a transaction.

The following is an example of how you could code the previously mentioned banking example as a single transaction in SQL Server:

 declare @checking_account char(10),          @savings_account char(10) select @checking_account = '0003456321',        @savings_account = '0003456322' begin tran update account    set balance = balance - 00    where account_number = @checking_account if @@error != 0 begin     rollback tran     return end update savings_account    set balance = balance + 00    where account_number = @savings_account if @@error != 0 begin     rollback tran     return end commit tran

There are certain commands that cannot be specified within a user-defined transaction, primarily because they cannot be effectively rolled back in the event of a failure. In most cases, because of their long-running nature, you would not want them to be specified within a transaction anyway. Here are the commands you cannot specify in a user-defined transaction:

`ALTER DATABASE`	`CREATE DATABASE`	`DROP DATABASE`
`BACKUP DATABASE`	`RESTORE DATABASE`	`RECONFIGURE`
`BACKUP LOG`	`RESTORE LOG`	`UPDATE STATISTICS`

Savepoints

Savepoints allow you to set a marker in a transaction that you can roll back to undo a portion of the transaction, but commit the remainder of the transaction. The syntax is as follows:

 SAVE TRAN[SACTION]  savepointname

Savepoints are not ANSI-SQL 92 compliant, so you must use the SQL Server-specific transaction management commands that allow you to specify a named point within the transaction and then recover back to it.

The following code illustrates the differences between the two types of syntax when using the SAVE TRAN command:

SQL-92 Syntax

SQL Server-Specific Syntax

 BEGIN TRAN mywork   UPDATE table1...    SAVE TRAN savepoint1       INSERT INTO table2...       DELETE table3...       IF @@error = -1          ROLLBACK WORK COMMIT WORK

 BEGIN TRAN mywork    UPDATE table1...    SAVE TRAN savepoint1       INSERT INTO table2...       DELETE table3...       IF @@error = -1          ROLLBACK TRAN savepoint1 COMMIT TRAN

Note the difference between the SQL-92 syntax on the left and the SQL Server “specific syntax on the right. In the SQL-92 syntax, when you reach the ROLLBACK WORK command, the entire transaction is undone, rather than undoing only to the point marked by the savepoint. You have to use the SQL Server “specific ROLLBACK TRAN command and specify the savepoint name to roll back the work to the savepoint and still be able to subsequently roll back or commit the rest of the transaction.

Nested Transactions

By rule, you can't have more than one active transaction per user session within SQL Server. However, consider that you have a SQL batch that issues a BEGIN TRAN statement, and then subsequently invokes a stored procedure, which also issues a BEGIN TRAN statement. Because you can only have one transaction active, what does the BEGIN TRAN inside the stored procedure accomplish? In SQL Server, this leads to an interesting anomaly referred to as nested transactions.

To determine whether transactions are open and how deep they are nested within a connection, you can use the global function called @@trancount .

If no transaction is active, the transaction nesting level is . As a transaction is initiated, the transaction nesting level is incremented; as a transaction completes, the transaction nesting is decremented. The overall transaction remains open and can be entirely rolled back until the transaction nesting level returns to .

You can use the @@trancount to monitor the current status of a transaction. For example, what would SQL Server do when encountering the following transaction (which produces an error because of the reference constraint on the titles table)?

 BEGIN TRAN     DELETE FROM publishers    WHERE pub_id = '0736'

Is the transaction still active? You can find out using the @@trancount function:

 select @@trancount  go -----------           1

In this case, @@trancount returns a value of 1 , which indicates that the transaction is still open and in progress. This means that you can still issue commands within the transaction and commit the changes, or roll back the transaction. Also, if you were to log out of the user session from SQL Server before the transaction nesting level reached , SQL Server would automatically roll back the transaction.

Although nothing can prevent you from coding a BEGIN TRAN within another BEGIN TRAN , doing so has no real benefit even though such cases might occur. However, if you nest transactions in this manner, you must execute a COMMIT statement for each BEGIN TRAN statement issued. This is because SQL Server modifies the @@trancount with each transaction statement and considers the transaction finished only when the transaction nesting level returns to . Table 31.1 shows the effects that transaction control statements have on @@trancount .

Table 31.1. Transaction Statements' Effects on `@@trancount`

Statement	Effect on `@@trancount`
`BEGIN TRAN`	+1
`COMMIT`	-1
`ROLLBACK`	Sets to 0
`SAVE TRAN` `savepoint`	No effect
`ROLLBACK TRAN` `savepoint`	No effect

Following is a summary of how transactional control relates to the values reported by @@trancount :

When you log in to SQL Server, the value of @@trancount for your session is initially .
Each time you execute begin transaction , SQL Server increments @@trancount .
Each time you execute commit transaction , SQL Server decrements @@trancount .
Actual work is committed only when @@trancount reaches again.
When you execute rollback transaction , the transaction is canceled and @@trancount returns to . Notice that rollback transaction cuts straight through any number of nested transactions, canceling the overall main transaction. This means that you need to be careful how you write code that contains a rollback statement. Be sure to return up through all levels so you don't continue executing data modifications that were meant to be part of the larger overall transaction.
Setting savepoints and rolling back to a savepoint do not affect @@trancount or transaction nesting in any way.
If a user connection is lost for any reason when @@trancount is greater than , any pending work for that connection is automatically rolled back. SQL Server requires that multistatement transactions be explicitly committed.
Because the BEGIN TRAN statement increments @@trancount , each BEGIN TRAN statement must be paired with a COMMIT for the transaction to successfully complete.

Take a look at some sample code to show the values of @@trancount as the transaction progresses. This first example is a simple explicit transaction with a nested BEGIN TRAN :

SQL Statement	`@@trancount` Value
SELECT "Starting....." BEGIN TRAN DELETE FROM table1 BEGIN TRAN INSERT INTO table2 COMMIT UPDATE table3 COMMIT
	`1`
	`1`
	`2`
	`2`
	`1`
	`1`

Nested transactions are syntactic only. The only commit tran statement that has an impact on real data is the last one, the statement returning @@trancount to . That statement fully commits the work done by the initial transaction and the nested transactions. Until that final COMMIT TRAN is encountered , all of the work can be rolled back with a ROLLBACK statement.

As a general rule, if a transaction is already active, you shouldn't issue another BEGIN TRAN statement. Check the value of @@trancount to determine if a transaction is already active. If you want to be able to roll back the work performed within a nested transaction without rolling back the entire transaction, set a savepoint instead of issuing a BEGIN TRAN statement. Later in this chapter, you will see an example of how to check @@trancount within a stored procedure to determine if the stored procedure is being invoked within a transaction and issuing a BEGIN TRAN or SAVE TRAN as appropriate.

Implicit Transactions

AutoCommit transactions and explicit user-defined transactions in SQL Server are not ANSI-92 SQL compliant. The ANSI-92 SQL standard states that any data retrieval or modification statement issued should implicitly begin a multistatement transaction that remains in effect until an explicit ROLLBACK or COMMIT statement is issued.

To enable implicit transactions for a connection, you need to turn on the IMPLICIT_TRANSACTIONS session setting. The syntax is as follows:

 SET IMPLICIT_TRANSACTIONS {ON  OFF}

After this option is turned on, transactions will be implicitly started, if not already in progress, whenever any of the following commands are executed:

`ALTER TABLE`	`CREATE`	`DELETE`
`DROP`	`FETCH`	`GRANT`
`INSERT`	`OPEN`	`REVOKE`
`SELECT`	`TRUNCATE TABLE`	`UPDATE`

Note that neither the ALTER VIEW nor ALTER PROCEDURE statement starts an implicit transaction.

Implicit transactions must be explicitly completed by issuing a COMMIT or ROLLBACK , and a new transaction is started again on the execution of any of the preceding commands. If you plan to use implicit transactions, the main thing to be aware of is that locks are held until you explicitly commit the transaction. This can cause problems with concurrency and the ability of the system to back up the transaction log.

Even when using implicit transactions, you can still issue the BEGIN TRAN statement and create transaction nesting. In this next example, IMPLICIT TRANSACTIONS ON has been turned on to see the effect this has on the value of @@trancount .

SQL Statements	`@@trancount` Value
`SET IMPLICIT_TRANSACTIONS ON`
`go`
`INSERT INTO table1`	`1`
`UPDATE table2`	`1`
`COMMIT`
`go`
`BEGIN TRAN`	`2`
`DELETE FROM table1`	`2`
`COMMIT`	`1`
`go`
`DROP TABLE table1`	`1`
`COMMIT`

As you can see in this second example, if a BEGIN TRAN is issued while a transaction is still active, transaction nesting will occur and a second COMMIT is required to finish the transaction. The main difference is that a BEGIN TRAN was not required to start the transaction. The first INSERT statement initiated the transaction. When you are running in implicit transaction mode, you don't need to issue a BEGIN TRAN statement; in fact, you should avoid it to prevent transaction nesting and the need for multiple commits.

Look at the previous banking transaction using implicit transactions:

 set implicit_transactions on  go declare @checking_account char(10),         @savings_account char(10) select @checking_account = '0003456321',        @savings_account = '0003456322' update account    set balance = balance - 00    where account_number = @checking_account if @@error != 0 begin     rollback     return end update savings_account    set balance = balance + 00    where account_number = @savings_account if @@error != 0 begin     rollback     return end commit

This example is nearly identical to the explicit transaction example except for the lack of a BEGIN TRAN statement. In addition, when in implicit transaction mode, you cannot roll back to a named transaction because no name is assigned when the transaction is invoked implicitly. You can, however, still set savepoints and roll back to savepoints to partially roll back work within an implicit transaction.

TIP

If you need to know within your SQL code whether implicit transactions are enabled so you can avoid issuing explicit BEGIN TRAN statements, you can check the @@options function. @@options returns a bitmap indicating which session level options are enabled for the current session. If bit 2 is on, then implicit transactions are enabled. The following code snippet can be used in stored procedures or SQL batches to check this value and decide whether to issue a BEGIN TRAN statement:

[View full width]

  [View full width] 
 if @@options & 2 != 2 -- if bit 2 is not turned on      BEGIN TRAN --a begin tran can be issued since implicit  transactions are off ...

Implicit Transactions Versus Explicit Transactions

When would you want to use implicit transactions versus explicit transactions? If you are porting an application from another database environment that used an implicit transaction, that application will port over more easily with fewer code changes if you run in implicit transaction mode. Also, if the application you are developing needs to be ANSI-compliant and run across multiple database platforms with minimal code changes, you might want to use implicit transactions.

If you use implicit transactions in your applications, just be sure to issue COMMIT statements as frequently as possible to prevent leaving transactions open and holding locks for an extended period of time, which can have an adverse impact on concurrency and overall system performance.

If your application is only going to be hosted on SQL Server, it is recommended that you use AutoCommit and explicit transactions so that changes are committed as quickly as possible and only those logical units of work that are explicitly defined will contain multiple commands within a transaction.