I was testing a new screen against the production ERP database: a box comes back from the customer, and the screen has to release the material inside it so it can be scanned into a new order. Two writes, one transaction, and a ROLLBACK at the end so the rehearsal wouldn't leave a trace.
The rollback ran. No error. And half of it stayed.
The item table was back to its old state. The volume table was not — the write had persisted, in a transaction I had just explicitly undone.
The database is not one database
The schema is old. It has grown for years around a Delphi ERP that runs a real factory, and over that time it has been touched by people with different defaults. Some tables were created in the MyISAM era. The newer ones are InnoDB.
MyISAM has no transactions. An UPDATE against a MyISAM table is applied the moment it runs. Wrapping it in BEGIN doesn't do anything, COMMIT doesn't do anything, and ROLLBACK doesn't do anything either. It does not fail, and it does not warn you. The transaction simply doesn't include that table.
So in a routine that writes to both families, there is no "all or nothing". There are two independent writes, one of which is a point of no return, and the illusion that you have a transaction around them.
The field that decides is not in the document I trust
This is the part that still bothers me.
I keep an exported snapshot of the schema in the repository — JSON, 342 tables, 6,477 columns, every index. I use it constantly, and it's the file my CLAUDE.md tells the AI agent to consult before it writes any SQL, precisely so nobody invents a column name from memory.
It has tables. It has columns. It has indexes. It does not have the storage engine.
$ grep -ic engine docs/db/schema.json
0
The artifact I treat as the source of truth about the database is silent about the one property that determines whether my transaction is real. Nothing in the application layer fills that gap: SQLAlchemy commits happily, the driver reports success, the row count comes back correct. The only thing that tells you is asking the server directly.
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY ENGINE, TABLE_NAME;
Run it on any schema old enough to have outlived a MySQL version upgrade. It takes ten seconds, and the answer can be a surprise.
The four rules I write with now
You can't get atomicity back. MyISAM tables in a running ERP are not something you convert on a Tuesday afternoon — the Delphi application writes to them, some are big, and the conversion is a separate project with its own risk. So the routine has to be correct without a transaction.
1. Validate everything before writing anything.
The check that decides whether the operation is allowed runs first, over all affected rows, and it's all-or-nothing on its own. A box can hold volumes from more than one order; if any of them fails the rule, nothing is released — and the screen shows exactly which row blocked it. Every guard is also repeated in the WHERE of both UPDATEs, so the write can't drift from the check.
2. Order the writes: reversible first, irreversible last.
This is the whole trick. Within the pair of writes, one table can be rolled back and the other can't. So the InnoDB write goes first, commits, and only then does the MyISAM write run.
# 1) items (InnoDB): reversible. Frees the unique code that blocks re-scanning.
try:
items = db.session.execute(text(SQL_RELEASE_ITEMS), params).rowcount
db.session.commit()
except Exception:
db.session.rollback()
raise
# 2) volume (MyISAM): NOT reversible. Runs only after the first one is committed.
try:
volumes = db.session.execute(text(SQL_RELEASE_VOLUMES), params).rowcount
db.session.commit()
except Exception:
db.session.rollback() # honest no-op here, kept for symmetry
raise
Two blocks instead of one is not a style choice. It's the shape of the guarantee.
3. Choose which half-finished state you want to be left with.
Ordering the writes means deciding, in advance, what the world looks like if the process dies between them. Here it's "item codes are free, volume still points at the box". That's the benign side: the thing that actually blocks reuse is a unique index on the item code, and it's already released. The leftover is a stale pointer, visible and fixable. The other order would have left the operator with a box that looks released but still refuses every scan — a support call nobody can diagnose.
Pick the failure state the same way you'd pick an error message: for the person who will meet it.
4. Make re-running it safe.
Since the operation can stop halfway, the fix has to be "do it again". Both statements are idempotent — they set values to NULL under conditions that are still true — so running the screen a second time completes what the crash left behind, instead of doing damage on top of it.
It isn't one weird table
I thought I'd found a quirk. I'd found a property of the schema.
A completely different routine — the finishing line, where an operator types a work order and the system pulls the next item from the production queue — has the same shape: the queue table is InnoDB and takes a real SELECT ... FOR UPDATE lock, while the stock-entry table is MyISAM, so the rollback doesn't undo the stock entry. Same reasoning, same ordering decision: the stock stays right and the traceability lags, which is the safe direction of the error.
And a third routine, a quality checkpoint, writes to three tables that all happen to be InnoDB — so a single commit genuinely covers the set, and I wrote it as one block. That's the point: the answer is per-table, every time. "Is this atomic?" has no schema-wide answer here.
The engine changes the deployment story too. Adding an index to a MyISAM table rebuilds and locks the whole table, so that migration is scheduled outside scanning hours — the same ALTER on InnoDB would have been routine.
If you don't have a legacy MySQL schema
You probably still have this problem.
Write to Postgres and S3 in the same request, and there's no transaction around the pair. Insert a row and publish to a queue — same thing. Call a payment API and update your own record — same thing. Every one of those is "two stores, no shared transaction", and the four rules transfer without modification: validate first, order reversible before irreversible, choose the benign half-state on purpose, make retry safe.
The mixed storage engines just make it impossible to pretend otherwise. ROLLBACK looks like it's protecting you right up until you check.
Which is why my rehearsals now start with a query against information_schema, and why the next thing I want to add to that schema export is one more column.
