<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-trx-table">
      </a>
      28.4.28 The INFORMATION_SCHEMA INNODB_TRX Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076250352">
  </a>
  <p>
   The
   <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 provides
      information about every transaction currently executing inside
   <code class="literal">
    InnoDB
   </code>
   , including whether the transaction is
      waiting for a lock, when the transaction started, and the SQL
      statement the transaction is executing, if any.
  </p>
  <p>
   For usage information, see
   <a class="xref" href="innodb-information-schema-examples.html" title="17.15.2.1 Using InnoDB Transaction and Locking Information">
    Section 17.15.2.1, “Using InnoDB Transaction and Locking Information”
   </a>
   .
  </p>
  <p>
   The
   <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 has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ID
      </code>
     </p>
     <p>
      A unique transaction ID number, internal to
      <code class="literal">
       InnoDB
      </code>
      . These IDs are not created for
          transactions that are read only and nonlocking. For details,
          see
      <a class="xref" href="innodb-performance-ro-txn.html" title="10.5.3 Optimizing InnoDB Read-Only Transactions">
       Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_WEIGHT
      </code>
     </p>
     <p>
      The weight of a transaction, reflecting (but not necessarily
          the exact count of) the number of rows altered and the number
          of rows locked by the transaction. To resolve a deadlock,
      <code class="literal">
       InnoDB
      </code>
      selects the transaction with the
          smallest weight as the
      <span class="quote">
       “
       <span class="quote">
        victim
       </span>
       ”
      </span>
      to roll back.
          Transactions that have changed nontransactional tables are
          considered heavier than others, regardless of the number of
          altered and locked rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_STATE
      </code>
     </p>
     <p>
      The transaction execution state. Permitted values are
      <code class="literal">
       RUNNING
      </code>
      ,
      <code class="literal">
       LOCK WAIT
      </code>
      ,
      <code class="literal">
       ROLLING BACK
      </code>
      , and
      <code class="literal">
       COMMITTING
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_STARTED
      </code>
     </p>
     <p>
      The transaction start time.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_REQUESTED_LOCK_ID
      </code>
     </p>
     <p>
      The ID of the lock the transaction is currently waiting for,
          if
      <code class="literal">
       TRX_STATE
      </code>
      is
      <code class="literal">
       LOCK
          WAIT
      </code>
      ; otherwise
      <code class="literal">
       NULL
      </code>
      . To obtain
          details about the lock, join this column with the
      <code class="literal">
       ENGINE_LOCK_ID
      </code>
      column of the Performance
          Schema
      <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">
       TRX_WAIT_STARTED
      </code>
     </p>
     <p>
      The time when the transaction started waiting on the lock, if
      <code class="literal">
       TRX_STATE
      </code>
      is
      <code class="literal">
       LOCK WAIT
      </code>
      ;
          otherwise
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_MYSQL_THREAD_ID
      </code>
     </p>
     <p>
      The MySQL thread ID. To obtain details about the thread, join
          this column with the
      <code class="literal">
       ID
      </code>
      column of the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-processlist-table.html" title="28.3.23 The INFORMATION_SCHEMA PROCESSLIST Table">
       <code class="literal">
        PROCESSLIST
       </code>
      </a>
      table, but see
      <a class="xref" href="innodb-information-schema-internal-data.html" title="17.15.2.3 Persistence and Consistency of InnoDB Transaction and Locking Information">
       Section 17.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking
        Information”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_QUERY
      </code>
     </p>
     <p>
      The SQL statement that is being executed by the transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_OPERATION_STATE
      </code>
     </p>
     <p>
      The transaction's current operation, if any; otherwise
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_TABLES_IN_USE
      </code>
     </p>
     <p>
      The number of
      <code class="literal">
       InnoDB
      </code>
      tables used while
          processing the current SQL statement of this transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_TABLES_LOCKED
      </code>
     </p>
     <p>
      The number of
      <code class="literal">
       InnoDB
      </code>
      tables that the
          current SQL statement has row locks on. (Because these are row
          locks, not table locks, the tables can usually still be read
          from and written to by multiple transactions, despite some
          rows being locked.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_LOCK_STRUCTS
      </code>
     </p>
     <p>
      The number of locks reserved by the transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_LOCK_MEMORY_BYTES
      </code>
     </p>
     <p>
      The total size taken up by the lock structures of this
          transaction in memory.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ROWS_LOCKED
      </code>
     </p>
     <p>
      The approximate number or rows locked by this transaction. The
          value might include delete-marked rows that are physically
          present but not visible to the transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ROWS_MODIFIED
      </code>
     </p>
     <p>
      The number of modified and inserted rows in this transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_CONCURRENCY_TICKETS
      </code>
     </p>
     <p>
      A value indicating how much work the current transaction can
          do before being swapped out, as specified by the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_concurrency_tickets">
       <code class="literal">
        innodb_concurrency_tickets
       </code>
      </a>
      system variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ISOLATION_LEVEL
      </code>
     </p>
     <p>
      The isolation level of the current transaction.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_UNIQUE_CHECKS
      </code>
     </p>
     <p>
      Whether unique checks are turned on or off for the current
          transaction. For example, they might be turned off during a
          bulk data load.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_FOREIGN_KEY_CHECKS
      </code>
     </p>
     <p>
      Whether foreign key checks are turned on or off for the
          current transaction. For example, they might be turned off
          during a bulk data load.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_LAST_FOREIGN_KEY_ERROR
      </code>
     </p>
     <p>
      The detailed error message for the last foreign key error, if
          any; otherwise
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ADAPTIVE_HASH_LATCHED
      </code>
     </p>
     <p>
      Whether the adaptive hash index is locked by the current
          transaction. When the adaptive hash index search system is
          partitioned, a single transaction does not lock the entire
          adaptive hash index. Adaptive hash index partitioning is
          controlled by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_hash_index_parts">
       <code class="literal">
        innodb_adaptive_hash_index_parts
       </code>
      </a>
      ,
          which is set to 8 by default.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_ADAPTIVE_HASH_TIMEOUT
      </code>
     </p>
     <p>
      Deprecated in MySQL 5.7.8. Always returns 0.
     </p>
     <p>
      Whether to relinquish the search latch immediately for the
          adaptive hash index, or reserve it across calls from MySQL.
          When there is no adaptive hash index contention, this value
          remains zero and statements reserve the latch until they
          finish. During times of contention, it counts down to zero,
          and statements release the latch immediately after each row
          lookup. When the adaptive hash index search system is
          partitioned (controlled by
      <a class="link" href="innodb-parameters.html#sysvar_innodb_adaptive_hash_index_parts">
       <code class="literal">
        innodb_adaptive_hash_index_parts
       </code>
      </a>
      ),
          the value remains 0.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_IS_READ_ONLY
      </code>
     </p>
     <p>
      A value of 1 indicates the transaction is read only.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_AUTOCOMMIT_NON_LOCKING
      </code>
     </p>
     <p>
      A value of 1 indicates the transaction is a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement that does not
          use the
      <code class="literal">
       FOR UPDATE
      </code>
      or
      <code class="literal">
       LOCK IN
          SHARED MODE
      </code>
      clauses, and is executing with
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit
       </code>
      </a>
      enabled so that
          the transaction contains only this one statement. When this
          column and
      <code class="literal">
       TRX_IS_READ_ONLY
      </code>
      are both 1,
      <code class="literal">
       InnoDB
      </code>
      optimizes the transaction to reduce
          the overhead associated with transactions that change table
          data.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TRX_SCHEDULE_WEIGHT
      </code>
     </p>
     <p>
      The transaction schedule weight assigned by the
          Contention-Aware Transaction Scheduling (CATS) algorithm to
          transactions waiting for a lock. The value is relative to the
          values of other transactions. A higher value has a greater
          weight. A value is computed only for transactions in a
      <code class="literal">
       LOCK WAIT
      </code>
      state, as reported by the
      <code class="literal">
       TRX_STATE
      </code>
      column. A NULL value is reported
          for transactions that are not waiting for a lock. The
      <code class="literal">
       TRX_SCHEDULE_WEIGHT
      </code>
      value is different from
          the
      <code class="literal">
       TRX_WEIGHT
      </code>
      value, which is computed by
          a different algorithm for a different purpose.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076161248">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76534383"><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> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TRX\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>
                    trx_id<span class="token punctuation">:</span> 1510
                 trx_state<span class="token punctuation">:</span> RUNNING
               trx_started<span class="token punctuation">:</span> 2014-11-19 13<span class="token punctuation">:</span>24<span class="token punctuation">:</span>40
     trx_requested_lock_id<span class="token punctuation">:</span> NULL
          trx_wait_started<span class="token punctuation">:</span> NULL
                trx_weight<span class="token punctuation">:</span> 586739
       trx_mysql_thread_id<span class="token punctuation">:</span> 2
                 trx_query<span class="token punctuation">:</span> DELETE FROM employees.salaries WHERE salary &gt; 65000
       trx_operation_state<span class="token punctuation">:</span> updating or deleting
         trx_tables_in_use<span class="token punctuation">:</span> 1
         trx_tables_locked<span class="token punctuation">:</span> 1
          trx_lock_structs<span class="token punctuation">:</span> 3003
     trx_lock_memory_bytes<span class="token punctuation">:</span> 450768
           trx_rows_locked<span class="token punctuation">:</span> 1407513
         trx_rows_modified<span class="token punctuation">:</span> 583736
   trx_concurrency_tickets<span class="token punctuation">:</span> 0
       trx_isolation_level<span class="token punctuation">:</span> REPEATABLE READ
         trx_unique_checks<span class="token punctuation">:</span> 1
    trx_foreign_key_checks<span class="token punctuation">:</span> 1
trx_last_foreign_key_error<span class="token punctuation">:</span> NULL
 trx_adaptive_hash_latched<span class="token punctuation">:</span> 0
 trx_adaptive_hash_timeout<span class="token punctuation">:</span> 10000
          trx_is_read_only<span class="token punctuation">:</span> 0
trx_autocommit_non_locking<span class="token punctuation">:</span> 0
       trx_schedule_weight<span class="token punctuation">:</span> NULL</span></code></pre>
  </div>
  <h4>
   <a name="idm46045076156432">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use this table to help diagnose performance problems that
          occur during times of heavy concurrent load. Its contents are
          updated as described in
      <a class="xref" href="innodb-information-schema-internal-data.html" title="17.15.2.3 Persistence and Consistency of InnoDB Transaction and Locking Information">
       Section 17.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking
        Information”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
