<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="performance-schema-data-locks-table">
      </a>
      29.12.13.1 The data_locks Table
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045070548736">
  </a>
  <a class="indexterm" name="idm46045070547248">
  </a>
  <p>
   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 shows data
          locks held and requested. For information about which lock
          requests are blocked by which held locks, see
   <a class="xref" href="performance-schema-data-lock-waits-table.html" title="29.12.13.2 The data_lock_waits Table">
    Section 29.12.13.2, “The data_lock_waits Table”
   </a>
   .
  </p>
  <p>
   Example data lock information:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65173355"><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_locks\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
       ENGINE_LOCK_ID<span class="token punctuation">:</span> 139664434886512<span class="token punctuation">:</span>1059<span class="token punctuation">:</span>139664350547912
ENGINE_TRANSACTION_ID<span class="token punctuation">:</span> 2569
            THREAD_ID<span class="token punctuation">:</span> 46
             EVENT_ID<span class="token punctuation">:</span> 12
        OBJECT_SCHEMA<span class="token punctuation">:</span> test
          OBJECT_NAME<span class="token punctuation">:</span> t1
       PARTITION_NAME<span class="token punctuation">:</span> NULL
    SUBPARTITION_NAME<span class="token punctuation">:</span> NULL
           INDEX_NAME<span class="token punctuation">:</span> NULL
OBJECT_INSTANCE_BEGIN<span class="token punctuation">:</span> 139664350547912
            LOCK_TYPE<span class="token punctuation">:</span> TABLE
            LOCK_MODE<span class="token punctuation">:</span> IX
          LOCK_STATUS<span class="token punctuation">:</span> GRANTED
            LOCK_DATA<span class="token punctuation">:</span> NULL
<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> 2. 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
       ENGINE_LOCK_ID<span class="token punctuation">:</span> 139664434886512<span class="token punctuation">:</span>2<span class="token punctuation">:</span>4<span class="token punctuation">:</span>1<span class="token punctuation">:</span>139664350544872
ENGINE_TRANSACTION_ID<span class="token punctuation">:</span> 2569
            THREAD_ID<span class="token punctuation">:</span> 46
             EVENT_ID<span class="token punctuation">:</span> 12
        OBJECT_SCHEMA<span class="token punctuation">:</span> test
          OBJECT_NAME<span class="token punctuation">:</span> t1
       PARTITION_NAME<span class="token punctuation">:</span> NULL
    SUBPARTITION_NAME<span class="token punctuation">:</span> NULL
           INDEX_NAME<span class="token punctuation">:</span> GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN<span class="token punctuation">:</span> 139664350544872
            LOCK_TYPE<span class="token punctuation">:</span> RECORD
            LOCK_MODE<span class="token punctuation">:</span> X
          LOCK_STATUS<span class="token punctuation">:</span> GRANTED
            LOCK_DATA<span class="token punctuation">:</span> supremum pseudo-record</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-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </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>
   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 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 holds or requested the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
     </p>
     <p>
      The ID of the lock held or requested by the storage
              engine. Tuples of (
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      ) values are unique.
     </p>
     <p>
      Lock ID formats are internal and subject to change at any
              time. Applications should not rely on lock IDs having a
              particular format.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ENGINE_TRANSACTION_ID
      </code>
     </p>
     <p>
      The storage engine internal ID of the transaction that
              requested the lock. This can be considered the owner of
              the lock, although the lock might still be pending, not
              actually granted yet
              (
      <code class="literal">
       LOCK_STATUS='WAITING'
      </code>
      ).
     </p>
     <p>
      If the transaction has not yet performed any write
              operation (is still considered read only), the column
              contains internal data that users should not try to
              interpret. Otherwise, the column is the transaction ID.
     </p>
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      , to obtain details about the
              transaction, join this column with the
      <code class="literal">
       TRX_ID
      </code>
      column of the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-innodb-trx-table.html" title="28.4.28 The INFORMATION_SCHEMA INNODB_TRX Table">
       <code class="literal">
        INNODB_TRX
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       THREAD_ID
      </code>
     </p>
     <p>
      The thread ID of the session that created the lock. To
              obtain details about the thread, join this column with the
      <code class="literal">
       THREAD_ID
      </code>
      column of the Performance
              Schema
      <a class="link" href="performance-schema-threads-table.html" title="29.12.22.8 The threads Table">
       <code class="literal">
        threads
       </code>
      </a>
      table.
     </p>
     <p>
      <code class="literal">
       THREAD_ID
      </code>
      can be used together with
      <code class="literal">
       EVENT_ID
      </code>
      to determine the event during
              which the lock data structure was created in memory. (This
              event might have occurred before this particular lock
              request occurred, if the data structure is used to store
              multiple locks.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       EVENT_ID
      </code>
     </p>
     <p>
      The Performance Schema event that caused the lock. Tuples
              of (
      <code class="literal">
       THREAD_ID
      </code>
      ,
      <code class="literal">
       EVENT_ID
      </code>
      ) values implicitly identify a
              parent event in other Performance Schema tables:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The parent wait event in the
         <code class="literal">
          events_waits_
          <em class="replaceable">
           <code>
            xxx
           </code>
          </em>
         </code>
         tables
        </p>
       </li>
       <li class="listitem">
        <p>
         The parent stage event in the
         <code class="literal">
          events_stages_
          <em class="replaceable">
           <code>
            xxx
           </code>
          </em>
         </code>
         tables
        </p>
       </li>
       <li class="listitem">
        <p>
         The parent statement event in the
         <code class="literal">
          events_statements_
          <em class="replaceable">
           <code>
            xxx
           </code>
          </em>
         </code>
         tables
        </p>
       </li>
       <li class="listitem">
        <p>
         The parent transaction event in the
         <a class="link" href="performance-schema-events-transactions-current-table.html" title="29.12.7.1 The events_transactions_current Table">
          <code class="literal">
           events_transactions_current
          </code>
         </a>
         table
        </p>
       </li>
      </ul>
     </div>
     <p>
      To obtain details about the parent event, join the
      <code class="literal">
       THREAD_ID
      </code>
      and
      <code class="literal">
       EVENT_ID
      </code>
      columns with the columns of
              like name in the appropriate parent event table. See
      <a class="xref" href="performance-schema-obtaining-parent-events.html" title="29.19.2 Obtaining Parent Event Information">
       Section 29.19.2, “Obtaining Parent Event Information”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_SCHEMA
      </code>
     </p>
     <p>
      The schema that contains the locked table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_NAME
      </code>
     </p>
     <p>
      The name of the locked table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION_NAME
      </code>
     </p>
     <p>
      The name of the locked partition, if any;
      <code class="literal">
       NULL
      </code>
      otherwise.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION_NAME
      </code>
     </p>
     <p>
      The name of the locked subpartition, if any;
      <code class="literal">
       NULL
      </code>
      otherwise.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INDEX_NAME
      </code>
     </p>
     <p>
      The name of the locked index, if any;
      <code class="literal">
       NULL
      </code>
      otherwise.
     </p>
     <p>
      In practice,
      <code class="literal">
       InnoDB
      </code>
      always creates an
              index (
      <code class="literal">
       GEN_CLUST_INDEX
      </code>
      ), so
      <code class="literal">
       INDEX_NAME
      </code>
      is
              non-
      <code class="literal">
       NULL
      </code>
      for
      <code class="literal">
       InnoDB
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_INSTANCE_BEGIN
      </code>
     </p>
     <p>
      The address in memory of the lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_TYPE
      </code>
     </p>
     <p>
      The type of lock.
     </p>
     <p>
      The value is storage engine dependent. For
      <code class="literal">
       InnoDB
      </code>
      , permitted values are
      <code class="literal">
       RECORD
      </code>
      for a row-level lock,
      <code class="literal">
       TABLE
      </code>
      for a table-level lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_MODE
      </code>
     </p>
     <p>
      How the lock is requested.
     </p>
     <p>
      The value is storage engine dependent. For
      <code class="literal">
       InnoDB
      </code>
      , permitted values are
      <code class="literal">
       S[,GAP]
      </code>
      ,
      <code class="literal">
       X[,GAP]
      </code>
      ,
      <code class="literal">
       IS[,GAP]
      </code>
      ,
      <code class="literal">
       IX[,GAP]
      </code>
      ,
      <code class="literal">
       AUTO_INC
      </code>
      , and
      <code class="literal">
       UNKNOWN
      </code>
      . Lock modes other than
      <code class="literal">
       AUTO_INC
      </code>
      and
      <code class="literal">
       UNKNOWN
      </code>
      indicate gap locks, if present. For information about
      <code class="literal">
       S
      </code>
      ,
      <code class="literal">
       X
      </code>
      ,
      <code class="literal">
       IS
      </code>
      ,
      <code class="literal">
       IX
      </code>
      , and gap
              locks, refer to
      <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
       Section 17.7.1, “InnoDB Locking”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_STATUS
      </code>
     </p>
     <p>
      The status of the lock request.
     </p>
     <p>
      The value is storage engine dependent. For
      <code class="literal">
       InnoDB
      </code>
      , permitted values are
      <code class="literal">
       GRANTED
      </code>
      (lock is held) and
      <code class="literal">
       WAITING
      </code>
      (lock is being waited for).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_DATA
      </code>
     </p>
     <p>
      The data associated with the lock, if any. The value is
              storage engine dependent. For
      <code class="literal">
       InnoDB
      </code>
      , a
              value is shown if the
      <code class="literal">
       LOCK_TYPE
      </code>
      is
      <code class="literal">
       RECORD
      </code>
      , otherwise the value is
      <code class="literal">
       NULL
      </code>
      . Primary key values of the locked
              record are shown for a lock placed on the primary key
              index. Secondary index values of the locked record are
              shown with primary key values appended for a lock placed
              on a secondary index. If there is no primary key,
      <code class="literal">
       LOCK_DATA
      </code>
      shows either the key values
              of a selected unique index or the unique
      <code class="literal">
       InnoDB
      </code>
      internal row ID number,
              according to the rules governing
      <code class="literal">
       InnoDB
      </code>
      clustered index use (see
      <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
       Section 17.6.2.1, “Clustered and Secondary Indexes”
      </a>
      ).
      <code class="literal">
       LOCK_DATA
      </code>
      reports
      <span class="quote">
       “
       <span class="quote">
        supremum
              pseudo-record
       </span>
       ”
      </span>
      for a lock taken on a supremum
              pseudo-record. If the page containing the locked record is
              not in the buffer pool because it was written to disk
              while the lock was held,
      <code class="literal">
       InnoDB
      </code>
      does
              not fetch the page from disk. Instead,
      <code class="literal">
       LOCK_DATA
      </code>
      reports
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   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 has these
          indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Primary key on (
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       ENGINE_TRANSACTION_ID
      </code>
      ,
      <code class="literal">
       ENGINE
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       THREAD_ID
      </code>
      ,
      <code class="literal">
       EVENT_ID
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       OBJECT_SCHEMA
      </code>
      ,
      <code class="literal">
       OBJECT_NAME
      </code>
      ,
      <code class="literal">
       PARTITION_NAME
      </code>
      ,
      <code class="literal">
       SUBPARTITION_NAME
      </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-locks-table.html" title="29.12.13.1 The data_locks Table">
    <code class="literal">
     data_locks
    </code>
   </a>
   table.
  </p>
 </div>
 <br/>
</div>
