Is it time for SQL Server to have Isolated Transactions?

re: Is it time for SQL Server to have Isolated Transactions?

jacobsebastian — Tue, 10 Mar 2009 00:51:55 GMT

I will certainly support this feature request. Two of the use cases Arnie listed (Logging and SB Messages) are very much true in my environment.

I think if an Isolated transaction fails, it should not break the parent transaction. If an error occurs and it is not handled, the execution should jump to the next statement after the END block of the Isolated Transaction.

re: Is it time for SQL Server to have Isolated Transactions?

Mike C — Tue, 10 Mar 2009 02:44:55 GMT

I've had plenty of problems like you mention trying to log errors in transactions, and that would be my #1 use case for this feature. I'm 100% for it!

re: Is it time for SQL Server to have Isolated Transactions?

MontyMole — Tue, 10 Mar 2009 10:28:07 GMT

Here's the connect item to vote for:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296870

re: Is it time for SQL Server to have Isolated Transactions?

TiborKaraszi — Tue, 10 Mar 2009 10:31:18 GMT

Hi Arnie,

I think it is time for this functionality. I'm all for the KISS approach. We do not need more complex transaction semantice in SQL Server. It is bad enough as it is (sometimes I wish MS could begin from a blank piece of paper with their transaction handling commands and semantics).

Btw, I think a more common term for the functionality is "autonomous transactions" (which I probably can't spell).

re: Is it time for SQL Server to have Isolated Transactions?

Linchi Shea — Tue, 10 Mar 2009 13:14:27 GMT

The other reason is that Oracle has had the feature for a long time. This feature is very useful for writing administrative scripts. You got my vote.

re: Is it time for SQL Server to have Isolated Transactions?

Andrew Kelly — Tue, 10 Mar 2009 13:26:25 GMT

Yes sir, this would be welcomed by most for sure.

re: Is it time for SQL Server to have Isolated Transactions?

RobertDavis — Tue, 10 Mar 2009 20:54:58 GMT

I agree with your suggestion for the isolated transaction. Perhaps it could have support for calling ROLLBACK/COMMIT ISOLATED TRANSACTION to only rollback/commit the isolated transaction and ROLLBACK/COMMIT PARENT TRANSACTION to rollback/commit the parent as well.

The caveat would be that if there are other isolated transactions nested within the same parent when the parent rolls back, any already committed isolated transactions are not rolled back and any isolated transactions not started do not start.

Just a thought.

re: Is it time for SQL Server to have Isolated Transactions?

BinaryCoder — Sat, 14 Mar 2009 18:12:25 GMT

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).

I have a writeup on some ideas here: http://www.binarycoder.net/misc/nestedtransactions.html

re: Is it time for SQL Server to have Isolated Transactions?

oj_n — Sun, 15 Mar 2009 09:01:32 GMT

You do not have "autonomous transaction" in SQL yet but you can fake one by using loopback linked server. This is one of the tricks to persist data outside of the current tranx.

Works great for older SQL versions too.

re: Is it time for SQL Server to have Isolated Transactions?

bfrasca — Wed, 18 Mar 2009 14:25:26 GMT

I can only give a luke-warm endorsement as I think this violates the principles laid out in the ACID model, specifically, the Isolation component of that model. Yes, I know it would be a separate transaction but it could potentially impact the parent transaction negatively. I would probably be on board if these isolated transactions could be fired off asynchronously or pseudo-asynchronously, i.e. via a spawned process. I would hate to tell my boss that business transactions were failing because I was trying to create log entries.

One of the issues that must be overcome with this would be implementing an isolated transaction within the two and three-phase commit protocols. Distributed transactions of all types would need to be considered.

I think of it more as a nice-to-have although I've never really had any issues logging. It's easy enough to log as part of your rollback "handler", deadlock re-try logging comes to mind, although the need for log entries to persist for a failed transaction seems counter-intuitive to me except for logging problems. I guess I'd need to hear a more specific logging use case where the log entries need to persist even though the transaction failed. (I don't know enough about Service Broker to comment on that use case.) It would certainly make a great debugging tool but I'm not sure it would add a lot of value to justify the added complexity that would be required to use the capability.