应用-找出谁持有表级锁

    表级锁对应的instruments(wait/lock/table/sql/handler)默认已开启，对应的consumers为performance_schema.table_handlers，在setup_consumers中只受全局配置项global_instrumentation控制，默认已开启。所以默认情况下只要设置系统配置参数performance_schema=ON即可。下面通过一个示例来演示如何找出谁持有表级锁。

会话一：

use test;
select connection_id();
lock table sbtest1 read;

 

会话二：

use test;
select connection_id();
update sbtest1 set pad='xxx' where id = 1; # 被阻塞

 

第三个会话show processlist

mysql> show processlist;
+------+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------------------+
| Id   | User | Host      | db                 | Command | Time | State                           | Info                                      |
+------+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------------------+
| 1031 | root | localhost | performance_schema | Query   |    0 | starting                        | show processlist                          |
| 1099 | root | localhost | test               | Sleep   |  111 |                                 | NULL                                      |
| 1100 | root | localhost | test               | Query   |   84 | Waiting for table metadata lock | update sbtest1 set pad='xxx' where id = 1 |
+------+------+-----------+--------------------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

 

查询metadata_locks表信息

use performance_schema;
select p.THREAD_ID,
       l.ID as processlist_id,
       p.event_name,
       p.TIMER_START,
       round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)",
       p.CURRENT_SCHEMA,
       p.SQL_TEXT,
       l.host,
       l.DB,
       l.STATE,
       ml.LOCK_TYPE,
       ml.LOCK_DURATION,
       ml.LOCK_STATUS,
       ml.OWNER_EVENT_ID
from events_statements_current p
         inner join metadata_locks ml
         inner join information_schema.PROCESSLIST l
         on ml.OWNER_THREAD_ID = p.THREAD_ID and sys.ps_thread_id(l.id) = ml.OWNER_THREAD_ID
        and ml.OWNER_THREAD_ID != sys.ps_thread_id(connection_id());
+-----------+----------------+----------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+
| THREAD_ID | processlist_id | event_name           | TIMER_START         | TIMER_WAIT(s) | CURRENT_SCHEMA | SQL_TEXT                                  | host      | DB   | STATE                           | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | OWNER_EVENT_ID |
+-----------+----------------+----------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+
|      1124 |           1099 | statement/sql/select | 1506940469679980000 |          0.00 | test           | select connection_id()                    | localhost | test |                                 | SHARED_READ_ONLY    | TRANSACTION   | GRANTED     |           1903 |
|      1125 |           1100 | statement/sql/update | 1506967786060538000 |          4.47 | test           | update sbtest1 set pad='xxx' where id = 1 | localhost | test | Waiting for table metadata lock | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |            864 |
|      1125 |           1100 | statement/sql/update | 1506967786060538000 |          4.47 | test           | update sbtest1 set pad='xxx' where id = 1 | localhost | test | Waiting for table metadata lock | SHARED_WRITE        | TRANSACTION   | PENDING     |            864 |
+-----------+----------------+----------------------+---------------------+---------------+----------------+-------------------------------------------+-----------+------+---------------------------------+---------------------+---------------+-------------+----------------+
3 rows in set (0.00 sec)

 

找出持有标记锁的脚本

select /*找出持有标记锁的脚本*/
       p.THREAD_ID,
       l.ID as processlist_id,
       p.event_name,
       p.TIMER_START,
       round(p.TIMER_WAIT / 1000 / 1000 / 1000 /1000,2) as "TIMER_WAIT(s)",
       p.CURRENT_SCHEMA,
       p.SQL_TEXT,
       l.host,
       l.DB,
       l.STATE,
       ml.LOCK_TYPE,
       ml.LOCK_DURATION,
       ml.LOCK_STATUS,
       ml.OWNER_EVENT_ID
from events_statements_current p
         inner join metadata_locks ml
         inner join information_schema.PROCESSLIST l
         -- 增加过滤表解锁会记录在table_handles
         inner join table_handles th   
         on ml.OWNER_THREAD_ID = p.THREAD_ID
         and th.OWNER_THREAD_ID = ml.OWNER_THREAD_ID
         and sys.ps_thread_id(l.id) = ml.OWNER_THREAD_ID and th.OWNER_THREAD_ID !=0
         and ml.OWNER_THREAD_ID != sys.ps_thread_id(connection_id());