An application times out, but its SQL transaction can remain open and keep blocking other work. SET XACT_ABORT ON makes a client attention or runtime error roll back the work instead of leaving locks behind.
Reproduce the Blocking Pattern
In a disposable test database, create a small table. Open session one, begin a transaction, update a row, then wait. From the client, cancel the command or use a short command timeout while the wait is active. In session two, try to read or update the same row under ordinary locking. The follower blocks if the transaction remains open. I use two query windows so the open transaction is visible rather than theoretical. Clean up with ROLLBACK after the test. Do not run the demo against a production row.
CREATE TABLE dbo.TimeoutDemo (id int NOT NULL PRIMARY KEY, value_text varchar(20) NOT NULL);
INSERT dbo.TimeoutDemo VALUES (1,'before');
BEGIN TRANSACTION;
UPDATE dbo.TimeoutDemo SET value_text = 'after' WHERE id = 1;
WAITFOR DELAY '00:00:30';
-- Cancel the command from the client during the wait, then inspect @@TRANCOUNT.
ROLLBACK TRANSACTION;In session two, run the next query while session one still holds its transaction. It should wait under ordinary read committed locking. After the test, return to session one and roll back, then drop the test table. The timing of the two windows is the point of the demonstration.
SELECT value_text FROM dbo.TimeoutDemo WHERE id = 1;Why Cancellation Is Different
TRY…CATCH handles many SQL errors inside a batch, but a client attention such as a timeout is not a normal catchable T-SQL error. The client can stop waiting while the server session still holds a transaction. A connection pool can later reuse that connection, spreading the confusion. SET XACT_ABORT ON causes the transaction to be rolled back when a runtime error or attention aborts the request. I set it near the start of procedures that own transactions and still use TRY…CATCH for catchable errors.
Do not rely on an application timeout alone to release locks. Test the specific client driver and procedure path, since cancellation and connection handling belong to the end-to-end behavior.
Pair SET XACT_ABORT ON With Clear Transaction Code
Use TRY…CATCH, BEGIN TRANSACTION, COMMIT, and a CATCH that rolls back when XACT_STATE() is nonzero, then THROW. Keep external calls outside the transaction where possible. I avoid swallowing the error and returning success after rollback. A caller needs to know that the operation failed. Nested procedure contracts need care; if a caller owns the transaction, a callee should not unexpectedly commit it.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = N'Processed' WHERE OrderID = 42;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;Find Sleepers Holding Transactions
Query sys.dm_exec_sessions for sleeping sessions with open_transaction_count above zero, then join transaction information to see when work began. Pair it with locks and blocking_session_id from active requests. A sleeping open transaction deserves investigation, not an automatic KILL. I capture its host, program, login, and last command before contacting the owner. The last command can be unrelated to the statement that opened the transaction, so present it as a clue.
SELECT s.session_id, s.host_name, s.program_name,
s.open_transaction_count, at.transaction_begin_time
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS st ON st.session_id = s.session_id
JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = st.transaction_id
WHERE s.status = N'sleeping' AND s.open_transaction_count > 0
ORDER BY at.transaction_begin_time;
Run the Two-Session Check Again With SET XACT_ABORT ON
Repeat the timeout test with SET XACT_ABORT ON at the start of session one's batch. Verify the transaction is gone after cancellation and session two proceeds. Also test a catchable statement error to confirm the CATCH block rolls back and rethrows. I record the driver timeout setting, because an SSMS Cancel button and an application command timeout can take different paths. The accepted behavior is measured in the actual application, not inferred from one query window.
I ran this through .NET SqlClient with a three-second command timeout. Without the setting, the timed-out connection still showed @@TRANCOUNT of 1, the sleeper query listed it, and the second session's read timed out. With the setting at the top of the same batch, @@TRANCOUNT came back 0 and the second session read the old value.
What if the work committed just before the response was lost? The client still needs idempotency or a status check before retrying. XACT_ABORT protects a transaction aborted by the server; it cannot tell a caller whether a response was lost after commit.
Understand the Attention Boundary
When a client stops waiting, SQL Server receives an attention signal. That path does not behave like a normal error raised inside TRY…CATCH. A CATCH block that looks perfect for constraint errors can be bypassed by a client timeout. I test the actual driver timeout, since cancellation behavior can vary by client path. The setting rolls the transaction back when the request is aborted. The application should still dispose of the broken command and connection correctly.
I also check for code that turns XACT_ABORT OFF later in the call chain. A nested procedure can silently change session behavior. Keep the setting explicit near the transaction owner and document what callers expect. Dynamic SQL and linked-server work can add their own failure paths; test those when they are part of the procedure.
Design the Retry Outcome
A timeout can happen before the write, during an uncommitted transaction, or after a commit whose response never reached the caller. No server-side setting can make those three cases look identical to the application. Use a request identifier and status lookup for operations that cannot safely repeat. I ask the application team to log the request ID, timeout, and final reconciliation result. That turns a vague "SQL timeout" into an answer about whether the business operation happened.
What does the on-call DBA do with a sleeping open transaction? Capture session and blocking evidence, identify the owner, and coordinate cleanup. Do not build a job that kills every sleeping transaction by age. Some maintenance tools and applications hold them intentionally, even if that design deserves review. Fix the procedure and client lifecycle so the condition stops recurring; emergency KILL is an incident action, not the standard transaction policy.
Check XACT_STATE() in the CATCH block because some errors leave a transaction uncommittable. Rolling back when XACT_STATE() is nonzero is safe for the transaction owner. Avoid a CATCH that blindly commits after an error. I test a constraint failure, a client cancellation, and a successful path separately; each exercises a different branch of the transaction design. The lock does not know the client gave up.
Keep Transactions Short Even With SET XACT_ABORT ON
Even with safe rollback, long transactions block and grow the log. Do not wrap user input, network calls, or slow reporting work inside the transaction that changes a row. I review the procedure for waits and calls that extend the critical section. Monitor sleeping open transactions and alert before they block a large queue. A timeout should be an exceptional event, not a routine cleanup mechanism.
The best fix includes SQL transaction handling, client disposal, and retry rules. SET XACT_ABORT ON is a strong part of that design, and the two-session test shows whether the whole path behaves as intended.
Related reading on this blog: Finding Open Transactions for Session: @@TRANCOUNT and Before You KILL a Session: Estimating the Rollback Cost.
A client timeout is not a rollback, it is only the client giving up on the wait.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
