Transactions and Batches

There is no inherent transactional quality to batches. As you have seen already, unless you provide the syntax to define a single transaction made up of several statements, each individual statement in a batch is its own separate transaction, and each statement will be carried to completion or fail individually. The failure of a transaction within a batch does not cause the batch to stop processing.

In other words, transaction flow does not affect process flow. After a rollback tran statement, processing continues with the next statement in the batch or stored procedure. For this reason, you want to be sure to check for error conditions after each data modification within a transaction and exit the batch or stored procedure as appropriate.

Now go back to the banking example again and remove the RETURN statements:

 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     rollback tran update savings_account    set balance = balance + 00    where account_number = @savings_account if @@error != 0     rollback tran commit tran 

Assume that a check constraint on the account prevents the balance from being set to a value less than 0. If the checking account had less than $1,000 in it, the first update would fail, and you would catch the error condition and roll back the transaction. At this point, the transaction is no longer active, but the batch still contains additional statements to execute. Without a return after the rollback, SQL Server continues with the next statement in the batch, which is the update to the savings account. However, this will now execute as its own separate transaction and will AutoCommit if it completes successfully. This is not what you want to happen because now that second update is its own separate unit of work; you will have no way to roll it back.

The key concept to keep in mind here is that transaction flow does not affect program flow. In the event of an error within the transaction, you need to make sure you have the proper error checking and a means to exit the transaction in the event of an error. That will prevent the batch from continuing with any remaining modifications that were meant to be a part of the original transaction. As a general rule of thumb, a return statement should almost always follow rollbacks .

Although you can have multiple transactions within a batch, you can also have transactions that span multiple batches. For example, you could write an application that begins a transaction in one batch and then asks for user verification during a second batch. The SQL might look like this:

First Batch:

 begin transaction  insert publishers (pub_id, pub_name, city, state)     values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") if @@error = 0     print "publishers insert was successful. Please go on." else     print "publisher insert failed. Please roll back" 

Second Batch:

 update titles      set pub_id = "1111"     where pub_id = "1234" delete authors     where state = "CA" commit transaction 

Writing transactions that span multiple batches is almost always a bad idea. The locking and concurrency problems can become complicated, with awful performance implications. What if the application prompted for user input between batches and the user went out to lunch ? Locks would be held until the user got back and continued the transaction. In general, you want to enclose each transaction in a single batch, using conditional programming constructs to handle situations like the preceding example. Following is a better way to write that program:

 begin transaction  insert publishers (pub_id, pub_name, city, state)     values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") if @@error = 0 begin     print "publishers insert was successful. Continuing."     update titles         set pub_id = "1111"         where pub_id = "1234"     delete authors         where state = "CA"     commit transaction end else begin     print "publisher insert failed. rolling back transaction"     rollback transaction end 

The important point in this example is that the transaction now takes place within a single batch for better performance and consistency. As you will see in the next section, it is usually best to encode transactions in stored procedures for even better performance and to avoid the possibility of unfinished transactions.