Transaction Modes | Microsoft SQL Server 2000 Administrators Companion

3 4

A transaction can be started in one of three modes: autocommit, explicit, or implicit. The default mode for SQL Server is autocommit. Let's take a look at what each of these modes means.

Autocommit Mode

In autocommit mode, each T-SQL statement is committed when it finishes—there is no need for any additional statements to control transactions with this mode. In other words, each transaction consists of just one T-SQL statement. Autocommit mode is useful when you are executing statements by interactive command line, using OSQL or SQL Server Query Analyzer, because you do not have to worry about explicitly starting and ending each transaction. You know that each statement will be treated as its own transaction by SQL Server and will be committed as soon as it is finished. Every connection to SQL Server will use autocommit mode until you start an explicit transaction by using BEGIN TRANSACTION or until you specify implicit mode. Once the explicit transaction is ended or implicit mode is turned off, SQL Server returns to autocommit mode.

Explicit Mode

Explicit mode is used most often for programming applications and for stored procedures, triggers, and scripts. When you are executing a group of statements to perform a task, you might need to determine at what point the transaction should start and end so that either the entire group of statements succeeds or the entire group's modifications are rolled back. When you explicitly identify the beginning and the end of a transaction, you are using explicit mode, and the transaction is referred to as an explicit transaction. You specify an explicit transaction by using either T-SQL statements or API functions. This section explains only the T-SQL method; API functions are beyond the scope of this book.

MORE INFO
For information about explicit transactions using ADO and OLE-DB, look up "explicit transactions" in the Books Online index and select "Explicit Transactions" in the Topics Found dialog box. Note that the ODBC API does not support explicit transactions, but only implicit and autocommit transactions.

REAL WORLD Using an Explicit Transaction

Let's look at a situation in which you would need to use an explicit transaction to start and end a task. Suppose we have a stored procedure named Place_Order that handles the database task of placing a customer's order for an item. The steps in this procedure include selecting the customer's current account information, entering the new order ID number and the item ordered, calculating the price of the order plus taxes, updating the customer's account balance to reflect the total price, and checking whether the item is in stock.

We want all of these steps to be completed together or none of them to be completed so that the data will remain consistent in the database. To achieve this, we will group the statements that handle these tasks into an explicit transaction. If we do not group the statements, we could get inconsistent data. For example, if the network connection from the client to the server is broken off after the step that enters the new order number is executed but before the customer balance is updated, the database will be left with a new order for the customer but no charge on the customer's account. In this case, SQL Server commits each statement as soon as it finishes, leaving the stored procedure half-completed at the time of the network disconnect. But if the steps are defined within one explicit transaction, SQL Server automatically rolls back the entire transaction upon disconnection, and the client can later reconnect and execute the procedure again. For more details, see the section "Transaction Rollbacks" later in this chapter.

Using explicit transactions when your task consists of several steps, as in the preceding example, is also beneficial because whether or not you specify your own ROLLBACK statements, SQL Server will automatically roll back your transactions when a severe error occurs, such as a break in communication across the network, a database or client system crash, or a deadlock. (Deadlocks are covered in the section "Blocking and Deadlocks" later in this chapter.) The T-SQL statement used to start a transaction is BEGIN TRANSACTION. You specify the end of a transaction by using either COMMIT TRANSACTION or ROLLBACK TRANSACTION. You can optionally specify a name for a transaction in the BEGIN TRANSACTION statement, and you can then refer to the transaction by name in the COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. The syntax for these three statements is shown here:
 BEGIN TRAN[SACTION] [tran_name | @tran_name_variable ]   COMMIT [TRAN[SACTION] [tran_name | @tran_name_variable ]] ROLLBACK [TRAN[SACTION] [tran_name | @tran_name_variable | savepoint_name | @savepoint_name_variable ]] 

Committing Transactions

As mentioned, a committed transaction is one in which all modifications performed by the transaction are made a permanent part of the database. Before a transaction can commit, a record of its modifications and a commit record are written to the database transaction log. Thus, modifications that are a permanent part of the database can be in one of two locations: either the modifications are actually written to disk and thus are literally in the database, or they are in the data cache, and the transaction log can thus roll forward this transaction in the case of a failure so the transaction will not be lost.

All resources used by a transaction, such as locks, are released when the transaction commits. A transaction will commit successfully if each of its statements is successful. Here is a small explicit transaction, named update_state, that updates the state column value to XX in the publishers table for all publishers that have NULL in that column:

USE pubs  GO BEGIN TRAN update_state UPDATE publishers SET state = 'XX' WHERE state IS NULL COMMIT TRAN update_state GO

If you run this transaction, you should see two rows affected. To return the table to its original state (as if a rollback had occurred instead of the commit), run the following transaction:

USE pubs  GO BEGIN TRAN undo_update_state UPDATE publishers SET state = NULL WHERE state = 'XX' COMMIT TRAN undo_update_state GO

Again, you should see two rows affected. The transaction names update_state and undo_update_state used with COMMIT TRAN are ignored by SQL Server—transaction names serve simply as an aid to the programmer for identifying which transaction is being committed. SQL Server automatically commits the latest uncommitted transaction that started before the commit, regardless of whether a transaction name is specified.

Creating Nested Transactions

SQL Server allows nested transactions, or transactions within a transaction. With nested transactions, you should explicitly commit each inner transaction so SQL Server knows that an inner transaction has finished and will be able to release resources used by that transaction once the outer transaction commits. If resources are locked, other users will not be able to access them. Although you must include a COMMIT statement for each transaction, SQL Server will not actually commit the inner transactions until the outermost transaction has committed successfully; at the same time, SQL Server releases all resources used by the inner and outer transactions. If the outermost transaction fails to commit, none of the inner transactions will commit, and the outer transaction and all inner transactions will be rolled back. If the outer transaction commits, all inner transactions will commit. In other words, SQL Server basically ignores any COMMIT statements within inner nested transactions, in the sense that the inner transactions do not commit, and instead waits for the final commit or rollback of the outer transaction to determine the completion status of all inner transactions. (This is explained further in the Real World examples that follow.)

Also, in nested transactions, if a ROLLBACK statement is executed within the outer transaction or any of the inner transactions, all transactions are rolled back. It is not valid to include an inner transaction name with a ROLLBACK statement—if you do, SQL Server will return an error. Include the name of the outermost transaction, no name at all, or a savepoint name. (Savepoints are explained in the "Savepoints" section later in this chapter.)

REAL WORLD Using a Nested Transaction
Let's look at an example of a nested transaction that includes a stored procedure. The stored procedure contains an explicit transaction and is called from within another explicit transaction. Therefore, the transaction in the stored procedure becomes an inner nested transaction. The following code shows the statements used to create the stored procedure and the transaction that calls the stored procedure. (For simplicity, this example uses a PRINT statement rather than the real data modification statements.)
USE MyDB  GO CREATE PROCEDURE Place_Order --Creates the stored procedure AS BEGIN TRAN place_order_tran PRINT 'SQL Statements that perform order tasks go here' COMMIT TRAN place_order_tran GO BEGIN TRAN Order_tran --Begins the outer transaction PRINT 'Place an order' EXEC Place_Order --Calls the stored procedure, which --begins the inner transaction COMMIT TRAN Order_tran --Commits the inner and outer --transactions GO 
After you execute this code, you will see both PRINT statements as output. The place_order_tran transaction must have a COMMIT statement within the stored procedure to mark the end of that transaction, but it will not actually commit until the Order_tran transaction commits. Whether place_order_tran will be committed or rolled back depends entirely on whether Order_tran commits.

Although SQL Server does not actually commit inner transactions upon encountering their COMMIT statements, it does update the @@TRANCOUNT system variable for each COMMIT statement encountered. This variable keeps track of the number of active transactions per user connection. When no active transactions are present, @@TRANCOUNT is 0. As each transaction begins (using BEGIN TRAN), @@TRANCOUNT is increased by 1. As each transaction is committed, @@TRANCOUNT is decreased by 1. When @@TRANCOUNT reaches 0, the outermost transaction commits. If a ROLLBACK statement is executed within the outer transaction or any of the inner transactions, @@TRANCOUNT is set to 0. Remember, you should commit each inner transaction so that @@TRANCOUNT can be decremented properly. You can test the value of @@TRANCOUNT to determine whether any active transactions are present. To see the value of @@TRANCOUNT, use the statement SELECT @@TRANCOUNT.

REAL WORLD Using @@TRANCOUNT
Let's look at an example of how SQL Server uses the @@TRANCOUNT variable. Assume that you have a nested transaction that consists of two transactions, one inner transaction and an outer transaction, as in the preceding example. After both transactions have begun but before either has been committed, the value of @@TRANCOUNT is 2. The outer transaction will not be able to commit because @@TRANCOUNT has a nonzero value. When the inner transaction COMMIT statement is encountered, SQL Server decrements @@TRANCOUNT to 1. When SQL Server encounters the COMMIT statement for the outer transaction, @@TRANCOUNT is decremented to 0, and the outer and inners transactions will actually commit.
The following code builds on our previous example but includes retrievals of @@TRANCOUNT values:
USE MyDB  GO DROP PROCEDURE Place_Order GO CREATE PROCEDURE Place_Order --Creates the stored procedure. AS BEGIN TRAN place_order_tran --TRANCOUNT is incremented. PRINT 'SQL Statements that perform order tasks go here' SELECT @@TRANCOUNT as TRANCOUNT_2 COMMIT TRAN place_order_tran --TRANCOUNT is decremented. GO SELECT @@TRANCOUNT as TRANCOUNT_initial BEGIN TRAN Order_tran --TRANCOUNT is incremented. PRINT 'Place an order' SELECT @@TRANCOUNT as TRANCOUNT_1 EXEC Place_Order --Calls the stored procedure, --which begins the inner transaction. SELECT @@TRANCOUNT as TRANCOUNT_3 COMMIT TRAN Order_tran --TRANCOUNT is decremented. SELECT @@TRANCOUNT as TRANCOUNT_4 GO 
If you run these statements, you will see a series of @@TRANCOUNT values displayed in this order: 0, 1, 2, 1, 0.

NOTE
For explicit transactions that use BEGIN TRAN, you must commit each transaction explicitly. When you use nested transactions, SQL Server will not be able to commit the outermost or innermost transactions until all the inner transactions have been explicitly committed with a COMMIT statement.

Implicit Mode

In implicit mode, a transaction automatically begins whenever certain T-SQL statements are used and will continue until explicitly ended with a COMMIT or ROLLBACK statement. If an ending statement is not specified, the transaction will be rolled back when the user disconnects. The following T-SQL statements will begin a new transaction in implicit mode:

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

When one of these statements is used to begin an implicit transaction, the transaction will continue until it is explicitly ended, even if another of these statements is executed within the transaction. After the transaction has been explicitly committed or rolled back, the next time one of these statements is used, a new transaction is started. This process continues until implicit mode is turned off.

To set the implicit transaction mode to ON, you can use the following T-SQL command:

SET IMPLICIT_TRANSACTIONS {ON | OFF}

ON activates the mode, and OFF deactivates it. When implicit mode is deactivated, autocommit mode is used.

Implicit transactions are useful when you are running scripts that perform data modifications that need to be protected within a transaction. You can turn on implicit mode at the beginning of the script, perform the necessary modifications, and then turn off the mode at the end. To avoid concurrency problems, disable implicit mode after making data modifications and before browsing through data. If the next statement after a commit is a SELECT statement, it will start a new transaction in implicit mode, and the resources will not be released until that transaction is committed.