Transaction Rollbacks | Microsoft SQL Server 2000 Administrators Companion

3 4

A rollback can occur in one of two ways: as an automatic rollback by SQL Server or as a manually programmed rollback. In certain cases, SQL Server will do the rollback for you. But to have logical consistency in your programs, you must explicitly call the ROLLBACK statement when needed. Let's look at these two methods in more detail.

Automatic Rollbacks

As mentioned earlier in this chapter, if a transaction fails because of a severe error, such as a loss of network connection while the transaction is being run or a failure of the client application or computer, SQL Server will automatically roll back the transaction. A rollback reverses all modifications the transaction performed and frees up any resources the transaction used.

If a run-time statement causes an error, such as a constraint or rule violation, by default, SQL Server automatically rolls back only the particular statement in error. To change this behavior, you can use the SET XACT_ABORT statement. Setting XACT_ABORT to ON tells SQL Server to automatically roll back a transaction in the event of a run-time error. This technique is useful when, for instance, one statement in your transaction fails because it violates a foreign key constraint and—because that statement failed—you do not want any of the other statements to succeed. By default, XACT_ABORT is set to OFF.

SQL Server also uses automatic rollback during recovery of a server. For example, if you have a power loss while running transactions and the system is rebooted, when SQL Server is restarted, it will perform automatic recovery. Automatic recovery involves reading from the transaction log information to replay committed transactions that did not get written to disk and to roll back transactions that were in flight (not committed yet) at the time of the power loss.

Programmed Rollbacks

You can specify a point in a transaction at which a rollback will occur by using the ROLLBACK statement. The ROLLBACK statement terminates the transaction and reverses any changes that were made. If you cause a rollback in the middle of a transaction, the rest of the transaction will be ignored. If the transaction is an entire stored procedure, for example, and the ROLLBACK statement occurs within the stored procedure, the stored procedure is rolled back and processing resumes at the next statement in the batch after the stored procedure call.

If you want to roll back a transaction based on the number of rows that are returned by a SELECT statement, use the @@ROWCOUNT system variable. This variable contains the number of rows returned from a query or affected by an update or a delete. If the specific number of rows does not matter but you simply need to find out whether a row or rows exist for a specific condition, you can use the IF EXISTS statement with the SELECT statement. This statement will not return any rows of data but returns TRUE or FALSE. If the result is TRUE, the statement that follows will be executed; if it returns FALSE, the statement that follows will not be executed. The IF EXISTS statement can use an ELSE clause also.

Let's look at an example using the IF EXISTS…ELSE clause. The following transaction updates royalty amounts in the roysched table for two royalty rates (16 percent and 15 percent), but if either of the royalty rates to be updated does not exist, neither UPDATE command will be executed. The transaction uses the ROLLBACK statement to ensure this outcome.

BEGIN TRAN update_royalty --Begin the transaction.  USE pubs IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id = roysched.title_id AND roysched.royalty = 16) UPDATE roysched SET royalty = 17 WHERE royalty = 16 --13 rows exist. ELSE ROLLBACK TRAN update_royalty --ROLLBACK is not executed. IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id = roysched.title_id AND roysched.royalty = 15) --No rows exist. BEGIN UPDATE roysched SET royalty = 20 WHERE royalty = 15 COMMIT TRAN update_royalty END ELSE --ROLLBACK is executed. ROLLBACK TRAN update_royalty GO

In this transaction, the first IF EXISTS (SELECT…) statement finds some rows that exist, and therefore the first UPDATE command is executed (showing 13 rows affected). The second SELECT statement returns 0 rows, and therefore the second UPDATE command is not executed, but ROLLBACK TRAN update_royalty is executed. Because ROLLBACK reverses all modifications to the beginning of the transaction, the first update was rolled back. If you execute the first SELECT statement again, you will still see 13 rows with royalty set to 16, which was the original state of the database when we started this transaction. Again, the update to set royalty to 17 was reversed, or rolled back, because of the ROLLBACK statement.

NOTE
Some new keywords were used in this transaction: IF, ELSE, BEGIN, and END. These keywords will be described in detail in Chapter 20.

A transaction cannot be rolled back after it commits. (Remember, an inner transaction isn't really committed until the outer one commits.) For an explicit rollback of a single transaction to occur, ROLLBACK must be called before COMMIT. In the case of nested transactions, once the outermost transaction has committed (and therefore the inner transactions also commit), none of the transactions can be rolled back. As mentioned, you cannot roll back only the inner transactions; instead, the entire transaction (all inner transactions and the outer transaction) must be rolled back. Therefore, if you include a transaction name in the ROLLBACK statement, be sure to use the outermost transaction's name to avoid confusion and to avoid getting an error from SQL Server. There is a way to get around having to roll back an entire transaction, which allows you to keep some of the modifications: you can use savepoints.

Savepoints

You can avoid having to roll back an entire transaction by using a savepoint to roll back to a certain point in a transaction, rather than to the beginning of the transaction. All modifications up to the savepoint will remain in effect and will not be rolled back, but the statements that are executed after the savepoint (which you must specify in the transaction) and up to the ROLLBACK statement will be rolled back. The statements following the ROLLBACK statement will then continue to be executed. If you later roll back the transaction without specifying a savepoint, all modifications will be reversed to the beginning of the transaction as usual; the entire transaction will be rolled back. Note that when a transaction is being rolled back to a savepoint, SQL Server does not release locked resources. They will be released when the transaction commits or upon a full-transaction rollback.

To specify a savepoint in a transaction, use the following statement:

SAVE TRAN[SACTION]  {savepoint_name |  @savepoint_name_variable}

Position a savepoint in the transaction at the location you want to roll back to. To roll back to the savepoint, use ROLLBACK TRAN with the savepoint name, as shown here:

ROLLBACK TRAN savepoint_name

You can have more T-SQL statements after the ROLLBACK statement to continue with the transaction. Remember to include a COMMIT statement or another ROLLBACK statement after the first ROLLBACK statement in order for the entire transaction to be completed.

MORE INFO
For more information about savepoints and a good example of using them, look up "Save Transaction" in the Books Online index and select "Save Transaction (T-SQL)" in the Topics Found dialog box.