InnoDB and the ACID Model
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.
Atomicity
The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include:
-
The
autocommitsetting. -
The
COMMITstatement. -
The
ROLLBACKstatement.
Consistency
The consistency aspect of the ACID model mainly involves internal InnoDB processing to protect data from crashes. Related MySQL features include:
-
The
InnoDBdoublewrite buffer. See Section 14.6.5, “Doublewrite Buffer”. -
InnoDBcrash recovery. See InnoDB Crash Recovery.
Isolation
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
autocommitsetting. -
Transaction isolation levels and the
SET TRANSACTIONstatement. See Section 14.7.2.1, “Transaction Isolation Levels”. -
The low-level details of
InnoDBlocking. Details can be viewed in theINFORMATION_SCHEMAtables. See Section 14.16.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.
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:
-
The
InnoDBdoublewrite buffer. See Section 14.6.5, “Doublewrite Buffer”. -
The
innodb_flush_log_at_trx_commitvariable. -
The
sync_binlogvariable. -
The
innodb_file_per_tablevariable. -
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.
- 点赞
- 收藏
- 关注作者
评论（0）