Dealing with transactions in tSQLt

This is the last article in a series about unit testing SQL Server with tSQLt:

  1. Introduction
  2. Anatomy of a tSQLt test
  3. Practical considerations
  4. Dealing with transactions

The problem

In my experience, most code is fine to be tested with tSQLt.  However, code that involves transactions will get tangled up in the transaction that tSQLt uses to contain the changes for each test.

SQL Server doesn’t allow proper nested transactions – you can nest transactions but only the outer-most begin transaction, commit or rollback actually does anything important.  Anything else just changes the value a counter of how many nested transactions are open.

If you take a stored procedure that does a commit or rollback and test it with tSQLt, you will probably get an unhelpful error message about being unable to commit the transaction.

Solution – committing

If you have have a stored procedure that commits a transaction, and you can alter its source code, then a solution I have found is to change it to commit only when it’s not under test.

So you change the code from this

-- code inside the transaction


to this

    SET @useLocalTransaction=1

-- code inside the transaction
IF @useLocalTransaction=1

If the code is under test by tSQLt, there will already be a transaction open before execution goes into the stored procedure.  Therefore @@TRANCOUNT will be 1 at the top of the code above, so under test the BEGIN TRANSACTION is skipped.

If the code is in production (assuming that there won’t normally be a transaction open due to other production code), then @@TRANCOUNT will be 0.  Therefore we start a transaction and remember the fact that we started the transaction (rather than some other code, e.g. tSQLt starting it).  After the main code has finished, we use the fact that we started the transaction to commit it.

Solution – rolling back

Sebastian Meine has written an article with lots of detail, which I recommend you read.  The TL;DR version is that you use savepoints to emulate partial rollbacks.  So instead of the stored procedure under test doing this

  1. Database is in state A
  2. Begin a transaction
  3. Make changes, so database is in state B
  4. Roll the transaction back, throwing away state B and restoring whatever was around before the transaction (state A)

it does this

  1. Database is in state A
  2. Create a savepoint (which takes a snapshot of A)
  3. Make changes, so database is in state B
  4. Use the savepoint to put the database back to how it was when the savepoint was taken (state A)

There are limitations to this approach, which the article goes into.




How to rollback in procedures

3 thoughts on “Dealing with transactions in tSQLt

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s