经常遇到开发测试童鞋问，离线开发测试环境，执行了一个DDL，发现很久没有执行了，是不是封锁？如何解决？?包括在群里，我们经常遇到类似的问题：DDL被阻塞了，如何找到阻塞它的SQL？其实，如何解决DDL被阻塞的问题，是MySQL中常见且频繁出现的问题。接下来，我将针对这个问题给出一个清晰易用的解决方案：如何判断一个DDL是否被阻塞？当DDL被阻塞时，如何找出阻塞它的session？如何判断一个DDL是否被阻塞Blocked首先看一个简单的Demosession1>createtablesbtest.t1(idintprimarykey,namevarchar(10));QueryOK,0rowsaffected(0.02sec)session1>insertintosbtest.t1values(1,'a');QueryOK,1rowaffected(0.01sec)session1>begin;QueryOK,0rowsaffected(0.00sec)session1>select*fromsbtest.t1;+----+------+|编号|名字|+----+------+|1|a|+----+------+1行在集合中（0.00秒）session2>altertablesbtest。t1添加c1日期时间；阻塞。..session3>showprocesslist;+----+----------------+------------+------+--------+--------+--------------------------------+--------------------------------------+|编号|用户|主持人|分贝|命令|时间|状态|信息|+----+----------------+----------+------+---------+--------+--------------------------------+-------------------------------------+|5|事件调度器|本地主机|空|守护进程|47628|等待空队列|空||24|根|本地主机|空|睡觉|11||空||25|根|本地主机|空|查询|5|等待表元数据锁|改变表sbtest.t1添加c1日期时间||26|根|本地主机|空|查询|0|初始化|显示进程列表|+----+----------------+------------+------+---------+--------+--------------------------------+-----------------------------------+4rowsinset(0.00sec)判断一个DDL是否被Blocked，很简单，执行showprocesslist查看DDL操作对应的状态即可。如果显示Waitingfortablemetadatalock，说明DDL被阻塞。一旦DDL被阻塞，后续对该表的所有操作都会被阻塞，显示Waitingfortablemetadatalock。这也是DDL如此受欢迎的原因。在类似的情况下，要么终止DDL操作，要么终止阻止DDL的会话。KillDDL操作是治标不治本。毕竟，DDL操作必须始终执行。另外，对于DDL操作，有两个阶段需要获取元数据库锁：DDL开始时和DDL结束前。如果是后者，意味着前面的操作要回滚，成本比较高。所以，遇到类似的场景，我们通常会kill阻塞DDL的session。那么，您如何知道哪些会话正在阻止DDL？下面来看看具体的定位方法。定位方法方法一：sys.schema_table_lock_waitssys.schema_table_lock_waits是MySQL5.7引入的，用于定位DDL被阻塞的问题。对于上面的演示。让我们看看sys.schema_table_lock_waits的输出。mysql>从sys.schema_table_lock_waits\G中选择*******************************1.行***************************object_schema：sbtestobject_name：t1waiting_thread_id：62waiting_pid：25waiting_account：root@localhostwaiting_lock_type：EXCLUSIVEwaiting_lock_duration：TRANSACTIONwaiting_query：altertablesbtest.t1addc1datetimewaiting_query_secs：17waiting_query_rows_affected：0waiting_query_rows_examined：0blocking_thread_id：61blocking_pid：24blocking_account：root@localhostblocking_lock_type：SHARED_READblocking_lock_duration：TRANSACTIONsql_kill_blocking_query：KILLQUERY24sql_kill_blocking_connection：KILL*24**************************2.行***************************object_schema:sbtestobject_name:t1waiting_thread_id：62waiting_pid：25waiting_account：root@localhostwaiting_lock_type：EXCLUSIVEwaiting_lock_duration：TRANSACTIONwaiting_query：altertablesbtest.t1addc1datetimewaiting_query_secs：17waiting_query_rows_affected：0waiting_query_rows_examined：0blocking_thread_id：62blocking_pid：25blocking_ADGRHAblocking_account：root@local_UPGRlockblocking_account：root@local_UPGRlockblocking_account:TRANSACTIONsql_kill_blocking_query:KILLQUERY25sql_kill_blocking_connection:KILL252rowsinset(0.00sec)只有一个alter操作，但是生成了两条记录，两条记录的Kill对象不同，其中一条Kill的对象是仍然是改变操作本身。如果不熟悉表结构或者不仔细阅读记录内容，难免杀错对象。不仅如此，DDL操作被阻塞后，如果有N个后续查询被DDL操作阻塞，也会产生N*2条记录。定位问题的时候，这N*2条记录完全是噪音。这时候我们就需要对上面的记录进行过滤。过滤的关键是blocking_lock_type不等于SHARED_UPGRADABLE。SHARED_UPGRADABLE是可升级的共享元数据锁，在加锁期间允许并发查询和更新，常用于DDL操作的第一阶段。因此，阻塞DDL的不会是SHARED_UPGRADABLE。因此，对于上面的情况，我们可以通过下面的查询来准确定位到需要Kill掉的session。选择sql_kill_blocking_connectionFROMsys.schema_table_lock_waitsWHEREblocking_lock_type<>'SHARED_UPGRADABLE'ANDwaiting_query='altertablesbtest.t1addc1datetime';方法二：在MySQL5.7引入DDLsys.schema_table_lock_waits之前kill掉session。但是在实际生产环境中，MySQL5.6还是占据了相当大的份额。MySQL5.6如何解决这个痛点？仔细研究，导致DDL被阻塞的操作无外乎两种：对表的慢查询没有完成。表中有未提交的事务。其中，第一类比较容易定位，通过showprocesslist可以找到。第二种仅靠showprocesslist很难定位，因为showprocesslist中有未提交事务的连接状态和idle连接一样，都是Sleep。所以网上说有Kil??lidleconnections也不是没有道理，但是这样做太简单粗暴了，误杀也是难免的。其实既然是事务，information_schema.innodb_trx中肯定有记录，比如session1中的事务，表中的记录如下，mysql>select*frominformation_schema.innodb_trx\G******************************1.行*****************************trx_id：421568246406trx_state：运行trx_started：2022-01-0208:53:50trx_requested_lock_id：nulltr??x_wait_started：00trx_lock_structs:0trx_lock_memory_bytes:1128trx_rows_locked:0trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level:REPEATABLEREADtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:0trx_is_read_only:0trx_autocommit_non_locking：0trx_schedule_weight:NULL1rowinset(0.00sec)其中trx_mysql_thread_id为线程id，结合information_schema.processlist，可以进一步缩小范围。因此，我们可以使用如下SQL来定位执行时间早于DDL的事务。SELECTconcat('kill',i.trx_mysql_thread_id,';')FROMinformation_schema.innodb_trxi,(SELECTMAX(time)ASmax_timeFROMinformation_schema.processlistWHEREstate='等待表元数据锁定'AND(infoLIKE'alter%'ORinfoLIKE'create%'ORinfoLIKE'drop%'ORinfoLIKE'truncate%'ORinfoLIKE'rename%'))pWHEREtimestampdiff(second,i.trx_started,now())>p.max_time;幸运的是，当前正在执行的查询也显示在information_schema.innodb_trx中。所以，上面的SQL同样适用于慢查询没有完成的场景。MySQL5.7sys.schema_table_lock_waits使用注意事项sys.schema_table_lock_waits视图依赖于一个MDL相关的表——performance_schema.metadata_locks。该表是MySQL5.7引入的，会显示MDL的相关信息，包括对象、锁类型、锁状态。但是在MySQL5.7中，这个表默认是空的，因为相关的instrument默认没有启用。默认情况下启用MySQL8.0。mysql>选择*fromperformance_schema.setup_instrumentswherename='wait/lock/metadata/sql/mdl';+--------------------------+--------+------+|姓名|已启用|定时|+--------------------------+--------+--------+|等待/锁定/元数据/sql/mdl|否|否|+----------------------------+--------+--------+1rowinset(0.00sec)所以，在MySQL5.7中，如果我们要使用sys.schema_table_lock_waits，首先要开启MDL相关工具。开启方法很简单，直接修改performance_schema.setup_instruments表即可。具体SQL如下。更新performance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES'WHERENAME='wait/lock/metadata/sql/mdl';不过该方法只是暂时有效，实例重启后会恢复到默认值。建议同步修改配置文件。[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'总结1.执行showprocesslist，如果DDL状态为Waitingfortablemetadatalock，说明DDL被阻塞。2.定位到导致DDL被阻塞的session。常用方法有两种：sys.schema_table_lock_waitsSELECTsql_kill_blocking_connectionFROMsys.schema_table_lock_waitsWHEREblocking_lock_type<>'SHARED_UPGRADABLE'AND(waiting_queryLIKE'alter%'ORwaitingqueryORLIKE'waiting_%'%'ORwaiting_queryLIKE'truncate%'ORwaiting_queryLIKE'rename%');此方法适用于MySQL5.7和8.0。请注意，在MySQL5.7中，与MDL相关的仪器默认情况下未启用。在DDLSELECTconcat('kill',i.trx_mysql_thread_id,';')FROMinformation_schema.innodb_trxi,(SELECTMAX(time)ASmax_timeFROMinformation_schema.processlistWHEREstate='等待表元数据锁定'AND(信息如“改变%”或信息如“创建%”或信息如“下降%”或信息如“截断%”或信息如“重命名%”))pWHEREtimestampdiff(second,i.trx_started,now())>p.max_time；如果mysql5.7或者mysql5.6没有打开mdl相关的instrument，可以使用该方法。
