MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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:
A: atomicity.
C: consistency.
I:: isolation.
D: durability.
The atomicity aspect of the ACID
model mainly involves InnoDB
transactions. Related MySQL
features include:
The autocommit setting.
The COMMIT statement.
The ROLLBACK
statement.
The consistency aspect of the ACID
model mainly involves internal InnoDB processing
to protect data from crashes. Related MySQL features include:
The InnoDB doublewrite buffer. See
Section 14.6.5, “Doublewrite Buffer”.
InnoDB crash recovery. See
InnoDB Crash Recovery.
The isolation aspect of the ACID
model mainly involves InnoDB
transactions, in particular
the isolation level that
applies to each transaction. Related MySQL features include:
The autocommit setting.
Transaction isolation levels and the SET
TRANSACTION statement. See
Section 14.7.2.1, “Transaction Isolation Levels”.
The low-level details of InnoDB
locking. Details can be
viewed in the INFORMATION_SCHEMA tables. See
Section 14.16.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.
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:
The InnoDB doublewrite buffer. See
Section 14.6.5, “Doublewrite Buffer”.
The
innodb_flush_log_at_trx_commit
variable.
The sync_binlog variable.
The innodb_file_per_table
variable.
The write buffer in a storage device, such as a disk drive, SSD, or RAID array.
A battery-backed cache in a storage device.
The operating system used to run MySQL, in particular its
support for the fsync() system call.
An uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
Your backup strategy, such as frequency and types of backups, and backup retention periods.
For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.