ORM pitfalls
Inspect the SQL and transaction boundaries your ORM actually uses. This lesson executes SQL on MySQL 8.4 InnoDB, not a framework or migration library. It does not establish how every ORM configures transactions, locking, or retries.
Pitfall #1: DDL in a "transaction" is the migration lie
A transaction wrapper cannot make the demonstrated INSERT plus CREATE INDEX atomic. The implicit-commit manual lists statements that end an active transaction "as if you had done a COMMIT before executing the statement." CREATE INDEX is on that list.
A migration script wraps its work in a transaction, believing that makes it atomic: insert data, add an index, and if anything fails — roll back.
A> BEGIN;
Query OK
A> INSERT INTO orders VALUES (1, 'draft');
Query OK, 1 row affected
B> SELECT count(*) AS visible FROM orders; -- uncommitted, invisible — so far so good
visible
---------
0
(1 row)
A> CREATE INDEX idx_orders_state ON orders (state); -- DDL — and an implicit COMMIT, right here
Query OK
B> SELECT count(*) AS visible FROM orders; -- A never said COMMIT. The CREATE INDEX did.
visible
---------
1
(1 row)A now explicitly rolls back. No migration exception is injected.
A> ROLLBACK;
Query OK
B> SELECT count(*) AS visible FROM orders; -- rolled back nothing — the INSERT was already committed
visible
---------
1
(1 row)CREATE INDEX has already committed the INSERT. Check the manual's implicit-commit list and exceptions before relying on a migration wrapper; this schedule does not execute every DDL statement.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
B sees the inserted order after CREATE INDEX and still sees it after A's ROLLBACK. The scenario executes a successful CREATE INDEX followed by explicit rollback; it does not inject a migration exception. Design recovery around these commit boundaries and make completed steps safe to inspect and resume.
The same manual section documents exceptions: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE do not implicitly commit, but their effects cannot be rolled back. Check the actual statement; "every DDL commits" is too broad. Atomic execution of a supported DDL statement is also distinct from rolling back a user transaction containing DDL and data changes.
Pitfall #2: the transaction that outlives the query
Waiting for an API or rendering inside an open transaction can extend the lifetime of locks and read views already held. The relevant database behaviors are demonstrated in row locks, metadata-lock waits, and retained undo history. Not every transaction holds all three. Keep the protected database work short; moving a remote effect outside it still requires an explicit failure-boundary design.
Pitfall #3: no transaction where you assumed one
If a load and save use separate transactions, or a plain read is followed by a stale absolute write, the SQL can reproduce the lost-update schedule. Check whether your ORM exposes arithmetic updates, a locking read in the same transaction, or a version predicate. The three repair schedules demonstrate those SQL protocols; their presence in an ORM must be checked separately.
Pitfall #4: trusting default isolation
The MySQL 8.4 manual states: "The default isolation level for InnoDB is REPEATABLE READ." A configured server, connection, or ORM can change it. The REPEATABLE READ lesson separates snapshot reads from locking reads and writes. That level does not reject the demonstrated stale overwrite. SERIALIZABLE uses more locking and can produce a deadlock; it does not remove the need to handle errors and retry complete transactions where appropriate. Verify the ORM's recovery behavior rather than assuming it reruns business logic.