Rolling Back to a Savepoint in MySQL InnoDB: Finding Savepoints, Binlog and InnoDB Undo Rollback, and Deleting Savepoints

This article, based on MySQL 8.0.32 source code with the InnoDB storage engine, demonstrates the complete process of rolling back a transaction to a specific savepoint.

Preparation

First, a test table t1 is created and populated with sample data:

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 series of SQL statements creates three savepoints and performs inserts, updates, and finally a rollback to savept2 :

/* 1 */ begin;
/* 2 */ insert into t1(id, i1) values(50, 501);
/* 3 */ savepoint savept1;
/* 4 */ insert into t1(id, i1) values(60, 601);
/* 5 */ savepoint savept2;
/* 6 */ update t1 set i1 = 100 where id = 10;
/* 7 */ savepoint savept3;
/* 8 */ insert into t1(id, i1) values(70, 701);
/* 9 */ rollback to savept2;

Each SQL statement generates an undo log entry; the numbers of those entries are noted in the article.

2. Finding the Savepoint

Every user thread maintains an m_savepoints linked list. When a savepoint is created, its object is appended to the list, and each object’s prev pointer links to the previously created savepoint. To locate a target savepoint, the list is traversed from the newest node backwards, comparing the saved name with the desired one. If the name is not found, MySQL returns the error:

(1305, 'SAVEPOINT xxx does not exist')

The savepoint object stores two crucial pieces of information: the binlog offset at creation time and the number of the last undo log generated before the savepoint.

3. Binlog Rollback

Rolling back a savepoint requires discarding binlog records generated after the savepoint’s binlog offset. Two situations are considered:

Case 1 : Only part of the in‑memory binlog buffer needs to be discarded. The write_pos pointer in the IO_CACHE is moved back to the position calculated as binlog_offset - pos_in_file .

Case 2 : The entire buffer and part of the on‑disk temporary file must be discarded. First, write_pos is moved to the start of the buffer, discarding all in‑memory binlog entries; then pos_in_file is moved to the saved binlog offset, discarding the later part of the temporary file.

In the example, rolling back SQL 9 to savept2 falls under Case 1.

4. InnoDB Undo Rollback

Each row modification creates an undo log. To rollback to a savepoint, InnoDB reads undo logs backward from the most recent entry until it reaches the undo log number stored in the target savepoint. For each undo log, the reverse operation is applied:

Insert → Delete

Update → Re‑apply the previous column values

Delete → Re‑insert the row

In the example, undo logs with numbers 4 and 3 are processed, deleting the row with id=70 and restoring i1 of id=10 from 100 back to 101. When the undo log number equals the one saved in savept2 (number 2), the rollback stops.

5. Deleting Subsequent Savepoints

After binlog and InnoDB rollbacks, any savepoints created after the target must be removed. In the example, savept3 is deleted, leaving the m_savepoints list ending with savept2 .

6. Summary

To rollback to a savepoint in MySQL:

Traverse the thread’s m_savepoints list to locate the savepoint.

Use the stored binlog offset and undo log number to discard relevant binlog entries (binlog rollback) and apply undo logs in reverse order (InnoDB rollback).

Delete any savepoints that were created after the target savepoint.

This procedure ensures the transaction state is restored precisely to the point where the savepoint was defined.