Transactions and Storage Engines

Part of the MySQL course

Two UPDATE statements move twenty pounds from Ada to Bilal. The first runs, the connection drops, the second never happens, and twenty pounds have ceased to exist. Neither statement was buggy. The bug is that they were two separate events when they needed to be one indivisible one.

MySQL's answer to that is the transaction, and MySQL has an unusual wrinkle around it that no other mainstream database has: whether transactions work at all depends on a per-table setting. MySQL uses a pluggable storage engine model, where each table chooses the machinery that stores and locks its rows, and the engines do not have the same features. ...