<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="performance-schema-metadata-locks-table">
      </a>
      29.12.13.3 The metadata_locks Table
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045070362256">
  </a>
  <a class="indexterm" name="idm46045070360768">
  </a>
  <a class="indexterm" name="idm46045070359280">
  </a>
  <p>
   MySQL uses metadata locking to manage concurrent access to
          database objects and to ensure data consistency; see
   <a class="xref" href="metadata-locking.html" title="10.11.4 Metadata Locking">
    Section 10.11.4, “Metadata Locking”
   </a>
   . Metadata locking applies
          not just to tables, but also to schemas, stored programs
          (procedures, functions, triggers, scheduled events),
          tablespaces, user locks acquired with the
   <a class="link" href="locking-functions.html#function_get-lock">
    <code class="literal">
     GET_LOCK()
    </code>
   </a>
   function (see
   <a class="xref" href="locking-functions.html" title="14.14 Locking Functions">
    Section 14.14, “Locking Functions”
   </a>
   ), and locks acquired with
          the locking service described in
   <a class="xref" href="locking-service.html" title="7.6.9.1 The Locking Service">
    Section 7.6.9.1, “The Locking Service”
   </a>
   .
  </p>
  <p>
   The Performance Schema exposes metadata lock information
          through the
   <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_locks
    </code>
   </a>
   table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Locks that have been granted (shows which sessions own
              which current metadata locks).
     </p>
    </li>
    <li class="listitem">
     <p>
      Locks that have been requested but not yet granted (shows
              which sessions are waiting for which metadata locks).
     </p>
    </li>
    <li class="listitem">
     <p>
      Lock requests that have been killed by the deadlock
              detector.
     </p>
    </li>
    <li class="listitem">
     <p>
      Lock requests that have timed out and are waiting for the
              requesting session's lock request to be discarded.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This information enables you to understand metadata lock
          dependencies between sessions. You can see not only which lock
          a session is waiting for, but which session currently holds
          that lock.
  </p>
  <p>
   The
   <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_locks
    </code>
   </a>
   table is read
          only and cannot be updated. It is autosized by default; to
          configure the table size, set the
   <a class="link" href="performance-schema-system-variables.html#sysvar_performance_schema_max_metadata_locks">
    <code class="literal">
     performance_schema_max_metadata_locks
    </code>
   </a>
   system variable at server startup.
  </p>
  <p>
   Metadata lock instrumentation uses the
   <code class="literal">
    wait/lock/metadata/sql/mdl
   </code>
   instrument,
          which is enabled by default.
  </p>
  <p>
   To control metadata lock instrumentation state at server
          startup, use lines like these in your
   <code class="filename">
    my.cnf
   </code>
   file:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Enable:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa72718788"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">performance-schema-instrument</span><span class="token attr-value"><span class="token punctuation">=</span>'wait/lock/metadata/sql/mdl=ON'</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Disable:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa78480354"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">performance-schema-instrument</span><span class="token attr-value"><span class="token punctuation">=</span>'wait/lock/metadata/sql/mdl=OFF'</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   To control metadata lock instrumentation state at runtime,
          update the
   <a class="link" href="performance-schema-setup-instruments-table.html" title="29.12.2.3 The setup_instruments Table">
    <code class="literal">
     setup_instruments
    </code>
   </a>
   table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Enable:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45781601"><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> performance_schema<span class="token punctuation">.</span>setup_instruments
<span class="token keyword">SET</span> ENABLED <span class="token operator">=</span> <span class="token string">'YES'</span><span class="token punctuation">,</span> TIMED <span class="token operator">=</span> <span class="token string">'YES'</span>
<span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">=</span> <span class="token string">'wait/lock/metadata/sql/mdl'</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Disable:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93382232"><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> performance_schema<span class="token punctuation">.</span>setup_instruments
<span class="token keyword">SET</span> ENABLED <span class="token operator">=</span> <span class="token string">'NO'</span><span class="token punctuation">,</span> TIMED <span class="token operator">=</span> <span class="token string">'NO'</span>
<span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">=</span> <span class="token string">'wait/lock/metadata/sql/mdl'</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The Performance Schema maintains
   <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_locks
    </code>
   </a>
   table content as
          follows, using the
   <code class="literal">
    LOCK_STATUS
   </code>
   column to
          indicate the status of each lock:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When a metadata lock is requested and obtained
              immediately, a row with a status of
      <code class="literal">
       GRANTED
      </code>
      is inserted.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a metadata lock is requested and not obtained
              immediately, a row with a status of
      <code class="literal">
       PENDING
      </code>
      is inserted.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a metadata lock previously requested is granted, its
              row status is updated to
      <code class="literal">
       GRANTED
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When a metadata lock is released, its row is deleted.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a pending lock request is canceled by the deadlock
              detector to break a deadlock
              (
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_lock_deadlock" target="_top">
       <code class="literal">
        ER_LOCK_DEADLOCK
       </code>
      </a>
      ), its
              row status is updated from
      <code class="literal">
       PENDING
      </code>
      to
      <code class="literal">
       VICTIM
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When a pending lock request times out
              (
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_lock_wait_timeout" target="_top">
       <code class="literal">
        ER_LOCK_WAIT_TIMEOUT
       </code>
      </a>
      ),
              its row status is updated from
      <code class="literal">
       PENDING
      </code>
      to
      <code class="literal">
       TIMEOUT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      When granted lock or pending lock request is killed, its
              row status is updated from
      <code class="literal">
       GRANTED
      </code>
      or
      <code class="literal">
       PENDING
      </code>
      to
      <code class="literal">
       KILLED
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       VICTIM
      </code>
      ,
      <code class="literal">
       TIMEOUT
      </code>
      ,
              and
      <code class="literal">
       KILLED
      </code>
      status values are brief and
              signify that the lock row is about to be deleted.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       PRE_ACQUIRE_NOTIFY
      </code>
      and
      <code class="literal">
       POST_RELEASE_NOTIFY
      </code>
      status values are
              brief and signify that the metadata locking subsubsystem
              is notifying interested storage engines while entering
              lock acquisition operations or leaving lock release
              operations.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_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">
       OBJECT_TYPE
      </code>
     </p>
     <p>
      The type of lock used in the metadata lock subsystem. The
              value is one of
      <code class="literal">
       GLOBAL
      </code>
      ,
      <code class="literal">
       SCHEMA
      </code>
      ,
      <code class="literal">
       TABLE
      </code>
      ,
      <code class="literal">
       FUNCTION
      </code>
      ,
      <code class="literal">
       PROCEDURE
      </code>
      ,
      <code class="literal">
       TRIGGER
      </code>
      (currently unused),
      <code class="literal">
       EVENT
      </code>
      ,
      <code class="literal">
       COMMIT
      </code>
      ,
      <code class="literal">
       USER LEVEL LOCK
      </code>
      ,
      <code class="literal">
       TABLESPACE
      </code>
      ,
      <code class="literal">
       BACKUP
              LOCK
      </code>
      , or
      <code class="literal">
       LOCKING SERVICE
      </code>
      .
     </p>
     <p>
      A value of
      <code class="literal">
       USER LEVEL LOCK
      </code>
      indicates a
              lock acquired with
      <a class="link" href="locking-functions.html#function_get-lock">
       <code class="literal">
        GET_LOCK()
       </code>
      </a>
      . A value of
      <code class="literal">
       LOCKING SERVICE
      </code>
      indicates a lock
              acquired with the locking service described in
      <a class="xref" href="locking-service.html" title="7.6.9.1 The Locking Service">
       Section 7.6.9.1, “The Locking Service”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_SCHEMA
      </code>
     </p>
     <p>
      The schema that contains the object.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_NAME
      </code>
     </p>
     <p>
      The name of the instrumented object.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OBJECT_INSTANCE_BEGIN
      </code>
     </p>
     <p>
      The address in memory of the instrumented object.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_TYPE
      </code>
     </p>
     <p>
      The lock type from the metadata lock subsystem. The value
              is one of
      <code class="literal">
       INTENTION_EXCLUSIVE
      </code>
      ,
      <code class="literal">
       SHARED
      </code>
      ,
      <code class="literal">
       SHARED_HIGH_PRIO
      </code>
      ,
      <code class="literal">
       SHARED_READ
      </code>
      ,
      <code class="literal">
       SHARED_WRITE
      </code>
      ,
      <code class="literal">
       SHARED_UPGRADABLE
      </code>
      ,
      <code class="literal">
       SHARED_NO_WRITE
      </code>
      ,
      <code class="literal">
       SHARED_NO_READ_WRITE
      </code>
      , or
      <code class="literal">
       EXCLUSIVE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_DURATION
      </code>
     </p>
     <p>
      The lock duration from the metadata lock subsystem. The
              value is one of
      <code class="literal">
       STATEMENT
      </code>
      ,
      <code class="literal">
       TRANSACTION
      </code>
      , or
      <code class="literal">
       EXPLICIT
      </code>
      . The
      <code class="literal">
       STATEMENT
      </code>
      and
      <code class="literal">
       TRANSACTION
      </code>
      values signify locks that
              are released implicitly at statement or transaction end,
              respectively. The
      <code class="literal">
       EXPLICIT
      </code>
      value
              signifies locks that survive statement or transaction end
              and are released by explicit action, such as global locks
              acquired with
      <a class="link" href="flush.html#flush-tables-with-read-lock">
       <code class="literal">
        FLUSH TABLES WITH READ
              LOCK
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK_STATUS
      </code>
     </p>
     <p>
      The lock status from the metadata lock subsystem. The
              value is one of
      <code class="literal">
       PENDING
      </code>
      ,
      <code class="literal">
       GRANTED
      </code>
      ,
      <code class="literal">
       VICTIM
      </code>
      ,
      <code class="literal">
       TIMEOUT
      </code>
      ,
      <code class="literal">
       KILLED
      </code>
      ,
      <code class="literal">
       PRE_ACQUIRE_NOTIFY
      </code>
      , or
      <code class="literal">
       POST_RELEASE_NOTIFY
      </code>
      . The Performance
              Schema assigns these values as described previously.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SOURCE
      </code>
     </p>
     <p>
      The name of the source file containing the instrumented
              code that produced the event and the line number in the
              file at which the instrumentation occurs. This enables you
              to check the source to determine exactly what code is
              involved.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OWNER_THREAD_ID
      </code>
     </p>
     <p>
      The thread requesting a metadata lock.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       OWNER_EVENT_ID
      </code>
     </p>
     <p>
      The event requesting a metadata lock.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="performance-schema-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_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">
       OBJECT_INSTANCE_BEGIN
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       OBJECT_TYPE
      </code>
      ,
      <code class="literal">
       OBJECT_SCHEMA
      </code>
      ,
      <code class="literal">
       OBJECT_NAME
      </code>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      Index on (
      <code class="literal">
       OWNER_THREAD_ID
      </code>
      ,
      <code class="literal">
       OWNER_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-metadata-locks-table.html" title="29.12.13.3 The metadata_locks Table">
    <code class="literal">
     metadata_locks
    </code>
   </a>
   table.
  </p>
 </div>
 <br/>
</div>
