<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-consistent-read">
      </a>
      17.7.2.3 Consistent Nonlocking Reads
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045164206624">
  </a>
  <a class="indexterm" name="idm46045164205168">
  </a>
  <p>
   A
   <a class="link" href="glossary.html#glos_consistent_read" title="consistent read">
    consistent read
   </a>
   means that
   <code class="literal">
    InnoDB
   </code>
   uses multi-versioning to
        present to a query a snapshot of the database at a point in
        time. The query sees the changes made by transactions that
        committed before that point in time, and no changes made by
        later or uncommitted transactions. The exception to this rule is
        that the query sees the changes made by earlier statements
        within the same transaction. This exception causes the following
        anomaly: If you update some rows in a table, a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   sees the latest version of
        the updated rows, but it might also see older versions of any
        rows. If other sessions simultaneously update the same table,
        the anomaly means that you might see the table in a state that
        never existed in the database.
  </p>
  <p>
   If the transaction
   <a class="link" href="glossary.html#glos_isolation_level" title="isolation level">
    isolation level
   </a>
   is
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   (the default
        level), all consistent reads within the same transaction read
        the snapshot established by the first such read in that
        transaction. You can get a fresher snapshot for your queries by
        committing the current transaction and after that issuing new
        queries.
  </p>
  <p>
   With
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ COMMITTED
    </code>
   </a>
   isolation
        level, each consistent read within a transaction sets and reads
        its own fresh snapshot.
  </p>
  <p>
   Consistent read is the default mode in which
   <code class="literal">
    InnoDB
   </code>
   processes
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements in
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ COMMITTED
    </code>
   </a>
   and
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   isolation
        levels. A consistent read does not set any locks on the tables
        it accesses, and therefore other sessions are free to modify
        those tables at the same time a consistent read is being
        performed on the table.
  </p>
  <p>
   Suppose that you are running in the default
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
    <code class="literal">
     REPEATABLE READ
    </code>
   </a>
   isolation
        level. When you issue a consistent read (that is, an ordinary
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement),
   <code class="literal">
    InnoDB
   </code>
   gives your transaction a timepoint
        according to which your query sees the database. If another
        transaction deletes a row and commits after your timepoint was
        assigned, you do not see the row as having been deleted. Inserts
        and updates are treated similarly.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The snapshot of the database state applies to
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements within a
          transaction, not necessarily to
    <a class="link" href="glossary.html#glos_dml" title="DML">
     DML
    </a>
    statements. If you insert
          or modify some rows and then commit that transaction, a
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    or
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    statement issued from
          another concurrent
    <code class="literal">
     REPEATABLE READ
    </code>
    transaction could affect those just-committed rows, even
          though the session could not query them. If a transaction does
          update or delete rows committed by a different transaction,
          those changes do become visible to the current transaction.
          For example, you might encounter a situation like the
          following:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51297438"><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">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token string">'xyz'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Returns 0: no rows match.</span>
<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token string">'xyz'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Deletes several rows recently committed by other transaction.</span>

<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c2 <span class="token operator">=</span> <span class="token string">'abc'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Returns 0: no rows match.</span>
<span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> c2 <span class="token operator">=</span> <span class="token string">'cba'</span> <span class="token keyword">WHERE</span> c2 <span class="token operator">=</span> <span class="token string">'abc'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.</span>
<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c2 <span class="token operator">=</span> <span class="token string">'cba'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Returns 10: this txn can now see the rows it just updated.</span></code></pre>
   </div>
  </div>
  <p>
   You can advance your timepoint by committing your transaction
        and then doing another
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   or
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     START TRANSACTION WITH
        CONSISTENT SNAPSHOT
    </code>
   </a>
   .
  </p>
  <p>
   This is called
   <span class="firstterm">
    multi-versioned
        concurrency control
   </span>
   .
  </p>
  <p>
   In the following example, session A sees the row inserted by B
        only when B has committed the insert and A has committed as
        well, so that the timepoint is advanced past the commit of B.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90885946"><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">Session</span> A              <span class="token keyword">Session</span> B

           <span class="token keyword">SET</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>      <span class="token keyword">SET</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token datatype">time</span>
<span class="token operator">|</span>          <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
<span class="token operator">|</span>          <span class="token keyword">empty</span> <span class="token keyword">set</span>
<span class="token operator">|</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 operator">|</span>
v          <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
           <span class="token keyword">empty</span> <span class="token keyword">set</span>
                                  <span class="token keyword">COMMIT</span><span class="token punctuation">;</span>

           <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
           <span class="token keyword">empty</span> <span class="token keyword">set</span>

           <span class="token keyword">COMMIT</span><span class="token punctuation">;</span>

           <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
           <span class="token comment" spellcheck="true">---------------------</span>
<span class="token output">           <span class="token punctuation">|</span>    1    <span class="token punctuation">|</span>    2    <span class="token punctuation">|</span></span>
           <span class="token comment" spellcheck="true">---------------------</span></code></pre>
  </div>
  <p>
   If you want to see the
   <span class="quote">
    “
    <span class="quote">
     freshest
    </span>
    ”
   </span>
   state of the
        database, use either the
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ
        COMMITTED
    </code>
   </a>
   isolation level or a
   <a class="link" href="glossary.html#glos_locking_read" title="locking read">
    locking read
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95818468"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">FOR</span> <span class="token keyword">SHARE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ COMMITTED
    </code>
   </a>
   isolation
        level, each consistent read within a transaction sets and reads
        its own fresh snapshot. With
   <code class="literal">
    FOR SHARE
   </code>
   , a
        locking read occurs instead: A
   <code class="literal">
    SELECT
   </code>
   blocks
        until the transaction containing the freshest rows ends (see
   <a class="xref" href="innodb-locking-reads.html" title="17.7.2.4 Locking Reads">
    Section 17.7.2.4, “Locking Reads”
   </a>
   ).
  </p>
  <p>
   Consistent read does not work over certain DDL statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Consistent read does not work over
      <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
       <code class="literal">
        DROP
            TABLE
       </code>
      </a>
      , because MySQL cannot use a table that has
            been dropped and
      <code class="literal">
       InnoDB
      </code>
      destroys the
            table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Consistent read does not work over
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      operations that
            make a temporary copy of the original table and delete the
            original table when the temporary copy is built. When you
            reissue a consistent read within a transaction, rows in the
            new table are not visible because those rows did not exist
            when the transaction's snapshot was taken. In this case, the
            transaction returns an error:
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_table_def_changed" target="_top">
       <code class="literal">
        ER_TABLE_DEF_CHANGED
       </code>
      </a>
      ,
      <span class="quote">
       “
       <span class="quote">
        Table definition has changed, please retry
            transaction
       </span>
       ”
      </span>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The type of read varies for selects in clauses like
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT INTO ...
        SELECT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
        ... (SELECT)
    </code>
   </a>
   , and
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
        SELECT
    </code>
   </a>
   that do not specify
   <code class="literal">
    FOR
        UPDATE
   </code>
   or
   <code class="literal">
    FOR SHARE
   </code>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      By default,
      <code class="literal">
       InnoDB
      </code>
      uses stronger locks
            for those statements and the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      part acts like
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      , where
            each consistent read, even within the same transaction, sets
            and reads its own fresh snapshot.
     </p>
    </li>
    <li class="listitem">
     <p>
      To perform a nonlocking read in such cases, set the
            isolation level of the transaction to
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-uncommitted">
       <code class="literal">
        READ UNCOMMITTED
       </code>
      </a>
      or
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      to avoid
            setting locks on rows read from the selected table.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
