This post is a continuation from part 4, but introduces a new complexity. Here is the explanation from a different website called SQLBalls, and the topic is SAVE TRANSACTION. Here the diagram he uses shows how you could have transactions already running when you get to the inner transaction. What happens if the inner transaction causes an error? If any one of these fail and you ROLLBACK, the all of the transactions will be rolled back.

Saved Transactions allow us to save our success as we go in Nested Transaction, so that a Rollback doesn’t wipe out all of our progress. So, SAVE TRANSACTION is saying that we should save the work we have done so far so that in case we later encounter a ROLLBACK, the rollback will not rollback what we have already saved with SAVE TRANSACTION.

Here is the code he used.

if exists(select name from sys.tables where name='myTable1')
begin
     drop table dbo.myTable1
end
go
create table dbo.myTable1(
          myid int identity(1,1)
          ,mychar char(50)
          ,mychar2 char(50)
          ,constraint pk_myid_mytable1 primary key clustered(myid)
          )

Below is the code from the website.

BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2)
          VALUES('some data 2', 'some more data 2')
          BEGIN TRANSACTION
              UPDATE dbo.myTable1
              set mychar='some new data 1'
              WHERE myid=1
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION  -- rolls back everything!

Here is the SQL output in the Results pane of SQL Server after running the above code, but don’t be fooled! Run the SELECT again and find that are no records in the table. Please see the screen shot from the website shown after the output below.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
myid        mychar                                             mychar2
----------- -------------------------------------------------- --------------------------------------------------
1           some new data 1                                    some more data 1                                  
2           some data 2                                        some more data 2                                  

(2 row(s) affected)

Oops! In the above code, at the end, we have a rollback transaction statement. This causes all of the transactions to be rolled back. Is this what we want? No. Now let’s save the transaction. Look at the code below.

Now let’s try the same code again, but now we’ll create a SAVE TRANSACTION and name is testSavePoint. Note the syntax change. We need to call this in the ROLLBACK otherwise the whole thing will ROLLBACK again. ACID properties are still in play, so we need a COMMIT AFTER the ROLLBACK.

Note that the writer of this code has two save transaction textSavePoint statements in the following code, for some unknown reason. So, I modified his code by creating two differently named save points: testSavePoint_1 and testSavePoint_2. Here we roll back to testSavePoint_2.

DELETE FROM dbo.Table1
GO
BEGIN TRANSACTION
     INSERT INTO dbo.myTable1(mychar, mychar2)
     VALUES('some data 1', 'some more data 1')
     SAVE TRANSACTION testSavePoint_1
     BEGIN TRANSACTION
          INSERT INTO dbo.myTable1(mychar, mychar2) VALUES('some data 2', 'some more data 2')
          save transaction testSavePoint_2
          BEGIN TRANSACTION
              UPDATE dbo.myTable1 set mychar='some new data 1' WHERE myid=3
              BEGIN TRANSACTION
                   SELECT * FROM dbo.myTable1
              COMMIT TRANSACTION
          COMMIT TRANSACTION
     COMMIT TRANSACTION
ROLLBACK TRANSACTION testSavePoint_2
COMMIT TRANSACTION

The transaction is rolled back, starting with the most recent statement which is SELECT (no changes made to the table), then it rolls back the UPDATE statement, but stops there because the next thing it encounters is save transaction testSavePoint. Only our Update Statement was “rolled back”. The output of a SELECT statement is shown below.

What would happen if we changed the rollback to a different save point? If we change the second-last line in the above code to ROLLBACK TRANSACTION testSavePoint_1, then we would only expect one record to be in the table after running the modified code above. Sure enough, this is the case. See below.