46

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?

gbn
  • 422,506
  • 82
  • 585
  • 676
JohnnyM
  • 28,758
  • 10
  • 38
  • 37

3 Answers3

26

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.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    Connection pooling happens on the client side, so if the client internally aborts a connection without actually closing it than it is responsible for issuing a rollback or doing whatever it needs to resolve this situation. – Fozi Oct 08 '09 at 21:35
  • @Fozi: unless you have SET XACT_ABORT ON. – gbn Oct 09 '09 at 18:13
  • 1
    @gbn : links to the sql team and msdn are now dead links –  Jun 28 '21 at 20:25
8

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.

Fozi
  • 4,973
  • 1
  • 32
  • 56
  • 10
    But it can hang other connections (waiting for a commit/rollback) depending on your isolation so the timing still matters. – Adam Batkin Oct 08 '09 at 18:21
  • 4
    Correct, but the emphasis is "eventually", which is a *bad thing* – gbn Oct 08 '09 at 18:46
  • I guess it is rolled back as soon as the TCP (or whatever) connection is known to be closed/broken. For connection pooling, see gbn's answer. – Fozi Oct 08 '09 at 21:32
  • Unless you're using NOLOCK or ISOLATION LEVEL READ UNCOMMITTED elsewhere. – tuseau Aug 22 '14 at 09:41
  • I am using this `ISOLATION LEVEL READ UNCOMMITTED` will my uncommitted changes be read on a new connection? – Barak Jan 06 '17 at 04:53
8

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569