debugsqlModerate
What happens to a transaction if the network connection fails?
Viewed 0 times
thefailswhattransactionhappensnetworkconnection
Problem
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?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#60001, answer score: 12
Revisions (0)
No revisions yet.