<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="performance-schema-obtaining-parent-events">
      </a>
      29.19.2 Obtaining Parent Event Information
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045064190224">
  </a>
  <a class="indexterm" name="idm46045064188736">
  </a>
  <a class="indexterm" name="idm46045064187248">
  </a>
  <a class="indexterm" name="idm46045064185760">
  </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. Rows of this table have a
   <code class="literal">
    THREAD_ID
   </code>
   column indicating the thread ID of
        the session that owns the lock, and an
   <code class="literal">
    EVENT_ID
   </code>
   column indicating 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: disc; ">
    <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. The relation is based on a nested set data model,
        so the join has several clauses. Given parent and child tables
        represented by
   <code class="literal">
    parent
   </code>
   and
   <code class="literal">
    child
   </code>
   , respectively, the join looks like
        this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90011211"><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">WHERE</span>
  parent<span class="token punctuation">.</span>THREAD_ID <span class="token operator">=</span> child<span class="token punctuation">.</span>THREAD_ID        <span class="token comment" spellcheck="true">/* 1 */</span>
  <span class="token operator">AND</span> parent<span class="token punctuation">.</span>EVENT_ID <span class="token operator">&lt;</span> child<span class="token punctuation">.</span>EVENT_ID      <span class="token comment" spellcheck="true">/* 2 */</span>
  <span class="token operator">AND</span> <span class="token punctuation">(</span>
    child<span class="token punctuation">.</span>EVENT_ID <span class="token operator">&lt;=</span> parent<span class="token punctuation">.</span>END_EVENT_ID   <span class="token comment" spellcheck="true">/* 3a */</span>
    <span class="token operator">OR</span> parent<span class="token punctuation">.</span>END_EVENT_ID <span class="token operator">IS</span> <span class="token boolean">NULL</span>          <span class="token comment" spellcheck="true">/* 3b */</span>
  <span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   The conditions for the join are:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      The parent and child events are in the same thread.
     </p>
    </li>
    <li class="listitem">
     <p>
      The child event begins after the parent event, so its
      <code class="literal">
       EVENT_ID
      </code>
      value is greater than that of
            the parent.
     </p>
    </li>
    <li class="listitem">
     <p>
      The parent event has either completed or is still running.
     </p>
    </li>
   </ol>
  </div>
  <p>
   To find lock information,
   <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>
   is the table containing
        child events.
  </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 shows only
        existing locks, so these considerations apply regarding which
        table contains the parent event:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For transactions, the only choice is
      <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>
      . If
            a transaction is completed, it may be in the transaction
            history tables, but the locks are gone already.
     </p>
    </li>
    <li class="listitem">
     <p>
      For statements, it all depends on whether the statement that
            took a lock is a statement in a transaction that has already
            completed (use
      <a class="link" href="performance-schema-events-statements-history-table.html" title="29.12.6.2 The events_statements_history Table">
       <code class="literal">
        events_statements_history
       </code>
      </a>
      ) or
            the statement is still running (use
      <a class="link" href="performance-schema-events-statements-current-table.html" title="29.12.6.1 The events_statements_current Table">
       <code class="literal">
        events_statements_current
       </code>
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      For stages, the logic is similar to that for statements; use
      <a class="link" href="performance-schema-events-stages-history-table.html" title="29.12.5.2 The events_stages_history Table">
       <code class="literal">
        events_stages_history
       </code>
      </a>
      or
      <a class="link" href="performance-schema-events-stages-current-table.html" title="29.12.5.1 The events_stages_current Table">
       <code class="literal">
        events_stages_current
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For waits, the logic is similar to that for statements; use
      <a class="link" href="performance-schema-events-waits-history-table.html" title="29.12.4.2 The events_waits_history Table">
       <code class="literal">
        events_waits_history
       </code>
      </a>
      or
      <a class="link" href="performance-schema-events-waits-current-table.html" title="29.12.4.1 The events_waits_current Table">
       <code class="literal">
        events_waits_current
       </code>
      </a>
      . However,
            so many waits are recorded that the wait that caused a lock
            is most likely gone from the history tables already.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Wait, stage, and statement events disappear quickly from the
        history. If a statement that executed a long time ago took a
        lock but is in a still-open transaction, it might not be
        possible to find the statement, but it is possible to find the
        transaction.
  </p>
  <p>
   This is why the nested set data model works better for locating
        parent events. Following links in a parent/child relationship
        (data lock -&gt; parent wait -&gt; parent stage -&gt; parent
        transaction) does not work well when intermediate nodes are
        already gone from the history tables.
  </p>
  <p>
   The following scenario illustrates how to find the parent
        transaction of a statement in which a lock was taken:
  </p>
  <p>
   Session A:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36830290"><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 punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">]</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> pk <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token punctuation">[</span><span class="token number">3</span><span class="token punctuation">]</span> <span class="token keyword">SELECT</span> <span class="token string">'Hello, world'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Session B:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33575034"><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">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>events_transactions_current <span class="token keyword">AS</span> parent
  <span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> performance_schema<span class="token punctuation">.</span>data_locks <span class="token keyword">AS</span> child
<span class="token keyword">WHERE</span>
  parent<span class="token punctuation">.</span>THREAD_ID <span class="token operator">=</span> child<span class="token punctuation">.</span>THREAD_ID
  <span class="token operator">AND</span> parent<span class="token punctuation">.</span>EVENT_ID <span class="token operator">&lt;</span> child<span class="token punctuation">.</span>EVENT_ID
  <span class="token operator">AND</span> <span class="token punctuation">(</span>
    child<span class="token punctuation">.</span>EVENT_ID <span class="token operator">&lt;=</span> parent<span class="token punctuation">.</span>END_EVENT_ID
    <span class="token operator">OR</span> parent<span class="token punctuation">.</span>END_EVENT_ID <span class="token operator">IS</span> <span class="token boolean">NULL</span>
  <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The query for session B should show statement [2] as owning a
        data lock on the record with
   <code class="literal">
    pk=1
   </code>
   .
  </p>
  <p>
   If session A executes more statements, [2] fades out of the
        history table.
  </p>
  <p>
   The query should show the transaction that started in [1],
        regardless of how many statements, stages, or waits were
        executed.
  </p>
  <p>
   To see more data, you can also use the
   <code class="literal">
    events_
    <em class="replaceable">
     <code>
      xxx
     </code>
    </em>
    _history_long
   </code>
   tables, except for transactions, assuming no other query runs in
        the server (so that history is preserved).
  </p>
 </div>
 <br/>
</div>
