<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-transaction-isolation-levels">
      </a>
      17.7.2.1 Transaction Isolation Levels
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045164403328">
  </a>
  <a class="indexterm" name="idm46045164401936">
  </a>
  <p>
   Transaction isolation is one of the foundations of database
        processing. Isolation is the I in the acronym
   <a class="link" href="glossary.html#glos_acid" title="ACID">
    ACID
   </a>
   ; the isolation level is
        the setting that fine-tunes the balance between performance and
        reliability, consistency, and reproducibility of results when
        multiple transactions are making changes and performing queries
        at the same time.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   offers all four transaction isolation
        levels described by the SQL:1992 standard:
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-uncommitted">
    <code class="literal">
     READ UNCOMMITTED
    </code>
   </a>
   ,
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ COMMITTED
    </code>
   </a>
   ,
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   , and
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
    <code class="literal">
     SERIALIZABLE
    </code>
   </a>
   . The default
        isolation level for
   <code class="literal">
    InnoDB
   </code>
   is
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   .
  </p>
  <p>
   A user can change the isolation level for a single session or
        for all subsequent connections with the
   <a class="link" href="set-transaction.html" title="15.3.7 SET TRANSACTION Statement">
    <code class="literal">
     SET
        TRANSACTION
    </code>
   </a>
   statement. To set the server's default
        isolation level for all connections, use the
   <a class="link" href="server-options.html#option_mysqld_transaction-isolation">
    <code class="option">
     --transaction-isolation
    </code>
   </a>
   option on
        the command line or in an option file. For detailed information
        about isolation levels and level-setting syntax, see
   <a class="xref" href="set-transaction.html" title="15.3.7 SET TRANSACTION Statement">
    Section 15.3.7, “SET TRANSACTION Statement”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   supports each of the transaction
        isolation levels described here using different
   <a class="link" href="glossary.html#glos_locking" title="locking">
    locking
   </a>
   strategies. You can
        enforce a high degree of consistency with the default
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   level, for
        operations on crucial data where
   <a class="link" href="glossary.html#glos_acid" title="ACID">
    ACID
   </a>
   compliance is important.
        Or you can relax the consistency rules with
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ COMMITTED
    </code>
   </a>
   or even
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-uncommitted">
    <code class="literal">
     READ UNCOMMITTED
    </code>
   </a>
   , in
        situations such as bulk reporting where precise consistency and
        repeatable results are less important than minimizing the amount
        of overhead for locking.
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
    <code class="literal">
     SERIALIZABLE
    </code>
   </a>
   enforces even
        stricter rules than
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE
        READ
    </code>
   </a>
   , and is used mainly in specialized situations,
        such as with
   <a class="link" href="glossary.html#glos_xa" title="XA">
    XA
   </a>
   transactions and
        for troubleshooting issues with concurrency and
   <a class="link" href="glossary.html#glos_deadlock" title="deadlock">
    deadlocks
   </a>
   .
  </p>
  <p>
   The following list describes how MySQL supports the different
        transaction levels. The list goes from the most commonly used
        level to the least used.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a name="isolevel_repeatable-read">
      </a>
      <code class="literal">
       REPEATABLE READ
      </code>
     </p>
     <a class="indexterm" name="idm46045164378160">
     </a>
     <a class="indexterm" name="idm46045164376768">
     </a>
     <p>
      This is the default isolation level for
      <code class="literal">
       InnoDB
      </code>
      .
      <a class="link" href="glossary.html#glos_consistent_read" title="consistent read">
       Consistent reads
      </a>
      within the same transaction read the
      <a class="link" href="glossary.html#glos_snapshot" title="snapshot">
       snapshot
      </a>
      established by
            the first read. This means that if you issue several plain
            (nonlocking)
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements within the same transaction, these
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements are
            consistent also with respect to each other. See
      <a class="xref" href="innodb-consistent-read.html" title="17.7.2.3 Consistent Nonlocking Reads">
       Section 17.7.2.3, “Consistent Nonlocking Reads”
      </a>
      .
     </p>
     <p>
      For
      <a class="link" href="glossary.html#glos_locking_read" title="locking read">
       locking reads
      </a>
      (
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      with
      <code class="literal">
       FOR
            UPDATE
      </code>
      or
      <code class="literal">
       FOR SHARE
      </code>
      ),
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements, locking
            depends on whether the statement uses a unique index with a
            unique search condition, or a range-type search condition.
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         For a unique index with a unique search condition,
         <code class="literal">
          InnoDB
         </code>
         locks only the index record
                found, not the
         <a class="link" href="glossary.html#glos_gap" title="gap">
          gap
         </a>
         before it.
        </p>
       </li>
       <li class="listitem">
        <p>
         For other search conditions,
         <code class="literal">
          InnoDB
         </code>
         locks the index range scanned, using
         <a class="link" href="glossary.html#glos_gap_lock" title="gap lock">
          gap locks
         </a>
         or
         <a class="link" href="glossary.html#glos_next_key_lock" title="next-key lock">
          next-key locks
         </a>
         to block insertions by other sessions into the gaps
                covered by the range. For information about gap locks
                and next-key locks, see
         <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
          Section 17.7.1, “InnoDB Locking”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      It is not recommended to mix locking statements
            (
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      ,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      , or
      <code class="literal">
       SELECT
            ... FOR ...
      </code>
      ) with non-locking
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements in a single
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
       <code class="literal">
        REPEATABLE READ
       </code>
      </a>
      transaction, because typically in such cases you want
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
       <code class="literal">
        SERIALIZABLE
       </code>
      </a>
      . This is
            because a non-locking
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement presents the state of the database from a read
            view which consists of transactions committed before the
            read view was created, and before the current transaction's
            own writes, while the locking statements use the most recent
            state of the database to use locking. In general, these two
            different table states are inconsistent with each other and
            difficult to parse.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a name="isolevel_read-committed">
      </a>
      <code class="literal">
       READ COMMITTED
      </code>
     </p>
     <a class="indexterm" name="idm46045164348480">
     </a>
     <a class="indexterm" name="idm46045164347088">
     </a>
     <p>
      Each consistent read, even within the same transaction, sets
            and reads its own fresh snapshot. For information about
            consistent reads, see
      <a class="xref" href="innodb-consistent-read.html" title="17.7.2.3 Consistent Nonlocking Reads">
       Section 17.7.2.3, “Consistent Nonlocking Reads”
      </a>
      .
     </p>
     <p>
      For locking reads (
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      with
      <code class="literal">
       FOR UPDATE
      </code>
      or
      <code class="literal">
       FOR
            SHARE
      </code>
      ),
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      statements, and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements,
      <code class="literal">
       InnoDB
      </code>
      locks only index
            records, not the gaps before them, and thus permits the free
            insertion of new records next to locked records. Gap locking
            is only used for foreign-key constraint checking and
            duplicate-key checking.
     </p>
     <p>
      Because gap locking is disabled, phantom row problems may
            occur, as other sessions can insert new rows into the gaps.
            For information about phantom rows, see
      <a class="xref" href="innodb-next-key-locking.html" title="17.7.4 Phantom Rows">
       Section 17.7.4, “Phantom Rows”
      </a>
      .
     </p>
     <p>
      Only row-based binary logging is supported with the
      <code class="literal">
       READ COMMITTED
      </code>
      isolation level. If you
            use
      <code class="literal">
       READ COMMITTED
      </code>
      with
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=MIXED
       </code>
      </a>
      , the
            server automatically uses row-based logging.
     </p>
     <p>
      Using
      <code class="literal">
       READ COMMITTED
      </code>
      has additional
            effects:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         For
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         or
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
         statements,
         <code class="literal">
          InnoDB
         </code>
         holds locks only for rows that
                it updates or deletes. Record locks for nonmatching rows
                are released after MySQL has evaluated the
         <code class="literal">
          WHERE
         </code>
         condition. This greatly reduces
                the probability of deadlocks, but they can still happen.
        </p>
       </li>
       <li class="listitem">
        <p>
         For
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         statements, if
                a row is already locked,
         <code class="literal">
          InnoDB
         </code>
         performs a
         <span class="quote">
          “
          <span class="quote">
           semi-consistent
          </span>
          ”
         </span>
         read,
                returning the latest committed version to MySQL so that
                MySQL can determine whether the row matches the
         <code class="literal">
          WHERE
         </code>
         condition of the
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         . If the row
                matches (must be updated), MySQL reads the row again and
                this time
         <code class="literal">
          InnoDB
         </code>
         either locks it or
                waits for a lock on it.
        </p>
       </li>
      </ul>
     </div>
     <p>
      Consider the table created and populated like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45664830"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In this case, the table has no indexes, so searches and
            index scans use the hidden clustered index for record
            locking (see
      <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
       Section 17.6.2.1, “Clustered and Secondary Indexes”
      </a>
      ) rather
            than indexed columns.
     </p>
     <p>
      Suppose that one session performs an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      using these
            statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10568338"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true"># Session A</span>
<span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> b <span class="token operator">=</span> <span class="token number">5</span> <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Suppose also that a second session performs an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      by executing these
            statements following those of the first session:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82696483"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true"># Session B</span>
<span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> b <span class="token operator">=</span> <span class="token number">4</span> <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      As
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      executes each
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , it first acquires an
            exclusive lock for each row, and then determines whether to
            modify it. If
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      does not
            modify the row, it releases the lock. Otherwise,
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      retains the lock until
            the end of the transaction. This affects transaction
            processing as follows.
     </p>
     <p>
      When using the default
      <code class="literal">
       REPEATABLE READ
      </code>
      isolation level, the first
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      acquires an x-lock on
            each row that it reads and does not release any of them:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa22680521"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock</code></pre>
     </div>
     <p>
      The second
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      blocks as
            soon as it tries to acquire any locks (because first update
            has retained locks on all rows), and does not proceed until
            the first
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      commits or
            rolls back:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa70066600"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">x-lock(1,2); block and wait for first UPDATE to commit or roll back</code></pre>
     </div>
     <p>
      If
      <code class="literal">
       READ COMMITTED
      </code>
      is used instead, the
            first
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      acquires an
            x-lock on each row that it reads and releases those for rows
            that it does not modify:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa17028125"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)</code></pre>
     </div>
     <p>
      For the second
      <code class="literal">
       UPDATE
      </code>
      ,
      <code class="literal">
       InnoDB
      </code>
      does a
      <span class="quote">
       “
       <span class="quote">
        semi-consistent
       </span>
       ”
      </span>
      read, returning the latest
            committed version of each row that it reads to MySQL so that
            MySQL can determine whether the row matches the
      <code class="literal">
       WHERE
      </code>
      condition of the
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa45028722"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock</code></pre>
     </div>
     <p>
      However, if the
      <code class="literal">
       WHERE
      </code>
      condition includes
            an indexed column, and
      <code class="literal">
       InnoDB
      </code>
      uses the
            index, only the indexed column is considered when taking and
            retaining record locks. In the following example, the first
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      takes and retains an
            x-lock on each row where b = 2. The second
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      blocks when it tries
            to acquire x-locks on the same records, as it also uses the
            index defined on column b.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78443093"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> b <span class="token datatype">INT</span><span class="token punctuation">,</span> c <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># Session A</span>
<span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> b <span class="token operator">=</span> <span class="token number">3</span> <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true"># Session B</span>
<span class="token keyword">UPDATE</span> t <span class="token keyword">SET</span> b <span class="token operator">=</span> <span class="token number">4</span> <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">2</span> <span class="token operator">AND</span> c <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <code class="literal">
       READ COMMITTED
      </code>
      isolation level can be
            set at startup or changed at runtime. At runtime, it can be
            set globally for all sessions, or individually per session.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a name="isolevel_read-uncommitted">
      </a>
      <code class="literal">
       READ UNCOMMITTED
      </code>
     </p>
     <a class="indexterm" name="idm46045164289040">
     </a>
     <a class="indexterm" name="idm46045164287648">
     </a>
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements are
            performed in a nonlocking fashion, but a possible earlier
            version of a row might be used. Thus, using this isolation
            level, such reads are not consistent. This is also called a
      <a class="link" href="glossary.html#glos_dirty_read" title="dirty read">
       dirty read
      </a>
      .
            Otherwise, this isolation level works like
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a name="isolevel_serializable">
      </a>
      <code class="literal">
       SERIALIZABLE
      </code>
     </p>
     <a class="indexterm" name="idm46045164281296">
     </a>
     <a class="indexterm" name="idm46045164279904">
     </a>
     <p>
      This level is like
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
       <code class="literal">
        REPEATABLE
            READ
       </code>
      </a>
      , but
      <code class="literal">
       InnoDB
      </code>
      implicitly
            converts all plain
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements to
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
            ... FOR SHARE
       </code>
      </a>
      if
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      is disabled. If
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      is enabled, the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      is its own
            transaction. It therefore is known to be read only and can
            be serialized if performed as a consistent (nonlocking) read
            and need not block for other transactions. (To force a plain
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      to block if other
            transactions have modified the selected rows, disable
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      .)
     </p>
     <p>
      DML operations that read data from MySQL grant tables
            (through a join list or subquery) but do not modify them do
            not acquire read locks on the MySQL grant tables, regardless
            of the isolation level. For more information, see
      <a class="xref" href="grant-tables.html#grant-tables-concurrency" title="Grant Table Concurrency">
       Grant Table Concurrency
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
