SQLServerCentral Article

Explicit Transactions

,

Frequently there are questions relating to transactions posted on various forums and although the questions show a basic misunderstanding of this aspect of SQL Server, sometimes the answers show some misunderstanding also.

I initially started an article about nested transactions, because that is one of the areas that appear to cause the most confusion (and I wanted to make sure I understood it too). However, in looking into this I have also investigated other areas related to explicit transactions where I believe some simple examples could help to clarify.

This article will look at the various properties and usage of explicit transactions, as well as some behaviours that are frequently misunderstood.

What is a transaction?

Simply put, a transaction is a single unit of work. It is a modification, or a series of modifications, to data that will complete or fail as one unit. If any part of the transaction fails then all the changes must be all rolled back, reversing all modifications for which that the transaction was responsible.

What is an explicit transaction?

An explicit transaction is a transaction that is started with a BEGIN TRANSACTION statement and ends with either a COMMIT or ROLLBACK statement.

Note: Until this article covers ‘transaction names’ either ROLLBACK, ROLLBACK TRAN, ROLLBACK TRANSACTION or ROLLBACK WORK can be used – they are interchangeable. Similarly COMMIT, COMMIT TRAN, COMMIT TRANSACTION and COMMIT WORK are interchangeable.

The behaviour of explicit transactions can be demonstrated with a very simple table. Run the code in Listing 1 to set up the test.

Listing 1: Create the test database, table and data.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = 'Test_Table_1')
DROP TABLE Test_Table_1;
CREATE TABLE [dbo].[Test_Table_1](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] int NULL
) ON [PRIMARY]
GO
CREATE PROC RestoreTestTable
AS
BEGIN
UPDATE dbo.Test_Table_1
SET TestValue = 0;
END
GO
INSERT dbo.Test_Table_1 (TestValue)
VALUES(0);
GO
EXECUTE dbo.RestoreTestTable;
GO

Basic BEGIN/COMMIT transaction

In its most basic format the following code shows the only row in the test table being updated within an explicit transaction. SELECT statements show the value of the ‘TestValue’ column at various stages.

Listing 2: Basic BEGIN/COMMIT transaction.

USE TestDB;
GO
BEGIN TRANSACTION
               SELECT TestValue AS [Before Update]
               FROM dbo.Test_Table_1;
  UPDATE dbo.Test_Table_1
  SET TestValue = TestValue + 1;
               SELECT TestValue AS [After Update]
               FROM dbo.Test_Table_1;
  COMMIT TRANSACTION
               SELECT TestValue AS [After Commit]
               FROM dbo.Test_Table_1;

The result of this is as expected – 1 was added to ‘TestValue’ and stored in the table. The progress of 'TestValue' within this transaction is shown in the screen-shot below.

Listing 3: Basic BEGIN/ROLLBACK transaction.

  USE TestDB;
  ---Reset test table
  EXECUTE dbo.RestoreTestTable;
  GO
  BEGIN TRANSACTION
          SELECT TestValue AS [Before Update]
          FROM dbo.Test_Table_1;
  UPDATE dbo.Test_Table_1
  SET TestValue = TestValue + 1;
           SELECT TestValue AS [After Update]
           FROM dbo.Test_Table_1;
  ROLLBACK TRANSACTION
           SELECT TestValue AS [After Rollback]
           FROM dbo.Test_Table_1;

This time the ROLLBACK has rolled back the data manipulation that took place after the BEGIN TRANSACTION, as shown in the following screen-shot.

IDENTITY columns

When a ROLLBACK executes, although the data updates are rolled back, any IDENTITY columns do not have the values reverted: the next available IDENTITY value is still based upon the last value used during that transaction.

Listing 4: IDENTITY column.

USE TestDB;
GO
SELECT IDENT_CURRENT('Test_Table_1'); 
BEGIN TRANSACTION 
INSERT INTO dbo.Test_Table_1 ( TestValue ) VALUES(100); 
INSERT INTO dbo.Test_Table_1 ( TestValue ) VALUES(200); 
INSERT INTO dbo.Test_Table_1 ( TestValue ) VALUES(300); 
SELECT * FROM dbo.Test_Table_1; 
SELECT IDENT_CURRENT('Test_Table_1'); 
ROLLBACK TRAN
SELECT * FROM dbo.Test_Table_1; 
SELECT IDENT_CURRENT('Test_Table_1');

The IDENTITY column was incremented from 1 to 4 in the Listing 4 example. After the ROLLBACK was issued the next available IDENTITY value was not rolled back, even though the data changes have been. This is one reason that gaps can be seen in the sequence of an identity column within a table,

Table Variables

As with the IDENTITY column, table variables are not reverted by a ROLLBACK.

Listing 5: Table variable.

  
USE TestDB;
GO
DECLARE @TestTable TABLE ( TestValue INT ); 
INSERT @TestTable ( TestValue ) VALUES( 0 ); 
                  SELECT TestValue AS 'Before BEGIN TRAN' FROM @TestTable; 
BEGIN TRANSACTION 
UPDATE @TestTable SET TestValue = 10; 
                  SELECT TestValue AS 'After BEGIN TRAN' FROM @TestTable;
ROLLBACK TRAN 
                  SELECT TestValue AS 'After ROLLBACK' FROM @TestTable;

As shown in the screen-shot above, any values assigned to a table variable during a transaction are not rolled back. This may cause issues if you're expecting a rollback to have undone all of the actions and values between the BEGIN and ROLLBACK command - your table variables will still have the results of any action taken upon them wthin the transaction.

@@TRANCOUNT

@@TRANCOUNT is a system function that effectively keeps track of the number of open transactions – the number of BEGIN TRANSACTION statements that have not had a corresponding COMMIT TRANSACTION or ROLLBACK statement executed. It is increased by 1 for every BEGIN TRANSACTION statement and reduced by 1 for every COMMIT TRANSACTION. When @@TRANCOUNT is 0 there are no outstanding transactions in the active session. Any other value indicates that are outstanding COMMIT TRANSACTION commands and the data from the first BEGIN TRANSACTION has not been committed.

ROLLBACK TRANSACTION sets @@TRANCOUNT to 0, regardless of how many BEGIN TRANSACTION or COMMIT TRANSACTION statements have been issued. ROLLBACK TRANSACTION is final. The exception is when using savepoints, which is covered later in this article.

The following listing is a variation of Listing 2, but with the addition of having the value of @@TRANCOUNT shown.

Listing 6: Basic BEGIN/COMMIT transaction with @@TRANCOUNT.

  USE TestDB;
  GO
  ---Reset test table
  EXECUTE dbo.RestoreTestTable;
  GO
  SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRANSACTION;
                   SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  COMMIT TRANSACTION;
                   SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue After Commit] FROM dbo.Test_Table_1;

At the end of this transaction the value of @@TRANCOUNT is 0, so there are no outstanding transactions and the action has completed.

Stored Procedures and @@TRANCOUNT

When a stored procedure has transactions within it, the net effect on @@TRANCOUNT within the calling process must be zero, once the stored procedure has completed. If the stored procedure has an imbalance between the BEGIN TRAN and COMMIT TRAN then an error will be raised when control returns back from the stored procedure.

Listing 7: Stored Procedure call within transactions – unmatched transaction

  USE TestDB;
  GO
  CREATE PROCEDURE AddToTestValue
  AS
  BEGIN TRANSACTION
  UPDATE dbo.Test_Table_1
  SET TestValue = 1000;
  --COMMIT TRAN --@@TRANCOUNT will be different when this proc completes
  GO
         SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRANSACTION;
         SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  EXEC dbo.AddToTestValue;
  SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After Commit] FROM dbo.Test_Table_1;

Msg 266, Level 16, State 2, Procedure AddToTestValue, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

A ROLLBACK issued within the stored procedure will cause a rollback to the start of the process that called the stored procedure. This will also cause an error to be raised.

Listing 8: Stored Procedure call within transactions – rollback in proc

  USE TestDB;
  GO
  ALTER PROCEDURE AddToTestValue
  AS
  BEGIN TRANSACTION
  UPDATE dbo.Test_Table_1 
  SET TestValue = 1000; 
  ROLLBACK TRAN --ROLLBACK will go to the start of the calling process. 
  GO 
       SELECT @@TRANCOUNT AS [First trancount]; 
  BEGIN TRANSACTION; 
       SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1; 
  UPDATE [dbo].[Test_Table_1] 
  SET TestValue = TestValue + 1; 
  EXEC dbo.AddToTestValue; 
       SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1; 
COMMIT TRANSACTION; 
       SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After Commit] FROM dbo.Test_Table_1;

Msg 266, Level 16, State 2, Procedure AddToTestValue, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 0.

Nested transactions – the myth that persists

In the previous example (Listing 6) the end result of @@TRANCOUNT would have been 0 if either COMMIT or ROLLBACK had been issued, as there was only one BEGIN TRANSACTION.

With so-called nested transactions there can be several BEGIN TRAN and associated COMMIT TRAN within each other:

BEGIN TRANSACTION

<sql code>

BEGIN TRANSACTION

<sql code>

COMMIT TRANSACTION

<sql code>

COMMIT TRANSACTION

There is a popular belief that a structure like this will allow different blocks of code to be committed or rolled back at different times. This is incorrect – what actually happens is that a transaction is started with the first BEGIN TRANSACTION and any ROLLBACK issues will go back to that point, regardless of how many other BEGIN TRANSACTION statements there are. When the COMMIT command is issued it is only when the last matching COMMIT is issued that all of the actions between the first BEGIN TRAN and the last COMMIT are actually committed.

Where @@TRANCOUNT can be more useful is showing what happens where transactions are nested within other transactions.

Listing 9: Nested transactions with matching COMMIT

  USE TestDB;
  GO
  SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRANSACTION;
        SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION;
        SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  COMMIT TRANSACTION;
       SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After First Commit] FROM dbo.Test_Table_1;
  COMMIT TRANSACTION;
       SELECT @@TRANCOUNT AS [Fifth trancount], TestValue AS [TestValue After Second Commit] FROM dbo.Test_Table_1;

After the first COMMIT TRANSACTION the value of @@TRANCOUNT is still greater than 0, so the work has not been committed. Any locks on the data are retained and the entire operation can be rolled back until the second COMMIT TRANSACTION has executed and @@TRANCOUNT is 0.

Listing 10: Nested transactions with ROLLBACK

  USE TestDB;
  GO
  ---Reset test table
  EXECUTE dbo.RestoreTestTable;
          SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRANSACTION;
          SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION;
         SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After First Commit] FROM dbo.Test_Table_1;
  ROLLBACK TRANSACTION;
  SELECT @@TRANCOUNT AS [Fifth trancount], TestValue AS [TestValue After Rollback] FROM dbo.Test_Table_1;

In the Listing 10 example the value of ‘TestValue’ is reverted back to the initial value of the first BEGIN TRANSACTION, even though there was a COMMIT TRANSACTION beforehand.

COMMIT TRANSACTION reduces the value of @@TRANCOUNT by 1. Unless @@TRANCOUNT reaches 0 the transaction is not committed, so a ROLLBACK can still roll the change back to the initial BEGIN TRANSACTION.

Once a ROLLBACK has been issued the @@TRANCOUNT is set to 0. Any subsequent COMMIT or ROLLBACK commands will raise an error.

Listing 11: Nested transactions with ROLLBACK before COMMIT

  USE TestDB;
  GO
  ---Reset test table
  EXECUTE dbo.RestoreTestTable;
  GO
  SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRANSACTION;
         SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION;
         SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  ROLLBACK TRANSACTION;
         SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After Rollback] FROM dbo.Test_Table_1;
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [Fifth trancount], TestValue AS [TestValue After Commit] FROM dbo.Test_Table_1;

In this example the ROLLBACK was issued before the COMMIT. Therefore the entire transaction (back to the first BEGIN TRANSACTION) has been rolled back and the subsequent COMMIT raised an error, because @@TRANCOUNT was already 0.

Msg 3902, Level 16, State 1, Line 22

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

As long as @@TRANCOUNT is not 0 a ROLLBACK will roll the changes back to the state as of the first BEGIN TRANSACTION and set @@TRANCOUNT to 0. Any COMMIT commands issued do not commit the changes until the COMMIT that sets @@TRANCOUNT to 0.

Transaction Names

When using TRAN or TRANSACTION (but not WORK) it is possible to assign a name to the transaction. This is mainly for readability as it does nothing to change the logic. The name can be in excess of 32 characters, but only the first 32 characters are actually used.

When BEGIN TRANSACTION has a name associated with it, it is still only possible to execute a ROLLBACK for the first transaction. Specifying the name used in any nested BEGIN TRANSACTION will produce an error.

COMMIT ignores the name completely – it is only for readability. You can specify a name that was not used for a BEGIN TRANSACTION and the COMMIT will still execute without error.

Listing 12: ROLLBACK to first named BEGIN

  USE TestDB;
  GO
  SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRAN fred;
  SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION ginger;
  SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  ROLLBACK TRANSACTION fred;
  SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After Rollback] FROM dbo.Test_Table_1;

The code in listing 12 completes, with a rollback at the end.

Listing 13: ROLLBACK to second named BEGIN

  USE TestDB;
  GO
        SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRAN fred;
        SELECT @@TRANCOUNT AS [Second trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION ginger;
        SELECT @@TRANCOUNT AS [Third trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  ROLLBACK TRANSACTION ginger;
        SELECT @@TRANCOUNT AS [Fourth trancount], TestValue AS [TestValue After Rollback] FROM dbo.Test_Table_1;

Msg 6401, Level 16, State 1, Line 18

Cannot roll back ginger. No transaction or savepoint of that name was found.

Listing 13 fails, because the ROLLBACK did not specify the name of the first BEGIN TRANSACTION.

Listing 14: COMMIT with unknown name.

  USE TestDB;
  GO
  SELECT @@TRANCOUNT AS [First trancount];
  BEGIN TRAN fred;
        SELECT @@TRANCOUNT AS [Second trancount];
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION ginger;
        SELECT @@TRANCOUNT AS [Third trancount];
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  COMMIT TRANSACTION smith;
         SELECT @@TRANCOUNT AS [Fourth trancount];
  COMMIT TRAN jones;
         SELECT @@TRANCOUNT AS [Fifth trancount];

As shown in the scree-shot above, even though the names referred to in the two COMMIT statements do not exist the commands completed successfully and @@TRANCOUNT has been incremented and decremented back to zero.

Savepoints

A savepoint allows the partial rollback with a transaction, by the use of SAVE TRANSACTION <name> and a later ROLLBACK using the same name.

In the following example the value of ‘TestValue’ will be updated from 0 to 1, then 3 and finally 7 provided there are no ROLLBACK commands issued. With the SAVE TRANSACTION and subsequent ROLLBACK to that savepoint there are still transactions to commit and the final value shows that ‘Thirdtran’ was rolled back in isolation.

Listing 15: Savepoint.

  USE TestDB;
  GO
  ---Reset test table
  EXECUTE dbo.RestoreTestTable;
  GO
  SELECT @@TRANCOUNT AS [Initial trancount];
  BEGIN TRANSACTION FirstTran
  SELECT @@TRANCOUNT AS [FirstTran trancount], TestValue AS [TestValue Before Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 1;
  BEGIN TRANSACTION SecondTran
  SELECT @@TRANCOUNT AS [SecondTran trancount], TestValue AS [TestValue First Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 2;
  SAVE TRANSACTION FirstSavePoint
  BEGIN TRANSACTION ThirdTran;
         SELECT @@TRANCOUNT AS [ThirdTran trancount], TestValue AS [TestValue Second Update] FROM dbo.Test_Table_1;
  UPDATE [dbo].[Test_Table_1]
  SET TestValue = TestValue + 4;
  ROLLBACK TRANSACTION FirstSavePoint
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [First Commit], TestValue AS [TestValue After First Commit] FROM dbo.Test_Table_1;
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [Second Commit], TestValue AS [TestValue After Second Commit] FROM dbo.Test_Table_1;
  COMMIT TRANSACTION;
         SELECT @@TRANCOUNT AS [Third Commit], TestValue AS [TestValue After Third Commit] FROM dbo.Test_Table_1;

You cannot COMMIT to a savepoint – COMMIT ignores any names assigned to it (see Transaction Names).

WITH MARK

It is possible to restore from a log file to a particular point by recording the transaction mark, which can be used instead of a date and time. In the SSMS restore log wizard the available marks are listed for that log file and it is also possible to specify them when scripting the restore of the log.

Listing 16: WITH MARK.

  USE TestDB;
  GO
  BEGIN TRANSACTION Fred WITH MARK 'TestMark';
  UPDATE dbo.Test_Table_1
  SET TestValue = TestValue + 1;
  COMMIT TRANSACTION

The following screen-shot shows that restore dialog has a radio button 'Marked transaction'. With this selected, any MARK transactions available for this restore are shown, with the name assigned to the MARK and any description also used.

Conclusion

Explicit transactions can cause some confusion and hopefully the examples above have helped to explain certain aspects of this.

Nested transactions are purely for readability – and it is debatable whether they actually improve readability or just add another layer of unwarranted complexity.

My thanks to Wayne Sheffield for reviewing this article. The suggestions and corrections improved this article considerably. Any mistakes of course, are mine entirely.

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.9 (59)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.9 (59)

You rated this post out of 5. Change rating

Related content

External Article

Return to Sender: Identity Values and Triggers

By quickly and easily obtaining the identity value, the OUTPUT clause of an INSERT statement can obtain the auto-generated identity value of a row, and so will allow the application to immediately reference the new row or add rows to another table that use the identity value as a foreign key reference.

2010-11-09

3,380 reads