赞
踩
- -- 1.使用大于0表
- SHOW OPEN TABLES WHERE `Database` = 'test' AND In_use > 0 ;
- -- 2.request_trx_id请求锁 与 blocking_trx_id产生锁原因
- SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-
- SELECT trx_id,trx_mysql_thread_id FROM information_schema.innodb_trx ;
-
- -- 3.被锁语句id
- SELECT
- NOW(),
- (
- UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)
- ) diff_sec,
- b.id,-- 被锁语句id
- b.user,
- b.host,
- b.db,
- c.lock_type,
- c.lock_table,
- c.lock_index
- FROM
- information_schema.innodb_trx a
- INNER JOIN information_schema.PROCESSLIST b
- ON a.TRX_MYSQL_THREAD_ID = b.id
- INNER JOIN information_schema.INNODB_LOCKS c
- ON a.trx_requested_lock_id = c.lock_id ;
-
- -- 4.查看正在锁的事务,表名,锁状态
- SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
- -- 5.查看id对应的SQL
- SHOW FULL PROCESSLIST;
-
- SELECT b.processlist_id,a.thread_id,a.sql_text FROM
- performance_schema.events_statements_current a, performance_schema.threads b
- WHERE a.thread_id=b.thread_id
-
- -- 6.杀掉进程
- KILL ??
-
-
- -- 一步到位
-
- SELECT * FROM sys.`innodb_lock_waits`;
-
-
- -- 以下语句适用5.6(含有performance_schema.events_statements_current)以上版本
- SELECT
- b.`trx_mysql_thread_id` 被锁id,
- (SELECT
- a.sql_text
- FROM
- performance_schema.events_statements_current a,
- performance_schema.threads b
- WHERE a.thread_id = b.thread_id
- AND b.processlist_id = b.`trx_mysql_thread_id`) 被锁SQL,
- d.`lock_table` 被锁表,
- c.`trx_mysql_thread_id` 锁表id, -- 杀掉 kill ??
- (SELECT
- a.sql_text
- FROM
- performance_schema.events_statements_current a,
- performance_schema.threads b
- WHERE a.thread_id = b.thread_id
- AND b.processlist_id = c.`trx_mysql_thread_id`) 锁表SQL,
- e.`lock_table` 锁表
- FROM
- INFORMATION_SCHEMA.INNODB_LOCK_WAITS a
- LEFT JOIN information_schema.innodb_trx b
- ON a.`requesting_trx_id` = b.`trx_id`
- LEFT JOIN information_schema.innodb_trx c
- ON a.`blocking_trx_id` = c.`trx_id`
- LEFT JOIN information_schema.INNODB_LOCKS d
- ON a.`requesting_trx_id` = d.`lock_trx_id`
- LEFT JOIN information_schema.INNODB_LOCKS e
- ON a.`blocking_trx_id` = e.`lock_trx_id` ;
-
- --适用于5.7及以上版本
- select t2.PROCESSLIST_ID,from_unixtime(unix_timestamp(now())-t4.time) START_TIME,t1.*,t3.sql_text from
- (
- SELECT
- OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,OWNER_THREAD_ID
- FROM
- `performance_schema`.metadata_locks
- WHERE
- OWNER_THREAD_ID != sys.ps_thread_id (CONNECTION_id())
- and OBJECT_SCHEMA='test'
- ) t1
- left join `performance_schema`.threads t2 on t1.OWNER_THREAD_ID=t2.THREAD_ID
- left join performance_schema.events_statements_current t3 on t1.OWNER_THREAD_ID=t3.THREAD_ID
- left join information_schema.`PROCESSLIST` t4 on t4.ID=t2.PROCESSLIST_ID
- order by t1.OBJECT_NAME
-
-
-
MySQL 5.7版本
锁状态LOCK_STATUS:PENDING
全局读锁、MDL锁、表级锁
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connetion_id());
--语句
select * from performance_schema.events_statements_current where thread_id=?;MySQL 8.0版本
锁状态LOCK_STATUS:WAITING
行级锁
select * from performance_schema.data_locks
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。