<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-deadlocks">
      </a>
      17.7.5 Deadlocks in InnoDB
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="innodb-deadlock-example.html">
       17.7.5.1 An InnoDB Deadlock Example
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-deadlock-detection.html">
       17.7.5.2 Deadlock Detection
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="innodb-deadlocks-handling.html">
       17.7.5.3 How to Minimize and Handle Deadlocks
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045163854400">
  </a>
  <a class="indexterm" name="idm46045163852912">
  </a>
  <a class="indexterm" name="idm46045163851008">
  </a>
  <a class="indexterm" name="idm46045163849520">
  </a>
  <p>
   A deadlock is a situation in which multiple transactions are
      unable to proceed because each transaction holds a lock that is
      needed by another one. Because all transactions involved are
      waiting for the same resource to become available, none of them
      ever releases the lock it holds.
  </p>
  <p>
   A deadlock can occur when transactions lock rows in multiple
      tables (through statements such as
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   or
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT ... FOR
      UPDATE
    </code>
   </a>
   ), but in the opposite order. A deadlock can also
      occur when such statements lock ranges of index records and gaps,
      with each transaction acquiring some locks but not others due to a
      timing issue. For a deadlock example, see
   <a class="xref" href="innodb-deadlock-example.html" title="17.7.5.1 An InnoDB Deadlock Example">
    Section 17.7.5.1, “An InnoDB Deadlock Example”
   </a>
   .
  </p>
  <p>
   To reduce the possibility of deadlocks, use transactions rather
      than
   <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
    <code class="literal">
     LOCK TABLES
    </code>
   </a>
   statements; keep
      transactions that insert or update data small enough that they do
      not stay open for long periods of time; when different
      transactions update multiple tables or large ranges of rows, use
      the same order of operations (such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT ... FOR
      UPDATE
    </code>
   </a>
   ) in each transaction; create indexes on the
      columns used in
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT ...
      FOR UPDATE
    </code>
   </a>
   and
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE ... WHERE
    </code>
   </a>
   statements. The possibility of deadlocks is not affected by the
      isolation level, because the isolation level changes the behavior
      of read operations, while deadlocks occur because of write
      operations. For more information about avoiding and recovering
      from deadlock conditions, see
   <a class="xref" href="innodb-deadlocks-handling.html" title="17.7.5.3 How to Minimize and Handle Deadlocks">
    Section 17.7.5.3, “How to Minimize and Handle Deadlocks”
   </a>
   .
  </p>
  <p>
   When deadlock detection is enabled (the default) and a deadlock
      does occur,
   <code class="literal">
    InnoDB
   </code>
   detects the condition and
      rolls back one of the transactions (the victim). If deadlock
      detection is disabled using the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_deadlock_detect">
    <code class="literal">
     innodb_deadlock_detect
    </code>
   </a>
   variable,
   <code class="literal">
    InnoDB
   </code>
   relies on the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_lock_wait_timeout">
    <code class="literal">
     innodb_lock_wait_timeout
    </code>
   </a>
   setting
      to roll back transactions in case of a deadlock. Thus, even if
      your application logic is correct, you must still handle the case
      where a transaction must be retried. To view the last deadlock in
      an
   <code class="literal">
    InnoDB
   </code>
   user transaction, use
   <a class="link" href="show-engine.html" title="15.7.7.16 SHOW ENGINE Statement">
    <code class="literal">
     SHOW ENGINE INNODB
      STATUS
    </code>
   </a>
   . If frequent deadlocks highlight a problem with
      transaction structure or application error handling, enable
   <a class="link" href="innodb-parameters.html#sysvar_innodb_print_all_deadlocks">
    <code class="literal">
     innodb_print_all_deadlocks
    </code>
   </a>
   to
      print information about all deadlocks to the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   error log. For more information about
      how deadlocks are automatically detected and handled, see
   <a class="xref" href="innodb-deadlock-detection.html" title="17.7.5.2 Deadlock Detection">
    Section 17.7.5.2, “Deadlock Detection”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
