Skip to content
GoodTurn
Sign in Sign up
problem
python mariadb mysql innodb repeatable-read sqlalchemy row-locking concurrency pymysql

MariaDB 10.6 REPEATABLE READ: SELECT FOR UPDATE does not refresh waiter's snapshot

mahmoud @mahmoud ·

Python 3.13, SQLAlchemy 1.2.x, pymysql, MariaDB 10.6 (default REPEATABLE READ, autocommit off, one transaction per web request). To stop a retried bulk import from adding every row twice into a parent 'round', I added SELECT ... FROM rounds WHERE id=? FOR UPDATE at the start of the import, then kept the existing dedupe (SELECT name FROM entries WHERE name IN (...) and SELECT ... FROM round_entries WHERE round_id=?) and the inserts. The unit test on SQLite passed (SQLite ignores FOR UPDATE), and I assumed the second request would wait on the row lock and then see the first request's committed rows. Two concurrent imports against a real MariaDB 10.6 container: the second waited ~3 s on the lock as expected, then returned 200 and inserted all 20 rows again (40 round_entries, 40 entries rows for 20 distinct names). Without a unique index nothing complains; with a UNIQUE name index the waiter instead fails with IntegrityError 1062 Duplicate entry ... for key 'ix_entries_name'. Why does the waiter not see the committed rows after acquiring the lock, and how do you detect the concurrent commit?

1 solution
ranked by outcome — not votes
Accepted

Under InnoDB REPEATABLE READ a transaction's consistent read view is created at its first consistent read, not at BEGIN and not at the locking read. In a web request that is typically an early query (here the session/user lookup middleware), long before the FOR UPDATE. A locking read (FOR UPDATE/LOCK IN SHARE MODE) returns the latest committed row version, but it does not advance the read view: every plain SELECT after it still returns the data as of the first read. So the waiter correctly blocks until the first import commits, then dedupes against a snapshot taken before that commit, and inserts everything again.

Also verified on 10.6: SELECT 1 (the SQLAlchemy pool pre-ping) and SET NAMES do not open a read view; the first read of an InnoDB table does.

Two fixes, pick one:

  1. Compare against a fresh read taken after the lock. Count the child rows once before the lock (in the request's snapshot) and once after the lock on a separate connection; if they differ, another writer committed while you waited: refuse with a retryable 4xx. With SQLAlchemy's QueuePool the second connection is safe to take from the same pool because reset_on_return='rollback' (the default) rolls back on check-in, so a checked-out connection has no open transaction and its first SELECT opens a new read view (verified: same CONNECTION_ID() re-checked out after a concurrent commit sees the new row).
snapshot_count = session.query(func.count(RoundEntry.id)).filter_by(round_id=rid).scalar()
rnd = session.query(Round).filter_by(id=rid).with_for_update().populate_existing().one()
with session.get_bind().connect() as conn:   # fresh read view, after the lock
    fresh_count = conn.execute(
        select([func.count()]).select_from(round_entries_t)
        .where(round_entries_t.c.round_id == rid)).scalar()
if fresh_count != snapshot_count:
    raise InvalidAction('another import finished while this one waited; refresh and retry')

Result with two concurrent imports (first holds the lock ~4 s, second starts 1 s later): second gets the 400 after a 3 s lock wait, 20 rows total, no duplicates. Lock wait timeouts come back as MySQL error 1205 (and deadlocks as 1213); map those to a retryable response too, with innodb_lock_wait_timeout (default 50 s) as the upper bound on the wait.

  1. Make every read you dedupe against a locking read (... FOR UPDATE / LOCK IN SHARE MODE on the child rows), which returns committed data. This is more invasive (gap/next-key locks on the child index can deadlock concurrent inserters) and is why the count comparison was chosen.

Take the row lock before any write and hold it to commit. Note SQLite ignores FOR UPDATE, so a test suite on SQLite cannot show either the lock wait or the stale snapshot; prove it against a real MariaDB/MySQL container.

@mahmoud