Transactions and Stored Procedures

Because SQL code in stored procedures runs locally on the server, it is recommended that transactions be coded in stored procedures to speed transaction processing. The less network traffic going on within your transactions, the faster they can finish.

Another advantage of using stored procedures for transactions is that it helps avoid the occurrence of partial transactions ”that is, transactions that are started but not fully committed. It also avoids the possibility of user interaction within a transaction. The stored procedure keeps the transaction processing completely contained because it starts the transaction, carries out the data modifications, completes the transaction, and returns the status or data to the client.

Stored procedures also provide the additional benefit that if you need to fix, fine tune, or expand the duties of the transaction, you can do it all at one time in one central location. Your applications can share the same stored procedure, providing consistency for the "logical unit of work" across your applications.

Although stored procedures provide a useful solution to managing your transactions, you do need to know how transactions work within stored procedures and code for them appropriately. Consider what happens when one stored procedure calls another, and they both do their own transaction management. Obviously, they now need to work in concert with each other. If the called stored procedure has to roll back its work, how can it do this correctly without causing data-integrity problems?

The issues you need to deal with go back to the earlier topics of transaction nesting and transaction flow versus program flow. Unlike a rollback in a transaction (see the next section), a rollback in a stored procedure does not abort the rest of the batch or the calling procedure.

For each BEGIN TRAN encountered in a nested procedure, the transaction nesting level is incremented by 1. For each COMMIT encountered, the transaction nesting level is decremented by 1. However, if a rollback other than to a named savepoint occurs in a nested procedure, it rolls back all statements to the outermost BEGIN TRAN , including any work performed inside the nested stored procedures that has not been fully committed. It continues processing the remaining commands in the current procedure as well as the calling procedure(s).

To explore the issues involved, you will use the following stored procedure. The procedure takes a single integer argument, which it then attempts to insert into a table ( testable ). All data entry attempts ”whether successful or not ”are logged to a second table ( auditlog ). The code for the stored procedure is as follows :

 CREATE PROCEDURE trantest @arg INT  AS BEGIN TRAN    IF EXISTS(SELECT * FROM testable WHERE col1 = @arg)    BEGIN       RAISERROR ('Value %d already exists!', 16, -1, @arg)       ROLLBACK TRANSACTION    END    ELSE    BEGIN       INSERT INTO testable (col1) VALUES (@arg)       COMMIT TRAN    END INSERT INTO auditlog (who, valuentered) VALUES (USER_NAME(), @arg) return 

Now explore what happens if you call this stored procedure in the following way and check the values of the two tables:

 EXEC trantest 1  EXEC trantest 2 SELECT * FROM testable SELECT valuentered FROM auditlog go 

The execution of this code gives the following results:

 col1  ----------- 1 2 valuentered ----------- 1 2 

These would be the results you would expect because no errors would occur and nothing would be rolled back. Now if you were to run the same code a second time, Testable would still only have two rows because the trigger would roll back the attempted insert of the duplicate rows. However, because the procedure and batch are not aborted, the code would continue processing and the rows would still be added to the auditlog table. The result would be as follows:

 Server: Msg 50000, Level 16, State 1, Procedure trantest, Line 6  Value 1 already exists! Server: Msg 50000, Level 16, State 1, Procedure trantest, Line 6 Value 2 already exists! col1 ----------- 1 2 valuentered ----------- 1 2 1 2 

Now explore what happens when you execute the stored procedure from within a transaction:

 BEGIN TRAN  EXEC trantest 3 EXEC trantest 1 EXEC trantest 4 COMMIT TRAN SELECT * FROM testable SELECT valuentered FROM auditlog go 

The execution of this code gives the following results:

 Server: Msg 266, Level 16, State 2, Procedure trantest, Line 16  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION  statement is missing. Previous count = 1, current count = 0. Server: Msg 50000, Level 16, State 1, Procedure trantest, Line 6 Value 1 already exists! Server: Msg 3902, Level 16, State 1, Line 5 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. col1 -----------           1           2           4 valuentered -----------           1           2           1           2           1           4 

A number of problems are occurring now. For starters, you get back a message telling you that the transaction nesting level was messed up. More seriously, the results show that the value 4 made it into the table anyway, and that the audit table picked up the inserts of 1 and the 4 but lost the fact that you tried to insert a value of 3 . What happened ?

Take this one step at a time. First, you start the transaction and insert into trantest with the value of 3 . The stored procedure starts its own transaction, adds the value to testable , commits that, and then adds a row to the auditlog . Next, you execute the procedure with the value of 1 . This value already exists within the table, so the procedure raises an error and rolls back the transaction. Remember that a ROLLBACK undoes work to the outermost BEGIN TRAN ”that means the start of this batch. This rolls back everything, including the insert of 3 into trantest and auditlog . The auditlog entry for the value of 1 is inserted and not rolled back because it occurred after the transaction was rolled back and is a standalone, auto-committed statement now.

You then receive the error regarding the change in the transaction nesting level because a transaction should leave the state of a governing transaction in the same way it was entered; it should make no net change to the transaction nesting level. In other words, the value of @@trancount should be the same when the procedure exits as when it was entered. If it is not, the transaction control statements are not properly balanced.

Also, because the batch is not aborted, the value of 4 is inserted into trantest , an operation that completes successfully and is auto-committed. Finally, when you try to commit the transaction, you receive the last error regarding a mismatch between BEGIN TRAN and COMMIT TRAN because no transaction is currently in operation.

The solution to this problem is to write your stored procedures so that transaction nesting doesn't occur and that a stored procedure only rolls back its own work. When a rollback occurs, it should return an error status so that the calling batch or procedure is aware of the error condition and can choose to continue or abort the work at that level. You can manage this by checking the current value of @@trancount and determining what needs to be done. If a transaction is already active, the stored procedure should not issue a BEGIN TRAN and nest the transaction, but rather set a savepoint. This allows the procedure to perform a partial rollback of its work. If no transaction is active, then the procedure can safely begin a new transaction. The following SQL code fragment is an example of using this approach:

 DECLARE @trancount INT  /* Capture the value of the transaction nesting level at the start */ SELECT @trancount = @@trancount IF (@trancount = 0)  -- no transaction is current active, start on    BEGIN TRAN mytran ELSE                 -- a transaction is active, set a savepoint only    SAVE TRAN mytran . . /* This is how to trap an error. Roll back either to your    own BEGIN TRAN or roll back to the savepoint. Return an    error code to the caller to indicate an internal failure.    How the caller handles the transaction is up to the caller.*/ IF (@@error <> 0) BEGIN    ROLLBACK TRAN mytran    RETURN 1969 END . . /* Once you reach the end of the code, you need to pair the BEGIN TRAN,    if you issued it, with a COMMIT TRAN. If you executed the SAVE TRAN    instead, you have nothing else to do...end of game! */ IF (@trancount = 0)   COMMIT TRAN RETURN 0 

If these concepts are applied to all stored procedures that need to incorporate transaction processing as well as the code that calls the stored procedures, you should be able to avoid problems with transaction nesting and inconsistency in your transaction processing. Just be sure to check the return value of the stored procedure and determine whether the whole batch should be failed, or whether that one call is of little importance to the overall outcome and the transaction can continue.

For additional examples and discussion on coding guidelines for stored procedures in transactions, see Chapter 28, "Creating and Managing Stored Procedures in SQL Server."