执行ALTER TABLE就导致锁

执行 select * from sys.schema_table_lock_waits; 看结果，就基本上知道谁造成的mdl锁了

yejr 发表于 2023-10-24 15:51
执行 select * from sys.schema_table_lock_waits; 看结果，就基本上知道谁造成的mdl锁了 ...

是空的

fuhao009 发表于 2023-10-24 16:32
是空的

我执行ddl 主节点可以收到
2023-10-24T16:29:44.619708+08:00 6669241 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 6669241
2023-10-24T16:29:44.619796+08:00 6669241 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 6669241
2023-10-24T16:29:44.667627+08:00 6670379 [Note] [MY-010914] [Server] Aborted connection 6670379 to db: 'mysql' user: 'hsyq' host: '10.33.0.50' (Got an error reading communication packets).
但是其他两个节点收不到，这个ddl是需要主节点更新完才会传到其他节点上吗

fuhao009 发表于 2023-10-24 16:35
我执行ddl 主节点可以收到
2023-10-24T16:29:44.619708+08:00 6669241 [Note] [MY-012485]  DDL log post ...

是的，所有事务都要先在primary节点应用完才行

yejr 发表于 2023-10-24 18:25
是的，所有事务都要先在primary节点应用完才行

select * from performance_schema.processlist where not COMMAND = 'Sleep' and STATE = 'System lock'
查询结果
*************************** 1. row ***************************
           ID: 141895
         USER: hsyq
         HOST: 10.133.105.116:12454
           DB: c_station
      COMMAND: Connect
         TIME: 51723
        STATE: System lock
         INFO: select

   ....
      TIME_MS: 51722843
    ROWS_SENT: 1
ROWS_EXAMINED: 65
执行 kill 141895
返回成功,但是查询线程还在

fuhao009 发表于 2023-10-25 11:08
select * from performance_schema.processlist where not COMMAND = 'Sleep' and STATE = 'System lock' ...

# clone  反复执行
2023-10-25T11:57:30.069040+08:00 242600 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 1
2023-10-25T11:57:35.228296+08:00 242608 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 2
2023-10-25T11:57:35.229427+08:00 242609 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 3
2023-10-25T11:57:40.340900+08:00 242620 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 4
2023-10-25T11:57:40.341945+08:00 242618 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 5
2023-10-25T11:57:40.342142+08:00 242619 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 6
2023-10-25T11:57:40.343496+08:00 242617 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 7
2023-10-25T12:21:02.247306+08:00 244661 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 1
2023-10-25T12:21:07.332400+08:00 244670 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 2
2023-10-25T12:21:07.332515+08:00 244669 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 3
2023-10-25T12:21:12.370229+08:00 244679 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 4
2023-10-25T12:21:12.372340+08:00 244681 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 5
2023-10-25T12:21:12.372418+08:00 244680 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 6
2023-10-25T12:21:12.372571+08:00 244678 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 7
2023-10-25T12:44:20.949785+08:00 246784 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 1
2023-10-25T12:44:26.058850+08:00 246793 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 2
2023-10-25T12:44:26.059094+08:00 246792 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 3
2023-10-25T12:44:31.197617+08:00 246802 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 4
2023-10-25T12:44:31.199018+08:00 246804 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 5
2023-10-25T12:44:31.200251+08:00 246801 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 6
2023-10-25T12:44:31.200815+08:00 246803 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 7
2023-10-25T12:44:36.383757+08:00 246814 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 8
2023-10-25T12:44:36.383921+08:00 246815 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 9
2023-10-25T12:44:36.384258+08:00 246812 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 10
2023-10-25T12:44:36.385627+08:00 246813 [Note] [MY-013457] [InnoDB] Clone Begin Task ID: 11

DDL被堵塞大多数情况是因为，表上有大查询、长事务未提交。你只需要查看谁元数据锁的情况即可。SELECT * FROM performance_schema.metadata_locks; 然后看LOCK_STATUS是PENDING就是被锁的对象。而你要kill掉占用解决元数据锁占用问题，可以用这个sql：
SELECT sql_kill_blocking_connection
FROM sys.schema_table_lock_waits
WHERE blocking_lock_type <> 'SHARED_UPGRADABLE'
  AND (waiting_query LIKE 'alter%'
  OR waiting_query LIKE 'create%'
  OR waiting_query LIKE 'drop%'
  OR waiting_query LIKE 'truncate%'
  OR waiting_query LIKE 'rename%');

参考链接(强烈建议详细阅读)
https://www.cnblogs.com/ivictor/p/15787546.html