14.2 InnoDB and the ACID Model

14.2 InnoDB和ACID模型

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.

ACID模型是一组数据库设计原则, 强调了对于业务数据和关键应用至关重要的可靠性。 MySQL包含了紧密配合ACID模型的InnoDB存储引擎等组件, 因此数据不会损坏, 结果也不会因软件崩溃和硬件故障等异常情况而失真。 当您依靠符合ACID的功能时, 无需自己解决一致性检查和崩溃恢复机制。如果有其他软件保护措施, 超可靠的硬件或可以容忍少量数据丢失或不一致的应用, 则可以调整MySQL设置, 以牺牲一些ACID可靠性来获得更高的性能或吞吐量。

下面讨论MySQL功能, 主要是InnoDB存储引擎与ACID模型的类别如何进行交互:

• A: atomicity, 原子性
• C: consistency, 一致性
• I: isolation, 隔离性
• D: durability, 持久性

MySQL Glossary - ACID

An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.

The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)

See Also: atomic, commit, concurrency, doublewrite buffer, isolation level, locking, rollback, transaction.

术语介绍 - ACID

ACID 代表 atomicity, consistency, isolation, 以及 durability 的首字母缩写。这些属性都是数据库系统所需要的, 并且都与事务(transaction)的概念紧密相关。 InnoDB的事务功能遵循ACID原则。

事务是可以“提交”或“回滚”的“原子”工作单元。 当事务对数据库进行多次更改时, 要么在提交事务后所有更改成功, 要么在回滚事务后撤消所有更改。

在每次“提交”或“回滚”之后以及事务正在进行时, 数据库始终保持一致状态。如果正在跨多个表更新相关数据, 则查询将看到所有旧值或所有新值, 而不是新旧值的混合。

事务在进行过程中相互保护（隔离）；他们不能互相干扰, 也不能看到彼此的未提交数据。这种隔离是通过“锁定”机制实现的。有经验的用户可以确定事务确实不会相互干扰时, 可以调整“隔离级别”, 以牺牲较少的保护为代价来提高性能和并发性。

事务的结果是持久的: 提交操作成功后, 该事务所做的更改就不会出现电源故障, 系统崩溃, 竞争状况或许多非数据库应用程序容易受到的其他潜在危险。耐用性通常涉及写入磁盘存储, 并具有一定数量的冗余以防止写入操作期间出现电源故障或软件崩溃。 （在InnoDB中, doublewrite buffer 有助于持久性。）

另请参见: “原子”, “提交”, “并发”, “双写缓冲区”, “隔离级别”, “锁定”, “回滚”, “交易”。

Atomicity

The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include:

• autocommit setting.
• COMMIT statement.
• ROLLBACK statement.
• Operational data from the INFORMATION_SCHEMA tables.

原子性

ACID模型的 atomicity 方面主要涉及 InnoDB 事务。 相关的MySQL功能包括:

• 自动提交设置(autocommit)。
• COMMIT语句。
• ROLLBACK语句。
• INFORMATION_SCHEMA 的数据表的操作数据。

Consistency

The consistency aspect of the ACID model mainly involves internal InnoDB processing to protect data from crashes. Related MySQL features include:

• InnoDB doublewrite buffer.
• InnoDB crash recovery.

一致性

ACID模型的 consistency 方面主要涉及 InnoDB 内部处理, 以保护数据免于崩溃。 相关的MySQL功能包括:

• InnoDB 双写缓冲区。
• InnoDB 崩溃恢复。

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:

• Autocommit setting.
• SET ISOLATION LEVEL statement.
• The low-level details of InnoDB locking. During performance tuning, you see these details through INFORMATION_SCHEMA tables.

隔离性

ACID模型的 isolation 方面主要涉及 InnoDB事务, 尤其是适用于每个事务的隔离级别。 相关的MySQL功能包括:

• 自动提交设置。
• SET ISOLATION LEVEL语句。
• InnoDB锁定的底层细节。 在性能调整期间, 可以通过INFORMATION_SCHEMA中的表查看这些详细信息。

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:

• InnoDB doublewrite buffer, turned on and off by the innodb_doublewrite configuration option.
• Configuration option innodb_flush_log_at_trx_commit.
• Configuration option sync_binlog.
• Configuration option innodb_file_per_table.
• Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
• Battery-backed cache in a storage device.
• The operating system used to run MySQL, in particular its support for the fsync() system call.
• 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.

持久性

ACID模型的 durability 方面涉及与特定硬件配置交互的MySQL软件功能。 由于取决于您的CPU, 网络和存储设备的功能的可能性很多, 因此为具体的准则提供最复杂的方面。 （这些准则可能采取购买“新硬件”的形式。）相关的MySQL功能包括:

• InnoDB doublewrite缓冲区, 由 innodb_doublewrite 配置选项打开和关闭。
• 配置选项 innodb_flush_log_at_trx_commit。
• 配置选项 sync_binlog。
• 配置选项 innodb_file_per_table。
• 在存储设备中的写入缓冲区（例如磁盘驱动器, SSD或RAID阵列）。
• 电池支持的存储设备中的缓存。
• 用于运行MySQL的操作系统, 特别是它对 fsync() 系统调用的支持。
• 不间断电源（UPS）保护运行MySQL服务器并存储MySQL数据的所有计算机服务器和存储设备的电源。
• 您的备份策略, 例如备份的频率和类型以及备份保留期。
• 对于分布式或托管数据应用程序, MySQL服务器的硬件所在的数据中心的特殊特性, 以及数据中心之间的网络连接。

原文链接

• 14.2 InnoDB and the ACID Model
• MySQL Glossary - ACID