网站首页 返回列表
像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

查询当前是否存在meta锁

24-09-19 11:17:52 曹大卫 62

```sql
```bash
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;
```

```

关键字词[mysql]

分享到：

上一篇：查询当前连接数及动态修改参数

下一篇：使用alpine构建openjdk基础镜像

如需留言，请登录，没有账号？请注册

0 条评论 0 人参与

网站首页 返回列表
像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

查询当前是否存在meta锁

最新文章

点击排行

最新评论

友情链接

网站首页 返回列表 像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。

查询当前是否存在meta锁

最新文章

点击排行

最新评论

友情链接

网站首页返回列表
像“草根”一样，紧贴着地面，低调的存在，冬去春来，枯荣无恙。