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?
-
3if connection is lost uncommitted transaction is rolled back– akuzminskyMar 2, 2014 at 22:59
Add a comment
|
2 Answers
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.
answered Mar 3, 2014 at 0:59
-
1But 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?– JimMar 3, 2014 at 22:12
To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement.
