15.2 InnoDB and the ACID Model

The ACID model is a set of database
design principles that emphasize aspects of reliability that are
important for business data and mission-critical applications. MySQL
includes components such as the InnoDB storage
engine that adhere closely to the ACID model, so that data is not
corrupted and results are not distorted by exceptional conditions
such as software crashes and hardware malfunctions. When you rely on
ACID-compliant features, you do not need to reinvent the wheel of
consistency checking and crash recovery mechanisms. In cases where
you have additional software safeguards, ultra-reliable hardware, or
an application that can tolerate a small amount of data loss or
inconsistency, you can adjust MySQL settings to trade some of the
ACID reliability for greater performance or throughput.

The following sections discuss how MySQL features, in particular the
InnoDB storage engine, interact with the
categories of the ACID model:

The low-level details of InnoDBlocking. During performance
tuning, you see these details through
INFORMATION_SCHEMA tables.

Durability

The durability aspect of the ACID
model involves MySQL software features interacting with your
particular hardware configuration. Because of the many possibilities
depending on the capabilities of your CPU, network, and storage
devices, this aspect is the most complicated to provide concrete
guidelines for. (And those guidelines might take the form of buy
“new hardware”.) Related MySQL features include:

D - not so much. DDL in particular is not recoverable in the face of server crash. it is very possible to create half-open tables where innodb and the filesystem are inconsistent with each other, and it is impossible to repair without a complete dump/reload cycle. The most obvious way to fix this is to have a mysql-level intent log, with a two-phase commit with the innodb. Not trivial.