About eight years ago, Dan Guzman wrote a post called Use Caution with Explicit Transactions in Stored Procedures. In it, he talks about error handling and transactions, specifically with respect to the
Microsoft’s docs for
XACT_ABORT are pretty clear. The setting determines whether “SQL Server automatically rolls back the current transaction when a statement raises an error”.
And in nearly every scenario I can think of that uses a transaction, this automatic rollback is the desired behavior. The problem is that it’s not the default behavior. And this leads to Dan Guzman’s advice where he strongly recommends that
SET XACT_ABORT ON be included “in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.”
What Could Go Wrong?
When a statement inside a transaction fails (for whatever reason) and
XACT_ABORT is set to off, then…
- That transaction is abandoned.
- Any locks taken during that transaction are still held.
- Even if you close the connection from the application, .NET’s connection pooling will keep that connection alive and the transaction on SQL Server stays open.
- Fortunately, if another application reuses the same database connection from the connection pool, the old transaction will be rolled back.
- Unfortunately developers can’t count on that happening immediately.
- Abandoned transactions can cause excessive blocking leading to a concurrency traffic jam.
- Also, abandoned transactions can interfere with downstream solutions. Specifically ones that depend on the transaction log. Transaction logs can grow indefinitely. Replication solutions can suffer. If RCSI is enabled, the version store can get out of hand.
Some (or all) of those things happened to us last week.
Steps To Take
Here are some things you can do:
Do you have abandoned transactions right now?
It’s not too hard to identify these abandoned transactions:
-- do you have abandoned transactions? select p.spid, s.text as last_sql from sys.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s where p.status = 'sleeping' and p.open_tran > 0
Also if you use sp_whoisactive, you can identify these processes as those with a sleeping status and at least one open transaction. But there’s a trick I use to identify these quickly. The sql_text value in the output of sp_whoisactive will typically begin with
CREATE PROCEDURE. When I see that, I know it’s time to check whether this connection is sleeping or not.
SET XACT_ABORT ON
Follow Dan Guzman’s advice to include
SET XACT_ABORT ON in all stored procedures with explicit transactions.
You can actually find the procedures in your database that need a closer look
-- find procedures that could suffer from abandoned transactions SELECT * FROM sys.procedures where OBJECT_DEFINITION(object_id) like '%BEGIN TRAN%' and OBJECT_DEFINITION(object_id) not like '%XACT_ABORT%' order by name
Set XACT_ABORT ON server-wide
If you choose, you can decide to set the default value for all connections to your server. You can do that using Management Studio:
Or via a script:
-- turn the server's xact_abort default on declare @user_options_value bigint; select @user_options_value = cast(value as bigint) from sys.configurations where name = 'user options'; set @user_options_value = @user_options_value | 0x4000; exec sp_configure N'user options', @user_options_value; RECONFIGURE WITH OVERRIDE; -- (if necessary) turn the server's xact_abort default off declare @user_options_value bigint; select @user_options_value = cast(value as bigint) from sys.configurations where name = 'user options'; set @user_options_value = @user_options_value & 0x3fff; exec sp_configure N'user options', @user_options_value; RECONFIGURE WITH OVERRIDE;
I love code reviews. They’re more than just a tool for improving quality. They’re learning opportunities and teaching opportunities for all involved.
Last week, I invited readers to have a look at a procedure in a post called Code Review This Procedure. I was looking for anyone to suggest turning on
XACT_ABORT as a best practice. It’s a best practice where I work, but things like this slip through. We should have caught this not just during testing, but during development. It’s obvious with hindsight. But I wanted to determine how obvious it was without that hindsight. I guess it was pretty subtle, the
XACT_ABORT was not mentioned once. That’s either because the setting is not often used by most developers, or because it is easily overlooked.
But here are some other thoughts that readers had:
Many people pointed at concurrency and transaction isolation levels as a problem. It turns out that concurrency is very hard to do right and nearly impossible to verify by inspection. In fact one of my favorite blog posts is about getting concurrency right. It’s called Mythbusting: Concurrent Update/Insert Solutions. The lesson here is just try it.
Cody Konior (blog) submitted my favorite comment. Cody writes “I often can’t disentangle what the actual impact of various isolation levels would be so I go a different route; which is to create a quick and dirty load test”. I can’t determine concurrency solely by inspection either, which is why I never try. Cody determined that after hammering this procedure, it never failed.
He’s entirely right. Concurrency is done correctly here. Ironically, most of the fixes suggested in other people’s code reviews actually introduced concurrency issues like deadlocks or primary key violations.
People also suggested that blocking would become excessive. It turns out that throughput does not suffer either. My testing framework still managed to process 25,000 batches per second on my desktop without error.
Some people pointed out that if NULL values or other incorrect values were passed in, then a foreign key violation could be thrown. And they suggested that the procedure should validate the inputs. But what then? If there’s a problem, then there are two choices. Choice one, raise no error and exit quietly which is not ideal. Or choice 2, raise a new error which is not a significant improvement over the existing implementation.
Avoiding the transaction altogether
It is possible to rewrite this procedure without using an explicit transaction. Without the explicit transaction, there’s no chance of abandoning it. And no chance of encountering the trouble that goes with abandoned transactions. But it’s still necessary to worry about concurrency. Solutions that use single statements like
INSERT...WHERE NOT EXISTS still need
Since you’re EVENT_TICKETS table has required foreign keys (evidenced by NOT NULL on all columns with foreign key references) the proc should be validating that the input parameter values exist in the foreign key tables before trying to insert into EVENT_TICKETS. If it doesn’t find any one of them it should throw an error and gracefully rollback the transaction and return from the proc.
The way its designed currently I think you could get an error on inserting to EVENT_TICKETS that would fail the proc and leave the transaction open.