TSQL - Uncaught throw in transaction does not rollback?

Question

I assumed uncaught throw inside a transaction will roll it back. But testing with this code, seems the transaction stays open. Is this normal behavior?

begin transaction;
    throw 50001, N'Exception', 1;
commit transaction;

I use this query:

select * from sys.sysprocesses where open_tran = 1;

to list transactions and see 1 open. Once I run commit on that connection, it closes.

So, when I throw do I need to always rollback myself before? And what if some other code throws in my transaction but outside my code?

Normally it's not an issue as closing the connection ends it. But if I sp_getapplock bound to transaction, it stays locked if I throw without manual rollback.

May help: https://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error — ljcordero, Jan 10 '20 at 17:17
The behavior you describe is explained [in this article](http://www.sommarskog.se/error_handling/Part2.html#BatchAbort2012) by Erland Sommarskog and requires XACT_ABORT to be OFF. The batch is terminated but the transaction is not. As Erland explains, careful coding and no long-running transactions should mitigate this unfortunate behavior — Panagiotis Kanavos, Jan 10 '20 at 17:31
Why are you using `sp_getapplock`? The only valid reason is to prevent a stored procedure from executing twice. In this case you *have* to use `try/catch` instead leaving things to chance (aka ambient settings) — Panagiotis Kanavos, Jan 10 '20 at 17:34
@PanagiotisKanavos A mutex for concurrency handling in a queue table. — CodeAngry, Jan 10 '20 at 17:38
That's not a valid use. For starters, it doesn't scale - it's like trying to use SERIALIZABLE to ensure atomicity - too restrictive. Second, there's no guarantee it works to begin with as you found out. If you search you'll find ways to implement somewhat performant queues using the appropriate lock hints like TABLOCK and HOLDLOCK but in the end, only a destructive read (ie DELETE .... OUTPUT deleted....) is really safe. Even so, there are far safer and faster distributed queueing options — Panagiotis Kanavos, Jan 10 '20 at 17:38
@PanagiotisKanavos I know but for 100 users and just a 2 field update operation, it's good enough. If I need to scale I'll go for dequeue via `delete`. — CodeAngry, Jan 10 '20 at 17:52
Check [this article](https://rusanu.com/2010/03/26/using-tables-as-queues/) by Remus Rusany on using tables as queues. It's one of the best ones. — Panagiotis Kanavos, Jan 10 '20 at 17:52
@CodeAngry I thought so too. Until a few months later, when I found the lost records — Panagiotis Kanavos, Jan 10 '20 at 17:52
Problems with lock-based queues are *transient* and extremely hard to reproduce through tests. The DELETE/OUTPUT techniques though are trivial - far less code to implement than any kind of try/catch, sp_getapplcok or ultrahinted statements. — Panagiotis Kanavos, Jan 10 '20 at 18:03
@PanagiotisKanavos Before I got to `applocks` I experienced all the deadlocks you can get. Reproducing them is not hard. Launch in C# 100 threads with a locked mutex, and unlock the mutex when all are ready. If you launch without them locked, it's hard to see the errors. Once you unlock the Mutex, the deadlocks will start pouring in if code is prone to deadlocks. For my small use case, this method works. So I don't have to rethink the queue on a delete table as the queue work selection is quite complex. — CodeAngry, Jan 10 '20 at 21:53

tukan · Accepted Answer · 2020-01-11T06:20:55.233

I think you misunderstand the trow here. As you have written it, it leaves the connection opened because the commit transaction; is not executed. The begin transaction; (increments the @@TRANCOUNT counter), but throw does not automatically decrese it! (no automatic rollback).

When throw is executed it tries to find a catch statement if not found only error is shown and the the rest is terminated.

To quote the MSDN:

If a TRY...CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16.

In your simple case you could extend it like this:

BEGIN TRY
    begin transaction;
    throw 50001, N'Exception', 1;
END TRY
BEGIN CATCH
    IF <boolean_expression_for_commit>
       commit transaction;
    ELSE
       rollback transaction;
END CATCH

That will depend on your usecase. Both commit and rollback decrement the @@TRANCOUNT counter. You will get your transaction closed based on the <boolean_expression_for_commit>.

EDIT The OP wanted to know more about XACT_ABORT

If the XACT_ABORT is set to ON the TROW causes rollback on the whole transaction even when CATCH is missing. That will decrese the transaction counter and close the open transaction.

There is a catch however. If a developer wants to create a log in a CATCH then rollback is performed also on the CATCH block! (no log is created)

Things can get weird when SET XACT_ABORT OFF (the default). It may or may not leave the transaction opened based on severity of the error. If the error is considered sereve enough it will still rollback. In your case a simple THROW is not severe enough.

Note: That is also a reason why now THROW should be used instread of RAISERROR. The THROW follows the XACT_ABORT setting, RAISEERROR ignores it.

Add something about the `xact_abort` and I'll accept this tomorrow. — CodeAngry, Jan 10 '20 at 21:50
@CodeAngry I have added information about `XACT_ABORT` as you wished. I usually use `XACT_ABORT` when developing. Usually on a production code I tend to use `TRY...CATCH` because I want to have the `THROW` logged. — tukan, Jan 11 '20 at 06:29

TSQL - Uncaught throw in transaction does not rollback?

1 Answers1