<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-deadlocks-handling">
      </a>
      17.7.5.3 How to Minimize and Handle Deadlocks
     </h4>
    </div>
   </div>
  </div>
  <p>
   This section builds on the conceptual information about
        deadlocks in
   <a class="xref" href="innodb-deadlock-detection.html" title="17.7.5.2 Deadlock Detection">
    Section 17.7.5.2, “Deadlock Detection”
   </a>
   . It
        explains how to organize database operations to minimize
        deadlocks and the subsequent error handling required in
        applications.
  </p>
  <p>
   <a class="link" href="glossary.html#glos_deadlock" title="deadlock">
    Deadlocks
   </a>
   are a classic
        problem in transactional databases, but they are not dangerous
        unless they are so frequent that you cannot run certain
        transactions at all. Normally, you must write your applications
        so that they are always prepared to re-issue a transaction if it
        gets rolled back because of a deadlock.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   uses automatic row-level locking. You
        can get deadlocks even in the case of transactions that just
        insert or delete a single row. That is because these operations
        are not really
   <span class="quote">
    “
    <span class="quote">
     atomic
    </span>
    ”
   </span>
   ; they automatically set
        locks on the (possibly several) index records of the row
        inserted or deleted.
  </p>
  <p>
   You can cope with deadlocks and reduce the likelihood of their
        occurrence with the following techniques:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      At any time, issue
      <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
       <code class="literal">
        SHOW ENGINE
            INNODB STATUS
       </code>
      </a>
      to determine the cause of the most
            recent deadlock. That can help you to tune your application
            to avoid deadlocks.
     </p>
    </li>
    <li class="listitem">
     <p>
      If frequent deadlock warnings cause concern, collect more
            extensive debugging information by enabling the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_print_all_deadlocks">
       <code class="literal">
        innodb_print_all_deadlocks
       </code>
      </a>
      variable. Information about each deadlock, not just the
            latest one, is recorded in the MySQL
      <a class="link" href="glossary.html#glos_error_log" title="error log">
       error log
      </a>
      . Disable
            this option when you are finished debugging.
     </p>
    </li>
    <li class="listitem">
     <p>
      Always be prepared to re-issue a transaction if it fails due
            to deadlock. Deadlocks are not dangerous. Just try again.
     </p>
    </li>
    <li class="listitem">
     <p>
      Keep transactions small and short in duration to make them
            less prone to collision.
     </p>
    </li>
    <li class="listitem">
     <p>
      Commit transactions immediately after making a set of
            related changes to make them less prone to collision. In
            particular, do not leave an interactive
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      session open for a long time with
            an uncommitted transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use
      <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
            ... FOR UPDATE
       </code>
      </a>
      or
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
            SHARE
       </code>
      </a>
      ), try using a lower isolation level such as
      <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>
      When modifying multiple tables within a transaction, or
            different sets of rows in the same table, do those
            operations in a consistent order each time. Then
            transactions form well-defined queues and do not deadlock.
            For example, organize database operations into functions
            within your application, or call stored routines, rather
            than coding multiple similar sequences of
      <code class="literal">
       INSERT
      </code>
      ,
      <code class="literal">
       UPDATE
      </code>
      , and
      <code class="literal">
       DELETE
      </code>
      statements in different places.
     </p>
    </li>
    <li class="listitem">
     <p>
      Add well-chosen indexes to your tables so that your queries
            scan fewer index records and set fewer locks. Use
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
            SELECT
       </code>
      </a>
      to determine which indexes the MySQL server
            regards as the most appropriate for your queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use less locking. If you can afford to permit a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      to return data from an
            old snapshot, do not add a
      <code class="literal">
       FOR UPDATE
      </code>
      or
      <code class="literal">
       FOR SHARE
      </code>
      clause to it. Using the
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      isolation
            level is good here, because each consistent read within the
            same transaction reads from its own fresh snapshot.
     </p>
    </li>
    <li class="listitem">
     <p>
      If nothing else helps, serialize your transactions with
            table-level locks. The correct way to use
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      with
            transactional tables, such as
      <code class="literal">
       InnoDB
      </code>
      tables, is to begin a transaction with
      <code class="literal">
       SET
            autocommit = 0
      </code>
      (not
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        START
            TRANSACTION
       </code>
      </a>
      ) followed by
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK
            TABLES
       </code>
      </a>
      , and to not call
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        UNLOCK
            TABLES
       </code>
      </a>
      until you commit the transaction
            explicitly. For example, if you need to write to table
      <code class="literal">
       t1
      </code>
      and read from table
      <code class="literal">
       t2
      </code>
      , you can do this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28768858"><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">SET</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">LOCK</span> <span class="token keyword">TABLES</span> t1 <span class="token keyword">WRITE</span><span class="token punctuation">,</span> t2 <span class="token keyword">READ</span><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 class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">do</span> something <span class="token keyword">with</span> <span class="token keyword">tables</span> t1 <span class="token operator">and</span> t2 here <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></em><span class="token punctuation"></span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
<span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Table-level locks prevent concurrent updates to the table,
            avoiding deadlocks at the expense of less responsiveness for
            a busy system.
     </p>
    </li>
    <li class="listitem">
     <p>
      Another way to serialize transactions is to create an
            auxiliary
      <span class="quote">
       “
       <span class="quote">
        semaphore
       </span>
       ”
      </span>
      table that contains just
            a single row. Have each transaction update that row before
            accessing other tables. In that way, all transactions happen
            in a serial fashion. Note that the
      <code class="literal">
       InnoDB
      </code>
      instant deadlock detection algorithm also works in this
            case, because the serializing lock is a row-level lock. With
            MySQL table-level locks, the timeout method must be used to
            resolve deadlocks.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
