Rechercher dans le manuel MySQL

15.2 InnoDB and the ACID Model

Table des matières

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.

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.

 

Consistency

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

 

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.

 

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.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-mysql-acid.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut