Understanding MySQL Transaction Rollback: Binlog and InnoDB Rollback Process

Preparation

First a test table t1 is created and populated with sample rows using the following statements:

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t1` (`id`, `i1`) VALUES
(10,101),(20,201),(30,301),(40,401);

A short script demonstrates the rollback scenario:

/* 1 */ begin;
/* 2 */ insert into t1(id,i1) values(50,501);
/* 3 */ insert into t1(id,i1) values(60,601);
/* 4 */ rollback;

SQL statements 2 and 3 generate undo logs numbered 0 and 1 respectively; the fourth statement (rollback) is the focus of the article.

2. Binlog Rollback

During execution, the two INSERTs produce binlog entries stored in the transaction cache. When a full transaction rollback is requested, MySQL first attempts a binlog rollback, but the implementation only acts when the command is SQLCOM_ROLLBACK_TO_SAVEPOINT . For a normal ROLLBACK the condition fails, so no binlog cleanup occurs at this stage.

static int binlog_rollback(handlerton *, THD *thd, bool all) {
  DBUG_TRACE;
  int error = 0;
  if (thd->lex->sql_command == SQLCOM_ROLLBACK_TO_SAVEPOINT)
    error = mysql_bin_log.rollback(thd, all);
  return error;
}

3. InnoDB Rollback

After the (no‑op) binlog step, InnoDB processes the undo logs in reverse order. It reads undo log 1, deletes the row with id = 60 , then reads undo log 0 and deletes the row with id = 50 . When no more undo logs remain, the InnoDB rollback finishes.

4. Commit Transaction

Although the logical state of the rows is restored, the physical data pages have been modified, so InnoDB must commit these page changes. This commit differs from a regular COMMIT because the binlog does not need to be flushed; only the InnoDB transaction is committed.

5. Clear Binlog Logs

The temporary binlog data resides partly in an in‑memory buffer and partly in a temporary file. Clearing it involves resetting the buffer pointer and truncating the file after the InnoDB commit succeeds, ensuring the operation is irreversible only after a safe point.

6. Summary

The full rollback consists of three major steps: (1) attempt binlog rollback (which does nothing for a plain ROLLBACK), (2) execute InnoDB undo‑log processing to restore rows, and (3) commit the InnoDB changes and finally purge the temporary binlog logs from the transaction cache.