What happens when rollback() fails for 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 MySQL server (InnoDB Engine), what happens if rollback() fails for a transaction? (i.e. network goes down right while rollback() is running, etc...)

Does it mean that DB remains corrupted, or is there any way for MySQL server to recover from "unfinished" rollbacks?

Answer 1

Does it mean that DB remains corrupted

Note that at no point in your code snippet has the DB become corrupted. It's simultaneously keeping track of both the original state of the data and the changes you're making in your transaction.

Exceptions raised by rollback() are for the client's benefit, not the server. A network outage while trying to rollback triggers an exception so that the client can try to handle it, and because there's no use in proceeding normally. From the server's perspective a rollback is an explicit instruction to discard the contents of the transaction. If the rollback command never reaches the database, the database will simply hold off on committing the changes until it decides it's no longer needed, at which point the changes will be purged to clear up memory or disk space in the server.

If you haven't seen it before, you're probably looking for the term ACID; this describes how databases and other concurrent systems have to be designed to mitigate these sort of failures. An ACID-compliant database is intended to remain consistent even if there is a physical failure midway through the commit or rollback - the final step to commit the change (inside the DB) should be atomic, so that either it succeeds, or is discarded.

---

As a tangential example, Mercurial has a similar concern regarding ensuring commits never leave the repo in an inconsistent state. When a user commits a change, updates need to be written to multiple files, and any one of those writes could fail. So it does these writes in a careful order to ensure inconsistencies are avoided.

1. First the individual file diffs are appended to their associated revlog files in the repo, associated with a changeset ID.
2. Then the manifest listing these changes is updated, again tied to the changeset ID.
3. Only once all of the above operations have succeeded is the changeset ID itself recorded in the changelog (which is a single atomic write). If this write succeeds, the commit has succeeded.

If Mercurial comes across an unknown changeset ID in the revlog or manifest files, it ignores it; thereby ensuring a change is either fully committed or not at all.

_{It's been a while since I've poked around at the Mercurial internals, it's entirely possible I got some of this muddled, but the gist is correct.}