Movatterモバイル変換

Thedata_locks table shows data locks held and requested. Rows of this table have aTHREAD_ID column indicating the thread ID of the session that owns the lock, and anEVENT_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:

To obtain details about the parent event, join theTHREAD_ID andEVENT_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 byparent andchild, respectively, the join looks like this:

WHERE  parent.THREAD_ID = child.THREAD_ID        /* 1 */  AND parent.EVENT_ID < child.EVENT_ID      /* 2 */  AND (    child.EVENT_ID <= parent.END_EVENT_ID   /* 3a */    OR parent.END_EVENT_ID IS NULL          /* 3b */  )

The conditions for the join are:

To find lock information,data_locks is the table containing child events.

Thedata_locks table shows only existing locks, so these considerations apply regarding which table contains the parent event:

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:

[1] START TRANSACTION;[2] SELECT * FROM t1 WHERE pk = 1;[3] SELECT 'Hello, world';

Session B:

SELECT ...FROM performance_schema.events_transactions_current AS parent  INNER JOIN performance_schema.data_locks AS childWHERE  parent.THREAD_ID = child.THREAD_ID  AND parent.EVENT_ID < child.EVENT_ID  AND (    child.EVENT_ID <= parent.END_EVENT_ID    OR parent.END_EVENT_ID IS NULL  );

The query for session B should show statement [2] as owning a data lock on the record withpk=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 theevents_xxx_history_long tables, except for transactions, assuming no other query runs in the server (so that history is preserved).