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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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"