修改表的state值_解决MDL锁导致无法操作数据库表的问题

最新推荐文章于 2023-04-19 15:05:11 发布

fiisio

最新推荐文章于 2023-04-19 15:05:11 发布

阅读量431

点赞数

文章标签：修改表的state值

本文链接：https://blog.csdn.net/weixin_31198993/article/details/112588580

版权

背景信息

MYSQL的MDL锁，用于解决或者保证DDL操作与DML操作之间的一致性，但是在部分场景下会出现阻塞，例如执行DML操作时执行ALTER操作、存在长时间查询时执行ALTER操作等等。

表象如下：

出现 Waiting for table metadata lock 且长时间处于等待状态，并阻塞所有后续对表的操作

mysql> show processlist;
+------+-----------------+----------------------+------+---------+---------+---------------------------------+-------------------------------+
| Id   | User            | Host                 | db   | Command | Time    | State                           | Info                          |
+------+-----------------+----------------------+------+---------+---------+---------------------------------+-------------------------------+
|    1 | event_scheduler | localhost            | NULL | Daemon  | 1562471 | Waiting on empty queue          | NULL                          |
| 4280 | root            | 221.228.242.102:5585 | test | Sleep   |    1063 |                                 | NULL                          |
| 4284 | root            | 221.228.242.102:5604 | test | Sleep   |     728 |                                 | NULL                          |
| 4293 | root            | localhost            | test | Sleep   |     154 |                                 | NULL                          |
| 4294 | root            | localhost            | test | Query   |     128 | Waiting for table metadata lock | alter table t drop column d   |
| 4295 | root            | localhost            | test | Query   |       0 | starting                        | show processlist              |
| 4296 | root            | localhost            | test | Query   |       4 | Waiting for table metadata lock | insert into t(id) select null |
+------+-----------------+----------------------+------+---------+---------+---------------------------------+-------------------------------+
7 rows in set (0.03 sec)

MDL锁出现场景

创建、删除索引。
修改表结构。
表维护操作（optimize table、repair table 等）。
删除表。
获取表级写锁。

被锁原因

当前有对表的长时间查询。
显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚。
表上有失败的查询事务。

解决办法

找到魏师傅 MDL 锁的session，kill掉
或 kill 掉DDL语句，等待下一次运维时间变更

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                 and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))
    ) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

使用有权限的用户执行`kill`即可终端问题会话，解除DML锁。

维护建议

在业务低峰期执行相关场景操作，例如创建索引、删除索引等。
开启事务自动提交autocommit。
设置参数lock_wait_timeout为较小值。
考虑使用事件来终止长时间运行的事务，比如下面的例子中会终止执行时间超过60分钟的事务。

create event my_long_running_trx_monitor
on schedule every 60 minute
starts '2015-09-15 11:00:00'
on completion preserve enable do
begin
  declare v_sql varchar(500);
  declare no_more_long_running_trx integer default 0; 
  declare c_tid cursor for
    select concat ('kill ',trx_mysql_thread_id,';') 
    from information_schema.innodb_trx 
    where timestampdiff(minute,trx_started,now()) >= 60;
  declare continue handler for not found
    set no_more_long_running_trx=1;
  open c_tid;
  repeat
    fetch c_tid into v_sql;
 set @v_sql=v_sql;
 prepare stmt from @v_sql;
 execute stmt;
 deallocate prepare stmt;
  until no_more_long_running_trx end repeat;
  close c_tid;
end;

示例

场景1-有对表的长时间查询

data_million_large 当前有 800万+的数据,对表进行一次全表查询肯定是条慢SQL

mysql> show processlist;
+----+-----------------+----------------------+-------+---------+------+---------------------------------+------------------------------------------------------------+
| Id | User            | Host                 | db    | Command | Time | State                           | Info                                                       |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+------------------------------------------------------------+
|  1 | event_scheduler | localhost            | NULL  | Daemon  |  106 | Waiting on empty queue          | NULL                                                       |
|  3 | root            | 221.228.242.102:7645 | ytest | Sleep   |   97 |                                 | NULL                                                       |
|  4 | root            | 221.228.242.102:7646 | ytest | Sleep   |   94 |                                 | NULL                                                       |
|  5 | root            | 221.228.242.102:7647 | ytest | Sleep   |   93 |                                 | NULL                                                       |
|  6 | root            | 221.228.242.102:7648 | ytest | Query   |   90 | Sending to client               | select * from data_million_large                           |
|  7 | root            | 221.228.242.102:7660 | ytest | Query   |   77 | Waiting for table metadata lock | alter table data_million_large add column abcd varchar(10) |
|  8 | root            | 221.228.242.102:7663 | ytest | Sleep   |   40 |                                 | NULL                                                       |
|  9 | root            | 221.228.242.102:7669 | ytest | Sleep   |   55 |                                 | NULL                                                       |
| 10 | root            | 221.228.242.102:7674 | ytest | Query   |   46 | Waiting for table metadata lock | select * from data_million_large limit 1                   |
| 11 | root            | localhost            | NULL  | Query   |    0 | starting                        | show processlist                                           |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+------------------------------------------------------------+
10 rows in set (0.00 sec)

场景2-事务未提交或回滚

mysql> show processlist;
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
| Id | User            | Host                 | db    | Command | Time | State                           | Info                                                      |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
|  1 | event_scheduler | localhost            | NULL  | Daemon  | 1658 | Waiting on empty queue          | NULL                                                      |
| 15 | root            | 221.228.242.102:8652 | ytest | Sleep   |   64 |                                 | NULL                                                      |
| 16 | root            | 221.228.242.102:8669 | ytest | Query   |   23 | Waiting for table metadata lock | alter table data_million_large add column aaa varchar(10) |
| 17 | root            | 221.228.242.102:8671 | ytest | Query   |   13 | Waiting for table metadata lock | select * from data_million_large limit 1                  |
| 18 | root            | 221.228.242.102:8676 | ytest | Sleep   |   10 |                                 | NULL                                                      |
| 19 | root            | localhost            | NULL  | Query   |    0 | starting                        | show processlist                                          |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
6 rows in set (0.00 sec)

场景3-有失败的查询事务

mysql> show processlist;
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
| Id | User            | Host                 | db    | Command | Time | State                           | Info                                                      |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
|  1 | event_scheduler | localhost            | NULL  | Daemon  | 2305 | Waiting on empty queue          | NULL                                                      |
| 15 | root            | 221.228.242.102:8652 | ytest | Sleep   |  468 |                                 | NULL                                                      |
| 17 | root            | 221.228.242.102:8671 | ytest | Sleep   |  476 |                                 | NULL                                                      |
| 18 | root            | 221.228.242.102:8676 | ytest | Sleep   |  657 |                                 | NULL                                                      |
| 19 | root            | localhost            | NULL  | Query   |    0 | starting                        | show processlist                                          |
| 21 | root            | 221.228.242.102:8744 | ytest | Sleep   |  476 |                                 | NULL                                                      |
| 23 | root            | localhost            | ytest | Sleep   |   83 |                                 | NULL                                                      |
| 24 | root            | localhost            | ytest | Query   |   27 | Waiting for table metadata lock | alter table data_million_large add column bbb varchar(10) |
| 25 | root            | localhost            | ytest | Query   |    3 | Waiting for table metadata lock | select * from data_million_large limit 1                  |
+----+-----------------+----------------------+-------+---------+------+---------------------------------+-----------------------------------------------------------+
9 rows in set (0.01 sec)

这个示例最难排查，咋一看没有任何阻塞的事务存在，持有MDL锁的session已处于 Sleep 状态了

查看session最后一次执行的语句

可查看session执行的最后一次语句的记录，包括错误的信息

# 若同一session下执行错误，继续执行其他操作，则错误信息会被覆盖
mysql> select * from performance_schema.events_statements_current where errors=1G
*************************** 1. row ***************************
              THREAD_ID: 49
               EVENT_ID: 49
           END_EVENT_ID: 49
             EVENT_NAME: statement/sql/select
                 SOURCE: 
            TIMER_START: 2226273809177000
              TIMER_END: 2226273972864000
             TIMER_WAIT: 163687000
              LOCK_TIME: 0
               SQL_TEXT: select qwer from data_million_large limit 1
                 DIGEST: deeb24ced596fc26212e71be9d90cb47
            DIGEST_TEXT: SELECT `qwer` FROM `data_million_large` LIMIT ? 
         CURRENT_SCHEMA: ytest
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 1054
      RETURNED_SQLSTATE: 42S22
           MESSAGE_TEXT: Unknown column 'qwer' in 'field list'
                 ERRORS: 1
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
1 row in set (0.07 sec)

参考文档： https:// help.aliyun.com/knowled ge_detail/94566.html#concept-csn-5tt-4fb
《MySQL DBA甩锅技巧集锦-张沈波》