iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A MySQL ROLLBACK can undo changes to an InnoDB table while leaving a write to a MyISAM table in place. The reason is that rollback applies to transactional storage engines, not automatically to every table touched by an application transaction. In a 2026 account, Paulo Antunes described exactly this split during a release operation in a legacy Delphi ERP: “The rollback ran. No error. And half of it stayed.” (Paulo Antunes’s account)
Why did ROLLBACK leave data behind?
In Antunes’s incident, a production ERP screen released material from a returned box by updating an item table and a volume table. The item table used InnoDB; the volume table used MyISAM. After the application issued ROLLBACK, the item-table change was undone, but the volume-table write remained. The incident details are Antunes’s account, not a general statistic about MySQL.
This follows from the engines’ different guarantees. MySQL 8.4 documents InnoDB as transaction-safe, with commit, rollback, and crash-recovery capabilities; MyISAM has no transaction support. A MySQL schema can use different storage engines table by table, so a transaction that touches both does not make their writes atomic as a group. (MySQL 8.4: Storage Engines; MySQL 8.4: InnoDB and the ACID Model)
Recommended Free Tools
A transaction wrapper, ORM, or successful row count does not change a table’s engine semantics. The important question is not simply “Is this atomic?” It is: which tables participate, what engine backs each table, and what happens if execution stops after one write but before another?
#1 Best Overall
How to check the tables an operation actually writes
Inspect engine metadata in the live schema for every table on the operation’s write path. Antunes found that his schema export described 342 tables and 6,477 columns but omitted engine metadata; he used information_schema.TABLES to check the live database. Those counts describe the repository snapshot in his 2026 account, not MySQL generally.
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME IN ('item_table', 'volume_table');Replace the schema and table names with the actual database and tables used by the operation. Confirm the results against the deployed database rather than assuming an export or application model reflects the live engine configuration. If the operation can update additional tables through triggers or other code paths, include those writes in the audit too.
Rank #2
How to design a workflow that crosses the engine boundary
Antunes’s approach for this particular release operation was to accept that the mixed-engine workflow could not be made atomic, then design its validation, ordering, and retry behavior around a chosen recoverable failure state. That ordering is not a universal recipe: the safer sequence depends on which action is reversible and which partial result is least harmful in the specific system.
- Validate before the first write. Check every affected row and every condition needed for the release before changing anything. An invalid row should block the operation before it begins mutating either table.
- Repeat safety conditions in each update. Put applicable conditions in each update’s
WHEREclause as well as in preflight validation. This reduces the chance that the database changes between validation and mutation and helps prevent an update from applying to a row that no longer qualifies. - Choose write order based on the failure state. In Antunes’s example, the code commits the reversible InnoDB change first and then performs the MyISAM write. If execution stops between those operations, that order intentionally determines what remains; it does not restore atomicity. Antunes considered released item codes with a stale volume pointer easier to recover from than a volume that appeared released while its item code still blocked reuse.
- Make the interrupted operation safe to retry. The described updates set fields to
NULLonly while their relevant conditions still hold, allowing a second run to complete an interrupted operation rather than blindly repeating an unsafe mutation.
Think of the chosen partial result as a failure-state design decision. Identify what an operator or user will see, how to detect an incomplete operation, and how to finish or compensate for it. Ordering improves recoverability only when paired with clear detection and a safe recovery path.
What changes when every table is transactional?
When all writes in a set use InnoDB and participate in the same transaction, that set can use one transaction for commit or rollback. Antunes says a separate quality-check routine in his ERP touches only InnoDB tables and can use a single transaction for the set. This does not change the mixed-engine behavior of his release routine; each operation must be assessed using the tables it actually touches.
Antunes also reports that changing MyISAM index structures in his legacy system could require a table rebuild and lock. That is a system-specific operational cost to weigh when considering an engine migration; it is not a universal estimate of migration effort.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Does the same problem arise outside MySQL?
Yes, similar recovery boundaries appear when one request changes separate systems—for example, a database and an object store, a database and a message queue, or a payment API and a local record. Those systems do not share a database transaction merely because application code calls them in sequence. Design around the real boundary: choose ordering deliberately, define how partial completion is detected and recovered or compensated, and make retries safe for the particular systems involved.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
