Thought Bubble #3 – Nested transactions

nested transactions - rollback

Do you know how many rows will the following statement return, after the query from the thought bubble was executed?

SELECT * FROM [dbo].[Numbers]  ?

In this short article, we will concentrate on the effect a ROLLBACK statement has over the nested transactions in SQL Server.

Let’s say we have the “Numbers” table, with the following syntax:

On this empty table, we execute the queries from the thought bubble:

We have 2 transactions in our query: FirstTran and SecondTran.

The FirstTran inserts a value in our table and the, the second transaction inserts a different row. However, after the second insert, a new check is made: if the number of rows in the table is greater than 1, then the transaction is rolled back.

So what do you think:

  • do we have the initial row (100, 200) in the Numbers table?
  • do we have both rows: (100, 200), (200, 200) in the table?
  • or is the table empty?

The answer is, of course, that the table is empty, because a ROLLBACK statement automatically removes all the changes made by a transaction, even if between those changes there were other transactions, already committed.

The actual messages returned by SQL Server in this example are these:

You can read more about nested transactions in this article: Nested transactions

Thanks for reading and if you have questions or want to discuss in more detail, please add them in the section below.





Leave a Reply

Your email address will not be published. Required fields are marked *

3 + three =