Sql-server – SQL Server: affect other transactions

sql servertransaction

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example:

BEGIN TRY
  BEGIN TRAN
    -- Run updates 01
    INSERT INTO dbo.Log (Text) VALUES ('Updates 01 - big success');

    -- Run updates 02
    INSERT INTO dbo.Log (Text) VALUES ('Updates 02 - big success');

    -- and so on...
  COMMIT
END TRY
BEGIN CATCH
  ROLLBACK
  INSERT INTO dbo.Log (Text) VALUES ('At a certain step there was a problem: ' + ERROR_MESSAGE());
END CATCH

Of course, in case of errors we lose the success log entries done inside the transaction and are left only with the last (error) message inserted after the rollback took place.
Is there a way to make those INSERT INTO dbo.Log statements "persistant" before the transaction finishes?
I guess, if it were, it would fly in the face of proper transaction control, so I on some level hope this doesn't exist, but maybe somewhere in the category of tricks?

Thank you.

Best Answer

Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.

Related Solutions

Sql-server – Sleeping SPID blocking other transactions

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.

So, you need to use SET XACT_ABORT ON or add some client rollback code

See SQL Server Transaction Timeout for all the gory details

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

Sql-server – Sleeping SPID blocking other transactions

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question