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

Question

Are they rolled back immediately? Are they rolled back after some period of time? Are they left in an uncommitted state?

Is the behavior the same if connection pooling is used and the connections are simply reset?

Answer 1

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.