<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="mysql-acid">
      </a>
      17.2 InnoDB and the ACID Model
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167296288">
  </a>
  <p>
   The
   <a class="link" href="glossary.html#glos_acid" title="ACID">
    ACID
   </a>
   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
   <code class="literal">
    InnoDB
   </code>
   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.
  </p>
  <p>
   The following sections discuss how MySQL features, in particular the
   <code class="literal">
    InnoDB
   </code>
   storage engine, interact with the
    categories of the ACID model:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <span class="bold">
       <strong>
        A
       </strong>
      </span>
      : atomicity.
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="bold">
       <strong>
        C
       </strong>
      </span>
      : consistency.
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="bold">
       <strong>
        I:
       </strong>
      </span>
      : isolation.
     </p>
    </li>
    <li class="listitem">
     <p>
      <span class="bold">
       <strong>
        D
       </strong>
      </span>
      : durability.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045167284784">
   </a>
   Atomicity
  </h3>
  <p>
   The
   <span class="bold">
    <strong>
     atomicity
    </strong>
   </span>
   aspect of the ACID
    model mainly involves
   <code class="literal">
    InnoDB
   </code>
   <a class="link" href="glossary.html#glos_transaction" title="transaction">
    transactions
   </a>
   . Related MySQL
    features include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      setting.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        ROLLBACK
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045167274880">
   </a>
   Consistency
  </h3>
  <p>
   The
   <span class="bold">
    <strong>
     consistency
    </strong>
   </span>
   aspect of the ACID
    model mainly involves internal
   <code class="literal">
    InnoDB
   </code>
   processing
    to protect data from crashes. Related MySQL features include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       InnoDB
      </code>
      doublewrite buffer. See
      <a class="xref" href="innodb-doublewrite-buffer.html" title="17.6.4 Doublewrite Buffer">
       Section 17.6.4, “Doublewrite Buffer”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      crash recovery. See
      <a class="xref" href="innodb-recovery.html#innodb-crash-recovery" title="InnoDB Crash Recovery">
       InnoDB Crash Recovery
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045167267264">
   </a>
   Isolation
  </h3>
  <p>
   The
   <span class="bold">
    <strong>
     isolation
    </strong>
   </span>
   aspect of the ACID
    model mainly involves
   <code class="literal">
    InnoDB
   </code>
   <a class="link" href="glossary.html#glos_transaction" title="transaction">
    transactions
   </a>
   , in particular
    the
   <a class="link" href="glossary.html#glos_isolation_level" title="isolation level">
    isolation level
   </a>
   that
    applies to each transaction. Related MySQL features include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      setting.
     </p>
    </li>
    <li class="listitem">
     <p>
      Transaction isolation levels and the
      <a class="link" href="set-transaction.html" title="15.3.7 SET TRANSACTION Statement">
       <code class="literal">
        SET
        TRANSACTION
       </code>
      </a>
      statement. See
      <a class="xref" href="innodb-transaction-isolation-levels.html" title="17.7.2.1 Transaction Isolation Levels">
       Section 17.7.2.1, “Transaction Isolation Levels”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The low-level details of
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_locking" title="locking">
       locking
      </a>
      . Details can be
        viewed in the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      tables (see
      <a class="xref" href="innodb-information-schema-transactions.html" title="17.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information">
       Section 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”
      </a>
      ) and
        Performance Schema
      <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
       <code class="literal">
        data_locks
       </code>
      </a>
      and
      <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
       <code class="literal">
        data_lock_waits
       </code>
      </a>
      tables.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045167250896">
   </a>
   Durability
  </h3>
  <p>
   The
   <span class="bold">
    <strong>
     durability
    </strong>
   </span>
   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
   <span class="quote">
    “
    <span class="quote">
     buy new hardware
    </span>
    ”
   </span>
   .) Related MySQL features include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       InnoDB
      </code>
      doublewrite buffer. See
      <a class="xref" href="innodb-doublewrite-buffer.html" title="17.6.4 Doublewrite Buffer">
       Section 17.6.4, “Doublewrite Buffer”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit">
       <code class="literal">
        innodb_flush_log_at_trx_commit
       </code>
      </a>
      variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
       <code class="literal">
        sync_binlog
       </code>
      </a>
      variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      The write buffer in a storage device, such as a disk drive, SSD,
        or RAID array.
     </p>
    </li>
    <li class="listitem">
     <p>
      A battery-backed cache in a storage device.
     </p>
    </li>
    <li class="listitem">
     <p>
      The operating system used to run MySQL, in particular its
        support for the
      <code class="literal">
       fsync()
      </code>
      system call.
     </p>
    </li>
    <li class="listitem">
     <p>
      An uninterruptible power supply (UPS) protecting the electrical
        power to all computer servers and storage devices that run MySQL
        servers and store MySQL data.
     </p>
    </li>
    <li class="listitem">
     <p>
      Your backup strategy, such as frequency and types of backups,
        and backup retention periods.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
