**Comment from Launchpad by: Agustín on: 20-03-2017 18:36:42

For the last two queries:

events_transactions_current -> https://dev.mysql.com/doc/refman/5.7/en/performance-schema-transaction-tables.html

mysql> UPDATE setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%events_transactions%';

In one session run:

use test;
create table t1 (id int) engine=innodb;

set autocommit=0;
start transaction;
insert into t1 values (1);

In another session, run the first query:

select t.processlist_id, et.* from performance_schema.events_transactions_current et join performance_schema.threads t using(thread_id)\G
*************************** 1. row ***************************
processlist_id: 1878
THREAD_ID: 1903
EVENT_ID: 13
END_EVENT_ID: NULL
EVENT_NAME: transaction
STATE: ACTIVE
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE: transaction.cc:209
TIMER_START: 2906342199742000
TIMER_END: 3219870413619000
TIMER_WAIT: 313528213877000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 12
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

– note that the state should be ACTIVE

then, go back to the first session, and run:

mysql> commit;

and run the second query:

select t.processlist_id, et.* from performance_schema.events_transactions_current et join performance_schema.threads t using(thread_id)\G
*************************** 1. row ***************************
processlist_id: 1878
THREAD_ID: 1903
EVENT_ID: 13
END_EVENT_ID: 15
EVENT_NAME: transaction
STATE: COMMITTED
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE: transaction.cc:209
TIMER_START: 2906342199742000
TIMER_END: 3245593266177000
TIMER_WAIT: 339251066435000
ACCESS_MODE: READ WRITE
ISOLATION_LEVEL: REPEATABLE READ
AUTOCOMMIT: NO
NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
NUMBER_OF_RELEASE_SAVEPOINT: 0
OBJECT_INSTANCE_BEGIN: NULL
NESTING_EVENT_ID: 12
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)

– note that the state should be COMMITTED

**Comment from Launchpad by: Agustín on: 20-03-2017 18:29:23

Adding notes for tests as discussed with Carlos.

For the first two queries:

metadata_locks -> https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
table_handles -> https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/table-handles-table.html

Add the following to the my.cnf and restart:

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument='transaction=ON'

Run the following in one session:

use test;
create table table_read (id int) engine=innodb;
create table table_read_local (id int) engine=innodb;
create table table_write (id int) engine=innodb;
create table table_low_priority_write (id int) engine=innodb;
LOCK TABLES table_read READ, table_read_local READ LOCAL, table_write WRITE, table_low_priority_write LOW_PRIORITY WRITE;
– leave the session here

In another session, run the queries, and you should see output like:

mysql [localhost] {msandbox} (performance_schema) > select t.processlist_id, ml.* from performance_schema.metadata_locks ml join performance_schema.threads t on (ml.owner_thread_id=t.thread_id);
----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
...

...

mysql [localhost] {msandbox} (performance_schema) > select t.processlist_id, th.* from performance_schema.table_handles th left join performance_schema.threads t on (t
----------------------------+-------------------------------------------------------------------------------------------------------------------------------+

----------------------------+-------------------------------------------------------------------------------------------------------------------------------+

...

**Comment from Launchpad by: Sveta Smirnova on: 24-02-2017 12:04:45

In order to see these data you need to enable instrumentation.

For metadata locks this is:

UPDATE performance_schema.setup_instruments set ENABLED='YES' where name='wait/lock/metadata/sql/mdl';

For transactios this is:

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'transaction';
UPDATE setup_consumers SET ENABLED='YES' WHERE NAME LIKE ' events_transactions%';