• 【MySQL】DDL因Waiting for table metadata lock卡住


    在数据库空闲时间,对表做碎片整理:

    1
    alter table my_abc engine=innodb;

    发现会话被阻塞,显示状态是:

    1
    Waiting for table metadata lock

     

    手动断开alter操作后,通过show processlist查看:​

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    > show processlist;
    +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+
    | Id     | User   | Host                | db        | Command | Time  | State    | Info             | Progress |
    +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+
    | 489669 | pig    | 119.119.3.221:60226 | pig       | Sleep   |     4 |          | NULL             |    0.000 |
    | 489673 | xxx    | 119.119.3.172:41394 | abced_xxx | Sleep   |   120 |          | NULL             |    0.000 |
    | 498227 | xxx    | 119.119.3.172:48916 | abced_xxx | Sleep   | 28303 |          | NULL             |    0.000 |
    | 502773 | pig    | 119.119.3.172:47752 | pig       | Sleep   | 26139 |          | NULL             |    0.000 |
    | 519545 | root   | localhost           | NULL      | Sleep   | 15994 |          | NULL             |    0.000 |
    +--------+--------+---------------------+-----------+---------+-------+----------+------------------+----------+

     

    可以看到会话都处于sleep状态。但是一旦执行alter操作,就会卡在那里,等待元数据锁。

     

    出现Waiting for table metadata lock这种情况多半是和事务有关,要么是一个长事务在运行,要么是事务没有提交造成的

     

    先查看下是否开启了自动提交:​

    1
    2
    3
    4
    5
    6
    7
    8
    > show global variables like '%autocommit%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | autocommit             | ON    |
    | wsrep_retry_autocommit | 1     |
    +------------------------+-------+
    2 rows in set (0.001 sec)

     

    接下来,在查看一下是否有事务正在运行:​

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    > select * from information_schema.innodb_trx \G;
    *************************** 1. row ***************************
                        trx_id: 0
                     trx_state: RUNNING
                   trx_started: 2022-08-29 10:10:37
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 0
           trx_mysql_thread_id: 498227
                     trx_query: NULL
           trx_operation_state:
             trx_tables_in_use: 0
             trx_tables_locked: 0
              trx_lock_structs: 0
         trx_lock_memory_bytes: 1128
               trx_rows_locked: 0
             trx_rows_modified: 0
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    1 row in set (0.001 sec)
     
    ERROR: No query specified

    从这里看到,的确有一个事务在运行。从上班到下班还没结束。

     

    这里就要找开发同学问问应用到底是在干什么了,解决了这个问题,顺便还解决了最近业务反应有些功能慢的问题。

     

    此外,information_schema.innodb_trx中不会记录执行失败的事务,但是在这个执行失败的事务回滚前,它依然持有metadata lock,所以DDL操作依然会被阻塞。这个时候可以通过查找performance_schema.events_statements_current表来找到相关的语句和会话信息,将其杀死。为了减少metadata lock带来的危害,设置一个合理的lock_wait_timeout比较重要,这个值默认是365天,可以根据自身业务来设置,避免长时间的metadata lock等待。

     

    总结一下:

       ·长时间运行的事务很危险,需要多关注。

        ​·要关注autocommit是否开启

        ​·使用事务需要小心,记得 commit,捕获异常 rollback

        ​·做DDL操作前先检查一遍innodb_trx

        ​·设置合理的lock_wait_timeout

      

     

  • 相关阅读:
    代码随想录训练营第27天|LeetCode 39. 组合总和、40.组合总和II、 131.分割回文串
    前端vue scope的定义以及用法
    Spring 事务的简单了解
    某微e-office协同管理系统存在任意文件读取漏洞分析 CNVD-2022-07603
    9.2.tensorRT高级(4)封装系列-自动驾驶案例项目self-driving-深度估计
    【手写数据库toadb】数据库planner的整体架构,以及逻辑查询树的设计与实现流程
    JSON&redis
    Flutter开发 - iconfont妙用(手把手教程)
    数据结构与算法01:时间复杂度
    融合型日志平台应该如何建设?
  • 原文地址:https://www.cnblogs.com/abclife/p/16636955.html