ORM pitfalls
ORMs are fine. What bites is the transaction machinery they hide. The pitfalls from the PostgreSQL lesson — transactions held open across slow I/O, read-modify-write without protection, blind trust in default isolation — all apply on MySQL, two of them with worse defaults. And MySQL adds a fourth of its own: your migration tool's "transactional" migrations aren't.
Pitfall #1: DDL in a "transaction" — the migration lie
Every serious framework wraps migrations in a transaction, and on PostgreSQL that means a failed migration rolls back cleanly — schema and data. On MySQL the wrapper is decorative. The manual: DDL statements "implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement."
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)The script now hits an error and rolls back, trusting the transaction to clean up:
A> ROLLBACK;
Query OK
B> SELECT count(*) AS visible FROM orders; -- rolled back nothing — the INSERT was already committed
visible
---------
1
(1 row)There is no transactional DDL in MySQL. A migration that mixes data and schema changes has commit points at every DDL statement — design for re-runnability instead of atomicity.
Verified against MySQL 8.4.10 · Run it yourself · Scenario source
A migration that inserts data, alters a table, then fails leaves the database in a state no version of your code describes — half-migrated, permanently. Write MySQL migrations to be re-runnable (idempotent steps, one DDL per migration) instead of assuming atomicity the engine doesn't offer.
Pitfall #2: the transaction that outlives the query
Transaction-per-request middleware opens a transaction; the handler then awaits a payment API or renders a template. The database sees a session idle in transaction — holding row locks, blocking DDL via metadata locks, and pinning undo history — while your code isn't talking to it at all. MySQL makes this one harder to survive than PostgreSQL: there is no idle_in_transaction_session_timeout to kill the offender — the production chapter shows how to find the culprits yourself. The real fix is upstream: no network I/O inside a transaction, ever.
Pitfall #3: no transaction where you assumed one
Without an explicit block, most ORMs run each save() as its own autocommitted statement — so load entity, change field, save is exactly chapter 2's read-modify-write lost update, writing back every stale field the object was loaded with. The fixes are the previous lesson's; ORMs ship two of them as "optimistic locking" (version column) and a lock/forUpdate query option. They only work if you turn them on — and on MySQL you can't lean on REPEATABLE READ to catch what you missed, as PostgreSQL's would.
Pitfall #4: trusting default isolation
An ORM transaction block gives you REPEATABLE READ — MySQL's default — which sounds stronger than PostgreSQL's READ COMMITTED and is, for plain reads. But every write in it is a current read, lost updates pass silently, and there's no 40001 to tell you the snapshot betrayed you. If a unit of work needs SERIALIZABLE, say so per-transaction — and then own the 1213 retry loop, because the ORM won't rerun your business logic for you.
Four habits keep these from biting. MySQL has no transactional DDL, so every migration step that touches the schema commits everything before it — design migrations to re-run, never to roll back. An ORM transaction stays open from its first statement until your code returns, and with no idle-in-transaction timeout to save you, keeping network I/O out of it falls to you. Object-style read-modify-write is a lost update by default until you turn on a version column or a locking read, and the isolation level and the 1213 retry loop are your job, not the ORM's.