Collect information about locks and transactions using P_S

[Sveta Smirnova]

Performance Schema allows to collect information about metadata, table locks as well as on server level transaction. Please collect it if enabled:

select t.processlist_id, ml.* from performance_schema.metadata_locks ml join performance_schema.threads t on (ml.owner_thread_id=t.thread_id);

select t.processlist_id, th.* from performance_schema.table_handles th left join performance_schema.threads t on (th.owner_thread_id=t.thread_id);

select t.processlist_id, et.* from performance_schema.events_transactions_current et join performance_schema.threads t using(thread_id);

select t.processlist_id, et.* from performance_schema.events_transactions_history_long et join performance_schema.threads t using(thread_id);

[Sveta Smirnova (svetasmirnova)]

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%';

[Agustín (agustin-gallego)]

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);
+----------------+-------------+--------------------+--------------------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+----------------+-------------+--------------------+--------------------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
...
| 3 | TABLE | test | table_low_priority_write | 140316715851664 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6030 | 28 | 12 |
| 3 | TABLE | test | table_read | 140316725468352 | SHARED_READ_ONLY | TRANSACTION | GRANTED | sql_parse.cc:6030 | 28 | 12 |
| 3 | TABLE | test | table_write | 140316725468448 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6030 | 28 | 12 |
| 3 | TABLE | test | table_read_local | 140316715910512 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6030 | 28 | 12 |
...

mysql [localhost] {msandbox} (performance_schema) > select t.processlist_id, th.* from performance_schema.table_handles th left join performance_schema.threads t on (t
+----------------+-------------+---------------+--------------------------+-----------------------+-----------------+----------------+---------------+----------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+----------------+-------------+---------------+--------------------------+-----------------------+...

[Agustín (agustin-gallego)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1396