Jason Kresowaty
jason@binarycoder.net
3/14/2009

Nested/Isolated Transactions

For some time, I have wished that SQL Server were more flexible in its transaction handling. More specifically, it would be useful if it were possible to begin and commit independent transactions nested in parent transactions regardless of whether or not the parent transactions commit or rollback (this enables logging scenarios). It would also be useful for this to be designed in a general-purpose way that allows the programmer fine-grained control over what transactions are committed or rolled back when multiple transactions are in progress (this enables scenarios where a part of the high-level transaction needs to be retried or recovery is to be attempted after part of the high-level transaction has failed).

The following is my attempt to articulate transaction statements to support multiple isolated transactions with simultaneous commit/rollback capabilities. This includes, for example, ways to connect transactions so that you can commit them together while at the same time rolling them back separately, if need be. Another key feature is a suspend statement which puts the @@TRANCOUNT back to zero. Hopefully, these ideas would fit well with an environment that uses stored procedures and existing programming APIs.

This is intended to get some ideas out in the open. It is not complete, and there are probably some "gotchas".

Notes:
  1. There are some features to help avoid "leaking" transactions. If you specify COMMIT TRANSACTION or ROLLBACK TRANSACTION without parameters, suspended child transactions are affected in well-defined ways.
  2. Transactions that are in the suspended state at the time of an error should generally be immune from being placed in the XACT_STATE() = -1 (uncommittable) state. There may need to be some special cases.
  3. A stored procedure that suspends the transaction that was the parent transaction at the time the stored procedure was entered must resume that transaction or roll it back before it exits. If the parent transaction has not been resumed or is not in a rolled back state upon exit, automatically resume the parent transaction and raise an error.
  4. In the rare case any of the core transaction-related statements described here encounter an error, abort the entire batch, rolling back all work in progress.
  5. When a @tran_name_variable is specified as a UNIQUEIDENTIFIER variable type, remove all symbol characters so that it fits in the 32 character limit.

Statements

SUSPEND TRANSACTION

Suspends the parent transaction and its recursive parents with recursion stopping if it encounters a parent transaction already in the suspended state. @@TRANCOUNT goes to zero.

RESUME TRANSACTION

Resumes the transactions that actually transitioned from nonsuspended to suspended by the matching SUSPEND TRANSACTION. @@TRANCOUNT is restored to what it was at the SUSPEND TRANSACTION.

RESUME TRANSACTION name

Resumes a named transaction. If the parent transaction is named, same behavior as RESUME TRANSACTION. If a sibling transaction is named, resume the sibling transaction. Transactions other than the immediate parent and its sibling are ineligible and specifying any of them aborts the batch.

COMMIT TRANSACTION

Commits all non-suspended parent transactions and joined child transactions. If a parent transaction has non-joined suspended child transactions, abort the entire batch (i.e., this assumes that leaked transactions indicate a severe error). Suspended parent transactions stay suspended.

COMMIT TRANSACTION name[,name...]

Commits one or more named transactions, they can possibly be suspended transactions. There need to be rules, not formulated here, as to which transactions are eligible to be specified and how child transactions are recursively processed.

ROLLBACK TRANSACTION

Rolls back the parent transaction. If the parent transaction has suspended children, roll them back as well. Recursive parents of the parent transaction in the nonsuspended state are rolled back while recursive parents of the parent transaction in the suspended state stay suspended.

ROLLBACK TRANSACTION name[,name...]

Rolls back one or more named transactions, they can possibly be suspended transactions. There need to be rules, not formulated here, as to which transactions are eligible to be specified and how child transactions are recursively processed.

ROLLBACK TRANSACTION ALL

Rolls back all suspended and nonsuspended transactions in the session. This is a "sledgehammer" that might be nice to have. It should be possible to write correct programs without it.

JOIN TRANSACTION name

Causes the named transaction to COMMIT or ROLLBACK whenever the current parent transaction commits or rolls back, respectively. This overrides the case where COMMIT TRANSACTION would abort the batch due to a suspended child. Instead it would COMMIT that child.

Examples

Simultaneous Commit

DECLARE @A UNIQUEIDENTIFIER; SET @A = NEWID()
DECLARE @B UNIQUEIDENTIFIER; SET @B = NEWID()

DECLARE @C UNIQUEIDENTIFIER; SET @C = NEWID()

BEGIN TRANSACTION @A
SUSPEND TRANSACTION
BEGIN TRANSACTION @B
SUSPEND TRANSACTION @B
BEGIN TRANSACTION @C
SUSPEND TRANSACTION @C
COMMIT TRANSACTION @B,@C
COMMIT TRANSACTION @A

Distributed Transaction

Ideally, after executing SUSPEND TRANSACTION, it should be possible to use BEGIN DISTRIBUTED TRANSACTION to start an isolated distributed transaction. Ideally, isolatated distributed transactions would also be able to participate in the simultaneous commits and roll backs. One usage scenario is to retry a recoverable error such as a deadlock without rolling back the entire parent transaction while at the same time ensuring that, if the transactions commit, they commit together. 

DECLARE @D UNIQUEIDENTIFIER

BEGIN TRANSACTION

// Do work in PRIMARY database

SUSPEND TRANSACTION

WHILE @Success = 0
BEGIN
	SET @D = NEWID()
	BEGIN TRY
		BEGIN DISTRIBUTED TRANSACTION @D
		// Do work in SECONDARY database
		SET @Success = 1
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION @D
	END CATCH
END

RESUME TRANSACTION
JOIN TRANSACTION @D // Ensure transactions commit together

// Do some more work in SECONDARY database

COMMIT TRANSACTION // Commit both current (parent) txn and txn "D"