<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="performance-schema-data-lock-waits-table">
      </a>
      29.12.13.2 The data_lock_waits Table
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045070431840">
  </a>
  <a class="indexterm" name="idm46045070430352">
  </a>
  <p>
   The
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table
          implements a many-to-many relationship showing which data lock
          requests in the
   <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </code>
   </a>
   table
          are blocked by which held data locks in the
   <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </code>
   </a>
   table. Held locks in
   <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </code>
   </a>
   appear in
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   only if they
          block some lock request.
  </p>
  <p>
   This information enables you to understand data lock
          dependencies between sessions. The table exposes not only
          which lock a session or transaction is waiting for, but which
          session or transaction currently holds that lock.
  </p>
  <p>
   Example data lock wait information:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44509122"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>data_lock_waits\G
<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 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><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><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><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <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><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><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><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><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
                          ENGINE<span class="token punctuation">:</span> INNODB
       REQUESTING_ENGINE_LOCK_ID<span class="token punctuation">:</span> 140211201964816<span class="token punctuation">:</span>2<span class="token punctuation">:</span>4<span class="token punctuation">:</span>2<span class="token punctuation">:</span>140211086465800
REQUESTING_ENGINE_TRANSACTION_ID<span class="token punctuation">:</span> 1555
            REQUESTING_THREAD_ID<span class="token punctuation">:</span> 47
             REQUESTING_EVENT_ID<span class="token punctuation">:</span> 5
REQUESTING_OBJECT_INSTANCE_BEGIN<span class="token punctuation">:</span> 140211086465800
         BLOCKING_ENGINE_LOCK_ID<span class="token punctuation">:</span> 140211201963888<span class="token punctuation">:</span>2<span class="token punctuation">:</span>4<span class="token punctuation">:</span>2<span class="token punctuation">:</span>140211086459880
  BLOCKING_ENGINE_TRANSACTION_ID<span class="token punctuation">:</span> 1554
              BLOCKING_THREAD_ID<span class="token punctuation">:</span> 46
               BLOCKING_EVENT_ID<span class="token punctuation">:</span> 12
  BLOCKING_OBJECT_INSTANCE_BEGIN<span class="token punctuation">:</span> 140211086459880</span></code></pre>
  </div>
  <p>
   Unlike most Performance Schema data collection, there are no
          instruments for controlling whether data lock information is
          collected or system variables for controlling data lock table
          sizes. The Performance Schema collects information that is
          already available in the server, so there is no memory or CPU
          overhead to generate this information or need for parameters
          that control its collection.
  </p>
  <p>
   Use the
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table to
          help diagnose performance problems that occur during times of
          heavy concurrent load. For
   <code class="literal">
    InnoDB
   </code>
   , see the
          discussion of this topic at
   <a class="xref" href="innodb-information-schema-transactions.html" title="17.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information">
    Section 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”
   </a>
   .
  </p>
  <p>
   Because the columns in the
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table are similar
          to those in the
   <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </code>
   </a>
   table,
          the column descriptions here are abbreviated. For more
          detailed column descriptions, see
   <a class="xref" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
    Section 29.12.13.1, “The data_locks Table”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table has
          these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ENGINE
      </code>
     </p>
     <p>
      The storage engine that requested the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REQUESTING_ENGINE_LOCK_ID
      </code>
     </p>
     <p>
      The ID of the lock requested by the storage engine. To
              obtain details about the lock, join this column with the
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
      column of the
      <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
       <code class="literal">
        data_locks
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REQUESTING_ENGINE_TRANSACTION_ID
      </code>
     </p>
     <p>
      The storage engine internal ID of the transaction that
              requested the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REQUESTING_THREAD_ID
      </code>
     </p>
     <p>
      The thread ID of the session that requested the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REQUESTING_EVENT_ID
      </code>
     </p>
     <p>
      The Performance Schema event that caused the lock request
              in the session that requested the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       REQUESTING_OBJECT_INSTANCE_BEGIN
      </code>
     </p>
     <p>
      The address in memory of the requested lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLOCKING_ENGINE_LOCK_ID
      </code>
     </p>
     <p>
      The ID of the blocking lock. To obtain details about the
              lock, join this column with the
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
      column of the
      <a class="link" href="performance-schema-data-locks-table.html" title="29.12.13.1 The data_locks Table">
       <code class="literal">
        data_locks
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLOCKING_ENGINE_TRANSACTION_ID
      </code>
     </p>
     <p>
      The storage engine internal ID of the transaction that
              holds the blocking lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLOCKING_THREAD_ID
      </code>
     </p>
     <p>
      The thread ID of the session that holds the blocking lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLOCKING_EVENT_ID
      </code>
     </p>
     <p>
      The Performance Schema event that caused the blocking lock
              in the session that holds it.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BLOCKING_OBJECT_INSTANCE_BEGIN
      </code>
     </p>
     <p>
      The address in memory of the blocking lock.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table has
          these indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       REQUESTING_ENGINE_LOCK_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       BLOCKING_ENGINE_LOCK_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on
              (
      <code class="literal">
       REQUESTING_ENGINE_TRANSACTION_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on
              (
      <code class="literal">
       BLOCKING_ENGINE_TRANSACTION_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       REQUESTING_THREAD_ID
      </code>
      ,
      <code class="literal">
       REQUESTING_EVENT_ID
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       BLOCKING_THREAD_ID
      </code>
      ,
      <code class="literal">
       BLOCKING_EVENT_ID
      </code>
      )
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   is not permitted
          for the
   <a class="link" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    <code class="literal">
     data_lock_waits
    </code>
   </a>
   table.
  </p>
 </div>
 <br/>
</div>
