4

What happens to a transaction when the client connection is lost.
Example the client is a web application code that starts a transaction to a remote server instance. The client sends the sql code to the server and waits for the transaction to complete.
What happens if the network connection fails? Does it depend on the point we are in a transaction? Is it irrelevant? Is it aborted?

  • 3
    if connection is lost uncommitted transaction is rolled back – akuzminsky Mar 2 '14 at 22:59
9

http://dev.mysql.com/doc/refman/5.6/en/innodb-implicit-commit.html says:

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

That means if your session disconnects for any reason, either by choice, or else because an error occurs like the network connection fails, etc., then a transaction in progress is rolled back.

If you think about it, this is the only appropriate thing for the RDBMS to do. It doesn't know if you had completed the work for your transaction only partially. For example, you might have debited one bank account, and you intended to credit another bank account to reflect a funds transfer. If the debit has occurred but the credit has not, and the connection is lost, MySQL has no way of completing the transaction or even of knowing what you wanted to do next. So it has to roll back all work so far.

  • 1
    But this assumes that I execute statements in my application code one at a time.So if the connection is lost before doing: stmt.commit() then it is rolled back.What if I pass a series of SQL statements? – Jim Mar 3 '14 at 22:12
0

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement.

Source: https://dev.mysql.com/doc/refman/5.6/en/commit.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.