SQL Server: nested transacties dmv workaround

Oa voor unit testen wilde ik nested transacties gebruiken bij MS Sql Server 2005. Tot mijn verbazing is dit standaard niet mogelijk via ADO! Het is wel mogelijk via SQL, maar dan nogal omslachtig… Uiteindelijk is het me gelukt…

Je kunt namelijk handmatig transacties starten en committen via SQL met “BEGIN TRANSACTION” en “COMMIT TRANSACTION” statements. Dit kan ook nested, waarbij je transacties een naam kunt geven, bijv: “BEGIN TRANSACTION level1″.

Echter, een rollback wordt ALTIJD op de buitenste transactie gedaan! Dus niet netjes nested zoals met een commit…
Na wat zoekwerk vond ik de oplossing: savepoints gebruiken. Na elke “BEGIN TRANSACTION” doe ik een “SAVE TRANSACTION ”. Deze savepoint kunt je weer ongedaan maken via “ROLLBACK TRANSACTION ”. Echter, de transactie bestaat nog steeds, dus na de rollback moet je nog een commit (!) doen op de transactie zelf. Verre van netjes, maar het werkt…

Bijvoorbeeld:
In het onderstaande voorbeeld worden 2 transacties gebruikt, waarbij de 2e en geneste transactie weer ongedaan gemaakt wordt. De 1e transactie wordt wel gecommit. Het resultaat is dat “something 1″ wel opgeslagen is, maar dat “something 2″ niet doorgevoerd is in de database.

BEGIN TRANSACTION level1
SAVE TRANSACTION save1

BEGIN TRANSACTION level2
SAVE TRANSACTION save2

ROLLBACK TRANSACTION save2
COMMIT TRANSACTION level2

COMMIT TRANSACTION level1