3
mysql查锁SQL汇总.txt
5墨值下载
1、查询是否锁表
-- 查看那些表锁到了
show OPEN TABLES where In_use > 0;
-- 查看进程号
show processlist;
--删除进程
kill 1085850
查看正在锁的事务:
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务:
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查出死锁进程：SHOW PROCESSLIST
-- 杀掉进程 kill id;
其它关于查看死锁的命令：
1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4：select * from sys.innodb_lock_waits\G
-- 查询死锁详情
SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,
TIMESTAMPADD(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) wait_time,
r.trx_query waiting_query,
l.lock_table waiting_table_lock,
b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,
SUBSTRING(p.`HOST`,1,INSTR(p.`HOST`,':')-1) blocking_host,
SUBSTRING(p.`HOST`,INSTR(p.`HOST`,':')+1) blocking_port,
IF(p.COMMAND ='Sleep',p.TIME,0) idle_in_trx,
b.trx_query blocking_query
from information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_lock_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON l.lock_id = w.requested_lock_id
LEFT JOIN information_schema.`PROCESSLIST` p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time desc;
show engine innodb status \G
show engine innodb status 是 mysql 提供的一个用于查看 innodb 引擎时间信息的工具，就目前来说
有两处比较
常用的地方一、死锁分析 二、innodb 内存使用情况
二、通过 show engine innodb status 查看内存使用情况：
1、show variables like ‘innodb_buffer_pool_size’ 查看 buffer pool 的内存配置
show variables like ‘innodb_buffer_pool_size’;
±------------------------±----------+
| Variable_name | Value |
±------------------------±----------+
| innodb_buffer_pool_size | 268435456 |
±------------------------±----------+
1 row in set (0.01 sec)
mysql> select 268435456/1024/1024 as innodb_buffer_pool_size_in_MB;
±------------------------------+
| innodb_buffer_pool_size_in_MB |
±------------------------------+
| 256.00000000 |
±------------------------------+
1 row in set (0.00 sec)
2、通过 show engine innodb status 查看内存使用明细
show engine innodb status ;
BUFFER POOL AND MEMORY
Total large memory allocated 274857984
Dictionary memory allocated 116177
Buffer pool size 16382
Free buffers 16002
Database pages 380
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 345, created 35, written 37
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 380, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
三、对 BUFFER POOL AND MEMORY 各项的说明：
1、Total large memory allocated 分配给 innodb 的总内存大小、单位 byte
2、Dictionary memory allocated 分析给 innodb 数据字典的内存大小、单位 byte
3、Buffer pool size innodb buffer pool 的大小、单位 page 如果 page 的大小设置为 16k 的话
那么这个值乘以 16 就是 innodb buffer pool 的大小(单位 kb)
show global variables like ‘innodb_buffer_pool_size’;
±------------------------±----------+
| Variable_name | Value |
±------------------------±----------+
| innodb_buffer_pool_size | 268435456 |
±------------------------±----------+
1 row in set (0.00 sec)
of 21
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论