I decided to write this article when I noticed how developers might be surprised seeing the way SQL Server handles nested transactions. People usually know what transactions are ... they are not reserved to the relational databases world but is it a well-known subject? I do not think so. This time I will cover nesting transactions in SQL Server - how they can be started and committed or rollback. Additionally, have you used the SAVE TRANSACTION command? If not, enjoy reading the article.

Do you prefer a video than reading? If so, scroll down and watch it.

The below examples are based on inserting data to a test table. Before each test, the table is cleared from data. All tests are performed on SQL Server 2014 but it is not different from the earlier versions.

Single transaction

I will start with a very basic example. Here is a script:

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction');
commit transaction;
select value from test;

It starts with inserting one row to the table - there is no explicit transaction. It means that if the insert succeeds, there is no way to roll it back. Then the second row is inserted but this one is encapsulated in a transaction. Finally, the transaction is committed and the content of the table is printed.

I guess the result is pretty obvious.

Both rows are there because nothing was rolled back. Two rows were inserted so the table contains two rows. No magic.

Rolling back a single transaction

The second example shows how the rollback operation works.

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction');
rollback transaction;
select value from test;

The script is very similar to the first one with a small exception - the transaction is not committed but rolled back. The result is presented below.

Only the first row exists in the table. The second one also was there but as the transaction was rolled back, it disappeared. The database engine had to revert all DML changes made inside the transaction.

Nested transactions

SQL Server has a useful feature related to transactions that makes this database engine special - nested transactions. It is not common in other popular relational database management systems (RDBMSes) so it is worth to take a closer look on what Microsoft implemented. The whole concept is based on an idea that a developer may want to not only wrap a set of SQL statements into a transaction, but also to create transactions inside other transactions. It has at least two advantages:

• If you use stored procedures, you would like some of them to use transactions. Usually, because you want to be easily rollback a part of the work. If nesting transactions would not be allowed, how would you make sure you never execute those stored procedures with transactions inside from other explicit transactions in your code? There is no easy way. Ooh, actualy, there is a way, a hard way - discipline and development rules - PL/SQL developers know what I am writing about.
• You may consciously want to have transactions on different levels - commit or rollback those inside and leave a decision about the most outer one for the end.

Nevertheless, some developers find nested transactions useful. Here is a simple script showing how one transaction can be nested inside another one.

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction, row 1');
      begin transaction;
            insert into test (value) values ('second level transaction');
      commit transaction;
      insert into test (value) values ('first transaction, row 2');
commit transaction;
select value from test;

Nothing special happened in this case because both transactions were committed. Eventually, the effect of all four INSERTs are visible in the table. In spite of it, let's focus on what the script actually does.

The first row is inserted without an explicit transaction. The first transaction (outer) is opened and the second row is inserted. The inner transaction encapsulates the INSERT of the third row. Then, the inner transaction is committed and the last row is created. Finally, the outer transaction is committed.

The result is exactly the same as if there were no transactions at all, just four INSERT statements. The tricky part starts when the rollback statement comes into play.

Rolling back the outer transaction

Nested transactions are pretty straight forward if they are all committed. It is a little less obvious when one of them is rolled back. Can you guess what is a result of rolling back the outer transaction? The below script shows exactly that.

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction, row 1');
      begin transaction;
            insert into test (value) values ('second level transaction');
      commit transaction;
      insert into test (value) values ('first transaction, row 2');
rollback transaction;
select value from test;

What really happened here is very simple - the outer transaction was rolled back so everything that was done inside of it, was reverted. The inner transaction was also a part of the outer transaction so the result of it vanished too. The only row after all INSERT statements that survived is the first one because it was the only one that was not a part of the rolled back transaction.

Rolling back the inner transaction

If you have already got bored, I hope I will get your attention back with the next example. Try to guess what will be a result of rolling back the inner transaction but committing the outer one:

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction, row 1');
      begin transaction;
            insert into test (value) values ('second level transaction');
      rollback transaction;
      insert into test (value) values ('first transaction, row 2');
commit transaction;
select value from test;

If you expect the following three rows:

• no transaction,
• first transaction, row 1,
• first transaction, row 2,

you are so wrong ... Honestly speaking that would make sense, right? The inner transaction was rolled back so the second level transaction row should disappear like it never existed the outer transaction was committed so all other three rows should be there.

Unfortunately, it does not work like that. It would be the most expected result, by me too, but Microsoft developers implemented it in a different way. Take a look at the result.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 3902, Level 16, State 1, Line 8
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

(2 row(s) affected)

What did happen to first transaction, row 1 and why did the error occur? The rule that explains this is simple - the rollback statement rolls back all open transactions in the current session. It does not matter if there is only one transaction or ten nested in the cascading way - one in the other. They are all rolled back. It also nicely explains the error message. The outer transaction could not be committed because it was already rolled back by the rollback statement.

How about the insert statement? It also can be explained. The no transaction row was inserted before any transaction was opened so it could not be rolled back. Then the outer transaction was opened, the second row was inserted (but not committed), the inner transaction was started, the third row was inserted ... and both open transactions (inner and outer) were rolled back. The last row was inserted after the rollback statement so it was outside of any open transaction and the change became permanent.

The whole process is presented on the below diagram.

Only the rows inserted on the blue level are permanent. Everything else was rolled back.

If you are disappointed by this behavior, welcome to the club.

Real nesting transactions with SAVE TRANSACTION

Does this rolling back the whole stack of transactions means that I cannot truly nest transactions and roll back only the part I want? Fortunately, no. There is a way - the SAVE TRANSACTION statement. It puts a tag on a particular point in a transaction so later I can roll back to that point. The above script modified to use SAVE TRANSACTION is presented below.

insert into test (value) values ('no transaction');
begin transaction;
      insert into test (value) values ('first transaction, row 1');
      save transaction inner1;
            insert into test (value) values ('second level transaction');
      rollback transaction inner1;
      insert into test (value) values ('first transaction, row 2');
commit transaction;
select value from test;

The SELECT statement returned three rows (1, 2, and 4) because the third one was rolled back. The below diagram helps to understand what happened.

It is important to notice that the SAVE TRANSACTION statement does not open a new transaction, it just puts a tag at a particular point. For the whole time there is only one or zero transactions open, never two. On the whole, this technique is not nesting transactions but it behaves like it. There can be multiple SAVE TRANSACTION statements in a script and you can programmatically choose to which point roll back in each case.

Conclusions

Even that SQL Server supports nesting transactions, it does not work great when one of them has to be rolled back. I am not satisfied with rolling back everything by a single ROLLBACK statement. It makes scripting difficult and does not allow me to take real advantage of nesting. Fortunately, that is a better alternative - the SAVE TRANSACTION statement. Even if it does not open a nested transaction, it does the job - result of a nested part of the code can be rolled back.

Do you prefer a video?

[spvideo]https://youtu.be/mtxO9861pLI[/spvideo]