Atomic Compound Statements

Recently, we had a discussion about a hypothetical feature, “START TRANSACTION ON ERROR ROLLBACK”, that our users would find very useful. It would allow sending several commands to the server in one batch (a single network packet), and let the server handle the errors. This would combine efficient network use, and atomic execution. It turns out that it is already possible to do this with MariaDB, albeit with a slightly different syntax.

To execute several statements, stmt1;.. stmtN, and rollback on error, we can use MariaDB Server 10.1’s compound statement . Put a transaction inside it, combine it with an EXIT HANDLER, and here you are:

BEGIN NOT ATOMIC
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN  
   ROLLBACK;
   RESIGNAL;
  END;
 
  START TRANSACTION;
 
    stmt1;
    ....
    stmtN;

  COMMIT;
END

The statements are executed in a single transaction, which ends with a COMMIT, unless there is an exception, in which case the EXIT HANDLER takes care of ROLLBACK on any error and propagates the original error with RESIGNAL. QED.

The above is quite similar to what the ANSI standard BEGIN ATOMIC would be able to do, if it was implemented. It is not yet, but for the mentioned use case BEGIN NOT ATOMIC could already be helpful.

For illustration, here is a comparison of a conventional “atomic batch” implementation in Java vs using compounds.

Conventional example

Some Java boilerplate, lots of communication between client and server. On the positive side, portable JDBC code.

void atomicBatch(Connection con, Statement stmt, String[] commands) throws SQLException{
    try {
        con.setAutoCommit(false);
       
        for (String command : commands)
            stmt.execute(command);
        con.commit();
    }
    catch(SQLException e) {
        con.rollback();
        throw e;
    }
    finally {
        con.setAutoCommit(true);
    }
}

Compound statement example

Shorter, more efficient network communication. This does not work with MySQL (no compound statement support).

final String ATOMIC_BATCH_PREFIX = "BEGIN NOT ATOMIC DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN  ROLLBACK;   RESIGNAL;  END; START TRANSACTION;";
final String ATOMIC_BATCH_SUFFIX = "; COMMIT; END";

void atomicBatch(Statement stmt, String[] commands) throws SQLException{
   stmt.execute(ATOMIC_BATCH_PREFIX + String.join(";", Arrays.asList(commands)) + ATOMIC_BATCH_SUFFIX);
}