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

BEGIN TRANSACTION
-- code inside the transaction

COMMIT TRANSACTION

to this

IF @@TRANCOUNT=0
BEGIN
    BEGIN TRANSACTION
    SET @useLocalTransaction=1
END

-- code inside the transaction
IF @useLocalTransaction=1
   COMMIT TRANSACTION

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