patternsqlMinor
What are all the ways an open transaction will rollback?
Viewed 0 times
therollbackwhatallarewaysopenwilltransaction
Problem
When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions while the query is in a sleeping status, sometimes for days (showing via sp_WhoIsActive). This leads me to believe that there is an error somewhere in the application that is not allowing the transactions to commit. What are all the ways that these transactions could possibly rollback?
Should I be doing anything with transactions that have been open for hours or days? If they are going to rollback or timeout at some point anyway, is there any harm in killing the process?
- Client-side timeout (if specified)
- Client closes or restarts application
- Manual kill of the SPID
- Anything else?
Should I be doing anything with transactions that have been open for hours or days? If they are going to rollback or timeout at some point anyway, is there any harm in killing the process?
Solution
To add to what @RLF provided in his answer; the following will also result in transaction rollback.
-
-
Disconnection by the client, or server, or any bit of infrastructure in between.
-
If the underlying disk subsystem goes away, all open connections to the database will be terminated. Uncommitted Transactions will be rolled-back when the database is subsequently opened.
-
If the server is shutdown, or quits for any reason, uncommitted transactions will be rolled back when it starts up and opens the associated database.
-
If
-
Batch-scoped transactions, applicable to multiple active result sets (MARS), that have not been committed will roll back when a batch completes.
-
For completeness,
-
If the transaction log becomes full while a transaction is open, and the database is subsequently closed for any reason, roll-back will occur when the database is brought back online.
-
If
-
ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE; will rollback any open transactions.-
Disconnection by the client, or server, or any bit of infrastructure in between.
-
If the underlying disk subsystem goes away, all open connections to the database will be terminated. Uncommitted Transactions will be rolled-back when the database is subsequently opened.
-
If the server is shutdown, or quits for any reason, uncommitted transactions will be rolled back when it starts up and opens the associated database.
-
If
SET XACT_ABORT ON is set, any T-SQL statement that raises a run-time error will cause an open transaction to roll back.-
Batch-scoped transactions, applicable to multiple active result sets (MARS), that have not been committed will roll back when a batch completes.
-
For completeness,
ROLLBACK TRANSACTION will, rather obviously, roll back a transaction.-
If the transaction log becomes full while a transaction is open, and the database is subsequently closed for any reason, roll-back will occur when the database is brought back online.
-
If
SET IMPLICIT_TRANSACTIONS ON; is set, including by ANSI default, uncommitted transactions will be automatically rolled back when the client disconnects.Context
StackExchange Database Administrators Q#119865, answer score: 9
Revisions (0)
No revisions yet.