What Happens to an Uncommitted Transaction When the Connection Is Closed

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

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.

What happens to a mysqli transaction when the connection is closed without a commit or rollback?

When you call begin_transaction() or set the autocommit value to 0 then you are effectively telling MySQL server "don't commit the data until I explicitly tell you to". When you write code that never calls commit then the data on the server will never be committed.

When you call mysqli::close() or if the PHP script ends, then mysqlnd (or libmysql) will send COM_QUIT command to the server. The server will then close the session and discard any data related to it, including open transactions, locks, or prepared statement handles. The MySQL server should also honour the wait_timeout setting in case the PHP script crashes and the command is never sent.

One thing that would be specific to mysqli is persistent connections. These connection are reused between PHP executions. They are commonly regarded as a good way to shoot yourself in the foot, which is why mysqli has some logic to help with that. You can use the INI setting called rollback_on_cached_plink to instruct mysqli to clean up the persistent connection whenever the script ends by issuing a rollback command.

Does a transaction end after the program got closed?

The database is going to see a connection being closed, so the transaction is definitely going to end. What's not defined is whether the transaction is committed or rolled back.

If I'm not mistaken the more popular option is to rollback any uncommitted transactions (after all it indicates that something's gone wrong and you'd prefer a rollback), but this can depend on the database being used.

What happens to uncommitted sql statements?

If there is no COMMIT or ROLLBACK statements, then the work will be rolled back when the session ends.

Some detailed explanation you'll find in Transation Management Doc

UPDATE APC is right, the doc is more specific than me:

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT
clause.

A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If
the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL
statement as a new, single statement transaction.

A user disconnects from Oracle Database. The current transaction is
committed.

A user process terminates abnormally. The current transaction is
rolled back.

Note: Applications should always explicitly commit or undo transactions before program termination.

So, I didn't really answered to your question. It depends on what your connector does at disconection.

What happens if you don't commit a transaction to a database (say, SQL Server)?

As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks.

If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.

Breaking a connection to SQL Server halfway through an execution

When SQL Server detects that a connection is broken, it should rollback any current transaction and abort the current batch¹.

Any preceding committed transactions will still be committed.

---

¹Here I'm trying to draw the distinction between scripts and batches. Many client tools support scripts containing multiple batches (delimited by GOs) and its the batches that get submitted to SQL Server, sequentially.

Does Hibernate's Session.close() automatically rollback uncommitted transactions?

I've done a bit of digging into Hibernate:

Persistence sessions keep their life-cycle somewhat independent from JDBC connections. When you close Hibernate's Session the connection is released. Exact meaning of "releasing connection" depends on how the connection was obtained in the first place:

• if the connection was provided manually (e.g. via sessionFactory.openStatelessSession(connection)) you will get your connection with possibly unfinished transaction back when calling session.close()
• in other cases calling session.close() will usually end up in calling connection.close()

No automatic session flushing or transaction commit / rollback is made by Hibernate. The same states for the JPA's EntityManager.

So what happens in the end depends on your connection provider / data source. With C3PO any unfinished transaction will be rolled-back when the connection is returned to the pool. On the other hand if you have managed JTA connection then the actual transaction handling might be completely out of scope to your application.

Does closing a SQLAlchemy ORM Session roll back uncommitted changes?

Closing a session will implicitly roll back current transactional state:

The close() method issues a expunge_all(), and releases any transactional/connection resources. When connections are returned to the connection pool, transactional state is rolled back as well.

But I'd argue that the first form is still better, since explicit is better than implicit. The author of SQLAlchemy also seems to reflect this sentiment.

---

Related Topics