Cases1 case

1 to 1 of 1

1. IL-0436 A supremum lock left by ROLLBACK TO SAVEPOINT in a long transaction blocked inserts and filled Aurora MySQL connections

   freee2025

   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.

   Lock contention or deadlockService outage

   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.

   Root cause

   Software defect

   Component

   Database

   Contributing factor

   Unverified assumptionUnreviewed defaults

   Action items

   • As a rule stop issuing SAVEPOINT, split a transaction when a partial rollback is needed, and set an application-side timeout where a nested transaction is unavoidable
   • Teach developers to consider READ COMMITTED per session for use cases that can accept phantom rows
   • Monitor long transactions and keep working to remove them across the company

   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.

   View case

By root cause1