What happens to an uncommitted transaction when the connection is closed?

by

It can stay open while connection pooling applies. Example: command timeout can leave locks and TXN because the client sends as “abort”.

2 solutions:

  • Test in the client, literally:

    IF @@TRANCOUNT <> 0 ROLLBACK TRAN

  • Use SET XACT_ABORT ON to ensured a TXN is cleaned up: Question 1 and Question 2

I always use SET XACT_ABORT ON.

From this SQL Team blog:

Note that with connection pooling,
simply closing the connection without
a rollback will only return the
connection to the pool and the
transaction will remain open until
later reused or removed from the pool.
This can result in locks begin held
unnecessary and cause other timeouts
and rolling block

From MSDN, section “Transaction Support” (my bold)

When a connection is closed, it is
released back into the pool and into
the appropriate subdivision based on
its transaction context. Therefore,
you can close the connection without
generating an error, even though a
distributed transaction is still
pending. This allows you to commit or
abort the distributed transaction at a
later time.

More Related Contents:

  1. SQL Server – transactions roll back on error?
  2. Correct use of transactions in SQL Server
  3. What does a transaction around a single statement do?
  4. Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
  5. What does sp_reset_connection do?
  6. Get Parent with multiple children in desc order
  7. Reset identity seed after deleting records in SQL Server
  8. How to avoid the “divide by zero” error in SQL?
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. How to simulate UNPIVOT in Access?
  11. Better techniques for trimming leading zeros in SQL Server?
  12. What does =* mean?
  13. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  14. SSIS Package not wanting to fetch metadata of temporary table
  15. SQL GROUP BY CASE statement with aggregate function
  16. How can I include null values in a MIN or MAX?
  17. Favourite performance tuning tricks [closed]
  18. Query a database based on result of query from another database
  19. How to delete the top 1000 rows from a table using Sql Server 2008?
  20. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  21. Cumulative Total in MS Sql server [duplicate]
  22. What does ON [PRIMARY] mean?
  23. SQL query to group by day
  24. Export table data from one SQL Server to another
  25. Should I use the CASCADE DELETE rule? [duplicate]
  26. Dynamic order direction
  27. Why is RAND() not producing random numbers?
  28. SQL, How to Concatenate results?
  29. Should every SQL Server foreign key have a matching index? [closed]
  30. Getting warning: Null value is eliminated by an aggregate or other SET operation

Leave a Comment