MySQL 5.5 made InnoDB the default engine, which is often read as “we are on 5.5, so we have transactions”. Tables created under 5.1 keep MyISAM through an in-place upgrade, and a schema that has been alive for a few years is almost always mixed. START TRANSACTION against a MyISAM table does not error; it simply has no effect, and the rollback reports a warning nobody is reading.
SELECT table_name, engine, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY engine, table_name;
-- orders | InnoDB | 412883
-- stock | MyISAM | 9140
START TRANSACTION;
UPDATE stock SET qty = qty - 1 WHERE sku = 'FR-100';
UPDATE orders SET status = 'paid' WHERE id = 91;
ROLLBACK;
-- Warning 1196: Some non-transactional changed tables couldn't be rolled back
ALTER TABLE stock ENGINE = InnoDB;What that rollback leaves behind is the worst outcome on the menu: the order is unpaid and the stock has still been decremented. Half a transaction is worse than none, because none at least fails visibly. Converting is a single ALTER TABLE, which rebuilds the table and holds a lock for the whole rebuild, so on anything large it is a maintenance window rather than an afternoon. Two things change with the engine and both need checking first. MyISAM’s FULLTEXT index has no InnoDB equivalent in 5.5, so a search built on MATCH ... AGAINST has to move somewhere else before the table can convert. And InnoDB keeps no row count, so SELECT COUNT(*) stops being instant and becomes an index scan — which surprises whoever wrote the dashboard. The compensation is that locking drops from table level to row level, so write concurrency usually improves as a side effect nobody planned for.