What happens if rollback () failed to complete a transaction in MySQL?

Question

try { Connection conn = ... MySQL connection ...; conn.setAutoCommit(false); // transaction starts .... do something .... } catch (MySQLException e) { try { conn.rollback(); } catch (Exception e) { // What happens now? } } 

For a MySQL server (InnoDB Engine), what happens if rollback () failed to complete a transaction? (i.e. the network goes down when rollback () is done , etc.)

Does this mean that the database remains damaged or is there a way to restore the MySQL server from "incomplete" rollbacks?

Answer 1

Does this mean that the database remains damaged

Please note that nowhere in the code snippet is the database damaged. It simultaneously tracks both the initial state of the data and the changes that you make in your transaction.

The exceptions rollback() by rollback() are for the client, not the server. Disconnecting the network when trying to roll back throws an exception, so that the client can try to deal with this, and because there is no need to continue working. From a server perspective, rollback is an explicit instruction to discard the contents of a transaction. If the rollback command never reaches the database, the database will simply be paused when making changes until it decides that it is no longer needed, after which the changes will be cleared to clear memory or disk space on the server.

If you have not already seen this, you are probably looking for the term ACID ; this describes how databases and other parallel systems should be designed to mitigate such failures. A database compatible with ACID must remain consistent, even if there is a physical failure halfway through commit or rollback - the last step to commit the change (within the database) must be atomic so that it either succeeds or is discarded.

---

As a tangential example, Mercurial raises similar concerns about ensuring that commits never leave the repo in an inconsistent state. When a user makes changes, updates must be written to several files, and any of these entries may fail. In this way, they write down in order to avoid inconsistencies.

• First, individual differences between the files are added to the related revlog files in the repo associated with the change set identifier.
• Then the manifest listing these changes is updated, again bound to the change set identifier.
• Only after all of the above operations have succeeded, is it the change identifier itself, recorded in the change list (which is a single atom record). If this write succeeds, the commit is successful.

If Mercurial encounters an unknown change set identifier in revlog or manifest files, it ignores it; thereby ensuring that the change is not fully or completely implemented.

_{It has been some time since I messed around with the internal Mercurial, maybe I got some of these confused, but the essence is true.}