<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-locking-reads">
      </a>
      17.7.2.4 Locking Reads
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045164140432">
  </a>
  <p>
   If you query data and then insert or update related data within
        the same transaction, the regular
   <code class="literal">
    SELECT
   </code>
   statement does not give enough protection. Other transactions
        can update or delete the same rows you just queried.
   <code class="literal">
    InnoDB
   </code>
   supports two types of
   <a class="link" href="glossary.html#glos_locking_read" title="locking read">
    locking reads
   </a>
   that
        offer extra safety:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
            SHARE
       </code>
      </a>
     </p>
     <p>
      Sets a shared mode lock on any rows that are read. Other
            sessions can read the rows, but cannot modify them until
            your transaction commits. If any of these rows were changed
            by another transaction that has not yet committed, your
            query waits until that transaction ends and then uses the
            latest values.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       <code class="literal">
        SELECT ... FOR SHARE
       </code>
       is a replacement
              for
       <code class="literal">
        SELECT ... LOCK IN SHARE MODE
       </code>
       , but
       <code class="literal">
        LOCK IN SHARE MODE
       </code>
       remains available
              for backward compatibility. The statements are equivalent.
              However,
       <code class="literal">
        FOR SHARE
       </code>
       supports
       <code class="literal">
        OF
        <em class="replaceable">
         <code>
          table_name
         </code>
        </em>
       </code>
       ,
       <code class="literal">
        NOWAIT
       </code>
       , and
       <code class="literal">
        SKIP
              LOCKED
       </code>
       options. See
       <a class="xref" href="innodb-locking-reads.html#innodb-locking-reads-nowait-skip-locked" title="Locking Read Concurrency with NOWAIT and SKIP LOCKED">
        Locking Read Concurrency with NOWAIT and SKIP LOCKED
       </a>
       .
      </p>
     </div>
     <p>
      <code class="literal">
       SELECT ... FOR SHARE
      </code>
      requires the
      <a class="link" href="privileges-provided.html#priv_select">
       <code class="literal">
        SELECT
       </code>
      </a>
      privilege.
     </p>
     <p>
      <code class="literal">
       SELECT ... FOR SHARE
      </code>
      statements do not
            acquire read locks on MySQL grant tables. For more
            information, see
      <a class="xref" href="grant-tables.html#grant-tables-concurrency" title="Grant Table Concurrency">
       Grant Table Concurrency
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
            UPDATE
       </code>
      </a>
     </p>
     <p>
      For index records the search encounters, locks the rows and
            any associated index entries, the same as if you issued an
      <code class="literal">
       UPDATE
      </code>
      statement for those rows. Other
            transactions are blocked from updating those rows, from
            doing
      <code class="literal">
       SELECT ... FOR SHARE
      </code>
      , or from
            reading the data in certain transaction isolation levels.
            Consistent reads ignore any locks set on the records that
            exist in the read view. (Old versions of a record cannot be
            locked; they are reconstructed by applying
      <a class="link" href="glossary.html#glos_undo_log" title="undo log">
       undo logs
      </a>
      on an
            in-memory copy of the record.)
     </p>
     <p>
      <code class="literal">
       SELECT ... FOR UPDATE
      </code>
      requires the
      <a class="link" href="privileges-provided.html#priv_select">
       <code class="literal">
        SELECT
       </code>
      </a>
      privilege and at least
            one of the
      <a class="link" href="privileges-provided.html#priv_delete">
       <code class="literal">
        DELETE
       </code>
      </a>
      ,
      <a class="link" href="privileges-provided.html#priv_lock-tables">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      , or
      <a class="link" href="privileges-provided.html#priv_update">
       <code class="literal">
        UPDATE
       </code>
      </a>
      privileges.
     </p>
    </li>
   </ul>
  </div>
  <p>
   These clauses are primarily useful when dealing with
        tree-structured or graph-structured data, either in a single
        table or split across multiple tables. You traverse edges or
        tree branches from one place to another, while reserving the
        right to come back and change any of these
   <span class="quote">
    “
    <span class="quote">
     pointer
    </span>
    ”
   </span>
   values.
  </p>
  <p>
   All locks set by
   <code class="literal">
    FOR SHARE
   </code>
   and
   <code class="literal">
    FOR
        UPDATE
   </code>
   queries are released when the transaction is
        committed or rolled back.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Locking reads are only possible when autocommit is disabled
          (either by beginning transaction with
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      START
          TRANSACTION
     </code>
    </a>
    or by setting
    <a class="link" href="server-system-variables.html#sysvar_autocommit">
     <code class="literal">
      autocommit
     </code>
    </a>
    to 0.
   </p>
  </div>
  <p>
   A locking read clause in an outer statement does not lock the
        rows of a table in a nested subquery unless a locking read
        clause is also specified in the subquery. For example, the
        following statement does not lock rows in table
   <code class="literal">
    t2
   </code>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56871349"><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> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To lock rows in table
   <code class="literal">
    t2
   </code>
   , add a locking read
        clause to the subquery:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13957535"><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> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c1 <span class="token keyword">FROM</span> t2 <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">)</span> <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-locking-reads-examples">
       </a>
       Locking Read Examples
      </h5>
     </div>
    </div>
   </div>
   <p>
    Suppose that you want to insert a new row into a table
    <code class="literal">
     child
    </code>
    , and make sure that the child row has
          a parent row in table
    <code class="literal">
     parent
    </code>
    . Your
          application code can ensure referential integrity throughout
          this sequence of operations.
   </p>
   <p>
    First, use a consistent read to query the table
    <code class="literal">
     PARENT
    </code>
    and verify that the parent row
          exists. Can you safely insert the child row to table
    <code class="literal">
     CHILD
    </code>
    ? No, because some other session could
          delete the parent row in the moment between your
    <code class="literal">
     SELECT
    </code>
    and your
    <code class="literal">
     INSERT
    </code>
    ,
          without you being aware of it.
   </p>
   <p>
    To avoid this potential issue, perform the
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    using
    <code class="literal">
     FOR
          SHARE
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43269645"><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> parent <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">=</span> <span class="token string">'Jones'</span> <span class="token keyword">FOR</span> <span class="token keyword">SHARE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    After the
    <code class="literal">
     FOR SHARE
    </code>
    query returns the
          parent
    <code class="literal">
     'Jones'
    </code>
    , you can safely add the
          child record to the
    <code class="literal">
     CHILD
    </code>
    table and commit
          the transaction. Any transaction that tries to acquire an
          exclusive lock in the applicable row in the
    <code class="literal">
     PARENT
    </code>
    table waits until you are finished,
          that is, until the data in all tables is in a consistent
          state.
   </p>
   <p>
    For another example, consider an integer counter field in a
          table
    <code class="literal">
     CHILD_CODES
    </code>
    , used to assign a unique
          identifier to each child added to table
    <code class="literal">
     CHILD
    </code>
    . Do not use either consistent read or
          a shared mode read to read the present value of the counter,
          because two users of the database could see the same value for
          the counter, and a duplicate-key error occurs if two
          transactions attempt to add rows with the same identifier to
          the
    <code class="literal">
     CHILD
    </code>
    table.
   </p>
   <p>
    Here,
    <code class="literal">
     FOR SHARE
    </code>
    is not a good solution
          because if two users read the counter at the same time, at
          least one of them ends up in deadlock when it attempts to
          update the counter.
   </p>
   <p>
    To implement reading and incrementing the counter, first
          perform a locking read of the counter using
    <code class="literal">
     FOR
          UPDATE
    </code>
    , and then increment the counter. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89834019"><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> counter_field <span class="token keyword">FROM</span> child_codes <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> child_codes <span class="token keyword">SET</span> counter_field <span class="token operator">=</span> counter_field <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT ... FOR
          UPDATE
     </code>
    </a>
    reads the latest available data, setting
          exclusive locks on each row it reads. Thus, it sets the same
          locks a searched SQL
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    would set on the rows.
   </p>
   <p>
    The preceding description is merely an example of how
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT ... FOR
          UPDATE
     </code>
    </a>
    works. In MySQL, the specific task of
          generating a unique identifier actually can be accomplished
          using only a single access to the table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14213368"><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">UPDATE</span> child_codes <span class="token keyword">SET</span> counter_field <span class="token operator">=</span> <span class="token function">LAST_INSERT_ID</span><span class="token punctuation">(</span>counter_field <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">LAST_INSERT_ID</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement merely
          retrieves the identifier information (specific to the current
          connection). It does not access any table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-locking-reads-nowait-skip-locked">
       </a>
       Locking Read Concurrency with NOWAIT and SKIP LOCKED
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045164069792">
   </a>
   <a class="indexterm" name="idm46045164068720">
   </a>
   <a class="indexterm" name="idm46045164067232">
   </a>
   <p>
    If a row is locked by a transaction, a
    <code class="literal">
     SELECT ... FOR
          UPDATE
    </code>
    or
    <code class="literal">
     SELECT ... FOR SHARE
    </code>
    transaction that requests the same locked row must wait until
          the blocking transaction releases the row lock. This behavior
          prevents transactions from updating or deleting rows that are
          queried for updates by other transactions. However, waiting
          for a row lock to be released is not necessary if you want the
          query to return immediately when a requested row is locked, or
          if excluding locked rows from the result set is acceptable.
   </p>
   <p>
    To avoid waiting for other transactions to release row locks,
    <code class="literal">
     NOWAIT
    </code>
    and
    <code class="literal">
     SKIP LOCKED
    </code>
    options may be used with
    <code class="literal">
     SELECT ... FOR
          UPDATE
    </code>
    or
    <code class="literal">
     SELECT ... FOR SHARE
    </code>
    locking read statements.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        NOWAIT
       </code>
      </p>
      <p>
       A locking read that uses
       <code class="literal">
        NOWAIT
       </code>
       never
              waits to acquire a row lock. The query executes
              immediately, failing with an error if a requested row is
              locked.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        SKIP LOCKED
       </code>
      </p>
      <p>
       A locking read that uses
       <code class="literal">
        SKIP LOCKED
       </code>
       never waits to acquire a row lock. The query executes
              immediately, removing locked rows from the result set.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        Queries that skip locked rows return an inconsistent
                view of the data.
        <code class="literal">
         SKIP LOCKED
        </code>
        is
                therefore not suitable for general transactional work.
                However, it may be used to avoid lock contention when
                multiple sessions access the same queue-like table.
       </p>
      </div>
     </li>
    </ul>
   </div>
   <p>
    <code class="literal">
     NOWAIT
    </code>
    and
    <code class="literal">
     SKIP LOCKED
    </code>
    only apply to row-level locks.
   </p>
   <p>
    Statements that use
    <code class="literal">
     NOWAIT
    </code>
    or
    <code class="literal">
     SKIP
          LOCKED
    </code>
    are unsafe for statement based replication.
   </p>
   <p>
    The following example demonstrates
    <code class="literal">
     NOWAIT
    </code>
    and
    <code class="literal">
     SKIP LOCKED
    </code>
    . Session 1 starts a
          transaction that takes a row lock on a single record. Session
          2 attempts a locking read on the same record using the
    <code class="literal">
     NOWAIT
    </code>
    option. Because the requested row is
          locked by Session 1, the locking read returns immediately with
          an error. In Session 3, the locking read with
    <code class="literal">
     SKIP
          LOCKED
    </code>
    returns the requested rows except for the row
          that is locked by Session 1.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98052982"><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 1:</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>i<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 prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <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 punctuation">,</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">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> i <span class="token operator">=</span> <span class="token number">2</span> <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token comment" spellcheck="true"># Session 2:</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> i <span class="token operator">=</span> <span class="token number">2</span> <span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NOWAIT</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3572 (HY000)<span class="token punctuation">:</span> Do not wait for lock.

# Session 3<span class="token punctuation">:</span>
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <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">UPDATE</span> <span class="token keyword">SKIP</span> <span class="token keyword">LOCKED</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
