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

Uncommitted changes are not visible outside of the connection, so the time of the rollback is irrelevant. So yes, the transaction is eventually rolled back.

Answer 2

The server will rollback immedeatly any uncommited transaction when a session is closed.
The ADO pool is reponsible for clearing any uncommited transaction before returning a transaction to the pool. If you dispose a connection with pending transactions, it will rollback.

Transactions can be started by the client using the ADO API (SqlConnection.BeginTransaction) or by executing an BEGIN TRANSACTION statement. The TDS protocol between client and server has special tokens informing the client when a transaction was started/commited like this so ADO knows that the connection has pending transactions even if they are started in T-SQL code.

Answer 3

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"

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.