vendredi 28 juin 2019

MSSQL How to always rollback the entire transaction

I have a somewhat unsual need for my transaction handling.

I am working on a mutation test framework for MSSQL, for this I need to run my tests inside a transaction, so that the database always is in the state it started when the tests finish.

However I have the problem that users can code inside the test procedures and may call rollback transaction that may or may not be inside a (nested) transaction (savepoint).

high level it looks like this

start transaction
initialize test
run test with user code
   may or may not contain:
      - start tran
      - start tran savename
      - commit tran
      - commit tran savename
      - rollback tran
      - rollback tran savename
output testresults
rollback transaction

Is there a way to make sure I can at last always roll back to the intial state? I have to take in account that users can call stored procedures/triggers that maybe nested and can all contain transaction statements. With all my solutions the moment a user uses rollback tran in their test code they escape the transaction and not everything will be cleaned

What I want is that if a user calls rollback only their part of the transaction is rolled back and my transaction that I start before the initialization of the test is still intact. Maybe I am overthinking this a lot? Other solutions to this problem are welcome as well. Feel free to ask for clarifications.

Aucun commentaire:

Enregistrer un commentaire