mysql中的锁排查

1，排查元数据锁
方法1：列出所有持有 MDL 锁的会话，只能看到谁拿了锁，看不出有没有阻塞关系。
方法2：自动找出 “谁在等锁 + 是谁堵住它” 的阻塞链路，专门定位 DDL 卡壳问题。

方法1:
SELECT
  t.PROCESSLIST_ID AS '会话ID',
  t.PROCESSLIST_USER AS '用户',
  ml.OBJECT_SCHEMA AS '数据库',
  ml.OBJECT_NAME AS '表名',
  ml.LOCK_TYPE AS '锁类型',
  ml.LOCK_STATUS AS '锁状态',
  ml.OWNER_THREAD_ID AS '内部线程ID',
  t.PROCESSLIST_HOST AS '主机',
  t.PROCESSLIST_INFO AS '执行的SQL'
FROM performance_schema.metadata_locks ml
LEFT JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE ml.OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY ml.OBJECT_SCHEMA, ml.OBJECT_NAME;

方法2：
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G

2.排查行锁

方法1：
SELECT * FROM sys.innodb_lock_waits\G

方法2：

SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread_id,
  r.trx_query AS waiting_sql,
  w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread_id,
  b.trx_query AS blocking_sql,
  concat('KILL ',b.trx_mysql_thread_id) AS kill_block_sql
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks lr ON w.REQUESTING_ENGINE_LOCK_ID = lr.ENGINE_LOCK_ID
JOIN performance_schema.data_locks lb ON w.BLOCKING_ENGINE_LOCK_ID = lb.ENGINE_LOCK_ID
JOIN information_schema.innodb_trx r ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id\G

3.定位长事务

SELECT trx_id,trx_started,trx_mysql_thread_id,trx_query,trx_state
FROM information_schema.innodb_trx
ORDER BY trx_started\G