Design for implicit and explicit transactions
May include but is not limited to: nested transactions, savepoints, TRY/CATCH
A transaction is an atomic, consistent, isolated, durable operation – this is best known as the acronym ACID. There are two kinds of transactions: explicit – this is what you refer as a transaction. It starts with the BEGIN TRANSACTION clause, and ends with a COMMIT / ROLL BACK – what you prefer. There are also implicit transactions. These are enabled when you connect SQL Server with implicit transactions enabled. By this, some clauses automatically start transactions (CRUD operations, DDL statements, etc.) which you can commit or roll back immediately.
To enable implicit transactions, you call the SET IMPLICIT_TRANSCATIONS ON statement. Remember – a transaction either succeeds or fails, so this mode holds some surprises to you.
Nothing can hold you back from nesting transactions. There are some rules, although. The first rule is that the outmost transaction always wins. So if you commit every nested transaction, but roll back the outmost one, every inner transaction is rolled back. The reverse is true.
It’s quite obvious, but every COMMIT statements affect the last opened transaction. It’s not quite obvious, but you can call ROLL BACK only once, and it rolls back the entire set of transactions. You can examine you current transaction nesting level by examining the @@TRANCOUNT variable.
You can even use savepoints to save your progress in a given transaction. The syntax is SAVE TRANSACTION savepoint name, and you can roll back to the specified savepoint by using the ROLL BACK savepoint name syntax.
SQL Server has some quite nice error handling features in the shape of TRY / CATCH blocks. You can make good use of them in transactions, too. I wouldn’t waste time on writing examples, instead some behavior follows. If an error occurs inside a transaction, and the error is that sever, it might invalidate your transaction, making it uncommittable. An uncimmottable transaction can only make read operations or roll itself back (at the end of the batch, the database engine would roll it back anyway). To check if your transaction is in that bad shape, query XACT_STATE. If this function returns -1, then there’s a problem. If it returns 1, your transactions can be committed.
You can turn on XACT_ABORT, and then any SQL statement inside a transaction which generates an error will cause the transaction to be uncommittable.