Rechercher dans le manuel MySQL
26.19.2 Obtaining Parent Event Information
The data_locks
table shows data
locks held and requested. Rows of this table have a
THREAD_ID
column indicating the thread ID of
the session that owns the lock, and an
EVENT_ID
column indicating the Performance
Schema event that caused the lock. Tuples of
(THREAD_ID
, EVENT_ID
)
values implicitly identify a parent event in other Performance
Schema tables:
The parent wait event in the
events_waits_
tablesxxx
The parent stage event in the
events_stages_
tablesxxx
The parent statement event in the
events_statements_
tablesxxx
The parent transaction event in the
events_transactions_current
table
To obtain details about the parent event, join the
THREAD_ID
and EVENT_ID
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 parent
and
child
, respectively, the join looks like
this:
- parent.THREAD_ID = child.THREAD_ID /* 1 */
- AND (
- child.EVENT_ID <= parent.END_EVENT_ID /* 3a */
- )
The condititions for the join are:
The parent and child events are in the same thread.
The child event begins after the parent event, so its
EVENT_ID
value is greater than that of the parent.The parent event has either completed or is still running.
To find lock information,
data_locks
is the table containing
child events.
The data_locks
table shows only
existing locks, so these considerations apply regarding which
table contains the parent event:
For transactions, the only choice is
events_transactions_current
. If a transaction is completed, it may be in the transaction history tables, but the locks are gone already.For statements, it all depends on whether the statement that took a lock is a statement in a transaction that has already completed (use
events_statements_history
) or the statement is still running (useevents_statements_current
).For stages, the logic is similar to that for statements; use
events_stages_history
orevents_stages_current
.For waits, the logic is similar to that for statements; use
events_waits_history
orevents_waits_current
. However, so many waits are recorded that the wait that caused a lock is most likely gone from the history tables already.
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.
This is why the nested set data model works better for locating parent events. Following links in a parent/child relationship (data lock -> parent wait -> parent stage -> parent transaction) does not work well when intermediate nodes are already gone from the history tables.
The following scenario illustrates how to find the parent transaction of a statement in which a lock was taken:
Session A:
Session B:
The query for session B should show statement [2] as owning a
data lock on the record with pk=1
.
If session A executes more statements, [2] fades out of the history table.
The query should show the transaction that started in [1], regardless of how many statements, stages, or waits were executed.
To see more data, you can also use the
events_
tables, except for transactions, assuming no other query runs in
the server (so that history is preserved).
xxx
_history_long
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-performance-schema-obtaining-parent-events.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.