Incident Library

IL-0436 freee · 2025-08-27 (published)databaseaws

A supremum lock left by ROLLBACK TO SAVEPOINT in a long transaction blocked inserts and filled Aurora MySQL connections

In MySQL's default REPEATABLE READ isolation, InnoDB takes gap locks between records as well as record locks. An INSERT first takes an insert intention lock on the gap it inserts into, which conflicts only with other gap locks; with an auto-increment primary key that gap on the primary index is the supremum, the virtual upper bound after the last record. A long transaction held an exclusive lock on the supremum for a long time, so every INSERT into the table waited, many threads piled up waiting for the lock to be released, the connections ran out and the service stopped.

The long transaction's only locking statement on that table was an INSERT; otherwise it ran SELECT and UPDATE on other tables and SAVEPOINT and ROLLBACK TO SAVEPOINT. freee's DBRE team reproduced the lock with the sequence begin, any read, SAVEPOINT, INSERT into the table, ROLLBACK TO SAVEPOINT: performance_schema.data_locks showed only a TABLE IX lock after the INSERT, and a RECORD X lock on the "supremum pseudo-record" after the rollback. After an INSERT the transaction holds an implicit exclusive lock on the new record, not shown in data_locks until a conflict occurs. When the rollback's undo removes the record, the lock is not released but is inherited by the next gap, which for a record at the end of the index is the supremum. The MySQL 8.0 code calls this inheritance explicitly through row_undo_ins_remove_clust_rec() -> btr_cur_pessimistic_delete() -> lock_update_delete() -> lock_rec_inherit_to_gap().

Rollback to a savepoint was being used inside freee without developers knowing it: Rails Active Record uses SAVEPOINT when ActiveRecord::Base.transaction is given requires_new: true, and #create_or_find_by and #find_or_create_by call ActiveRecord::Base.transaction(requires_new: true) internally. The supremum lock alone causes no trouble; together with a long transaction it took the whole web service down.

Severity
–
Scope
A whole freee web service was down: its web pages could not be opened
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

Connections to Aurora MySQL failed again and again in one freee service until its web pages could not be opened at all. The database did not respond, the web application timed out and could not open new database connections, and the whole service was down.

On the Aurora MySQL writer instance, CPUUtilization and FreeableMemory showed nothing abnormal and the instance was not under heavy load. BlockedTransactions, the number of transactions waiting on locks, rose sharply, and DatabaseConnections rose sharply until it reached its limit.

Database Insights showed that around the time of the failure INSERTs into one table were waiting on wait/synch/cond/innodb/row_lock_wait, so new rows could not be added to it. This looked odd, because the table's primary key is an AUTO_INCREMENT value and an INSERT of a new row would not normally be blocked by a gap lock. The SHOW ENGINE INNODB STATUS output showed a long transaction holding lock_mode X on the PRIMARY index record "asc supremum;;".

Symptom Lock contention or deadlockService outage

Root cause

Root cause Software defect

Component Database
Contributing factor Unverified assumptionUnreviewed defaults
Trigger
A long transaction inserted a row into the table after a SAVEPOINT and then ran ROLLBACK TO SAVEPOINT, which left it holding an exclusive lock on the supremum of the table's primary index.

Detection

–

Remediation

–

Action items

Lessons

ROLLBACK TO SAVEPOINT rolls back rows but does not release their locks, so rolling back one inserted row can leave a supremum lock that blocks every INSERT into the table. ORM frameworks, including Active Record for nested transactions, use SAVEPOINT internally without developers noticing. Locks held by long transactions often go unnoticed when few workloads are blocked, and this incident showed their hidden risk.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.