mysql查看死锁记录(mysql查看死锁与去除死锁示例详解)
mysql查看死锁记录
mysql查看死锁与去除死锁示例详解1、查询进程
show processlist
2、 查询到相对应的进程,然后 kill id
验证(kill后再看是否还有锁)
2、查询是否锁表
|
show open tables where in_use > 0; |
示例:
新建一个会话执行如下的显示锁示例
|
lock tables account_data.account read ; select sleep(160); unlock tables account_data.account; |
另开启一个会话检查锁表情况:
|
mysql> show open tables where in_use > 0; + --------------+---------+--------+-------------+ | database | table | in_use | name_locked | + --------------+---------+--------+-------------+ | account_data | account | 1 | 0 | + --------------+---------+--------+-------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_locks\g; empty set , 1 warning (0.00 sec) error: no query specified mysql> show processlist\g; *************************** 1. row *************************** id: 5 user : root host: 192.168.0.206:64294 db: null command: sleep time : 4051 state: info: null *************************** 2. row *************************** id: 8 user : root host: 192.168.0.206:64297 db: null command: sleep time : 4042 state: info: null *************************** 3. row *************************** id: 10 user : root host: localhost db: null command: query time : 0 state: starting info: show processlist *************************** 4. row *************************** id: 19 user : root host: 192.168.0.206:54603 db: account_data command: sleep time : 245 state: info: null *************************** 5. row *************************** id: 20 user : root host: 192.168.0.206:54604 db: information_schema command: query time : 20 state: user sleep info: select sleep(160) 5 rows in set (0.00 sec) error: no query specified mysql> |
3、在5.5中,information_schema 库中增加了三个关于锁的表(innodb引擎):
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系
先来看一下这三张表结构:
|
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks; +————-+———————+——+—–+———+——-+ | field | type | null | key | default | extra | +————-+———————+——+—–+———+——-+ | lock_id | varchar (81) | no | | | |#锁id | lock_trx_id | varchar (18) | no | | | |#拥有锁的事务id | lock_mode | varchar (32) | no | | | |#锁模式 | lock_type | varchar (32) | no | | | |#锁类型 | lock_table | varchar (1024) | no | | | |#被锁的表 | lock_index | varchar (1024) | yes | | null | |#被锁的索引 | lock_space | bigint (21) unsigned | yes | | null | |#被锁的表空间号 | lock_page | bigint (21) unsigned | yes | | null | |#被锁的页号 | lock_rec | bigint (21) unsigned | yes | | null | |#被锁的记录号 | lock_data | varchar (8192) | yes | | null | |#被锁的数据 +————-+———————+——+—–+———+——-+ 10 rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | field | type | null | key | default | extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar (18) | no | | | |#请求锁的事务id | requested_lock_id | varchar (81) | no | | | |#请求锁的锁id | blocking_trx_id | varchar (18) | no | | | |#当前拥有锁的事务id | blocking_lock_id | varchar (81) | no | | | |#当前拥有锁的锁id +——————-+————-+——+—–+———+——-+ 4 rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | field | type | null | key | default | extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar (18) | no | | | |#事务id | trx_state | varchar (13) | no | | | |#事务状态: | trx_started | datetime | no | | 0000-00-00 00:00:00 | |#事务开始时间; | trx_requested_lock_id | varchar (81) | yes | | null | |#innodb_locks.lock_id | trx_wait_started | datetime | yes | | null | |#事务开始等待的时间 | trx_weight | bigint (21) unsigned | no | | 0 | |# | trx_mysql_thread_id | bigint (21) unsigned | no | | 0 | |#事务线程id | trx_query | varchar (1024) | yes | | null | |#具体sql语句 | trx_operation_state | varchar (64) | yes | | null | |#事务当前操作状态 | trx_tables_in_use | bigint (21) unsigned | no | | 0 | |#事务中有多少个表被使用 | trx_tables_locked | bigint (21) unsigned | no | | 0 | |#事务拥有多少个锁 | trx_lock_structs | bigint (21) unsigned | no | | 0 | |# | trx_lock_memory_bytes | bigint (21) unsigned | no | | 0 | |#事务锁住的内存大小(b) | trx_rows_locked | bigint (21) unsigned | no | | 0 | |#事务锁住的行数 | trx_rows_modified | bigint (21) unsigned | no | | 0 | |#事务更改的行数 | trx_concurrency_tickets | bigint (21) unsigned | no | | 0 | |#事务并发票数 | trx_isolation_level | varchar (16) | no | | | |#事务隔离级别 | trx_unique_checks | int (1) | no | | 0 | |#是否唯一性检查 | trx_foreign_key_checks | int (1) | no | | 0 | |#是否外键检查 | trx_last_foreign_key_error | varchar (256) | yes | | null | |#最后的外键错误 | trx_adaptive_hash_latched | int (1) | no | | 0 | |# | trx_adaptive_hash_timeout | bigint (21) unsigned | no | | 0 | |# +—————————-+———————+——+—–+———————+——-+ 22 rows in set (0.01 sec) |
查看正在锁的事务
|
select * from information_schema.innodb_locks; |
查看等待锁的事务
|
select * from information_schema.innodb_lock_waits; |
查看锁阻塞线程信息
3.1 使用show processlist查看
3.2 直接使用show engine innodb status查看
|
------------ transactions ------------ trx id counter 4131 purge done for trx's n:o < 4119 undo n:o < 0 state: running but idle history list length 126 list of transactions for each session: ---transaction 0, not started mysql thread id 2, os thread handle 0x7f953ffff700, query id 115 localhost root init show engine innodb status ---transaction 4130, active 41 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 4, os thread handle 0x7f953ff9d700, query id 112 localhost root updating delete from emp where empno=7788 ------- trx has been waiting 41 sec for this lock to be granted: ## 等待了41s record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4130 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 ## 线程4在等待往test.emp中的主键上加x锁,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ------------------ ---transaction 4129, active 45 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 7, os thread handle 0x7f953ff6c700, query id 111 localhost root updating update emp set sal=3500 where empno=7788 ------- trx has been waiting 45 sec for this lock to be granted: ## 等待了45s record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4129 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 ## 线程7在等待往test.emp中的主键上加x锁,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ------------------ ---transaction 4128, active 51 sec 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 3, os thread handle 0x7f953ffce700, query id 110 localhost root cleaning up |
我们知道,主要根因还是thread=3引起的,但从innodb status中却无法分析得到这个结果。
从上面来看,线程4和线程7都在等待往test.emp中的主键上加x锁,page num=3,但是线程7等待的时间为45s,而线程4等待的时间为41s,是较线程7之后申请的锁,所以可以判断是线程7阻塞了线程4。至于线程7为什么出现等待,这里分析不到根因。
3.3 使用mysqladmin debug查看
|
# mysqladmin -s /tmp/mysql3306.sock debug |
然后在error日志中,会看到:
|
thread database .table_name locked/waiting lock_type 3 test.t3 locked - read low priority read lock 7 test.emp locked - write high priority write lock |
这种方法中,能找到线程id=3和7是阻塞者,但还是不太准确,判断不出来线程7也是被线程id=3阻塞的。
3.4 使用innodb_lock_monitor来获取阻塞锁线程
|
mysql [test]> create table innodb_lock_monitor (a int ) engine=innodb; ## 随便在一个数据库中创建这个表,就会打开lock monitor query ok, 0 rows affected, 1 warning (0.07 sec) mysql [test]> show warnings\g *************************** 1. row *************************** level : warning code: 131 message: using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. use information_schema or performance_schema tables or set global innodb_status_output= on . 1 row in set (0.00 sec) |
说明:这个在5.6中有一个warning,但不影响使用。
然后再使用show engine innodb status查看:
|
------------ transactions ------------ trx id counter 4667 purge done for trx's n:o < 4659 undo n:o < 0 state: running but idle history list length 138 list of transactions for each session: ---transaction 0, not started mysql thread id 9, os thread handle 0x7f813c5f7700, query id 152 localhost root init show engine innodb status ---transaction 4663, active 78 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 4, os thread handle 0x7f813c628700, query id 149 localhost root updating delete from emp where empno=7788 ------- trx has been waiting 78 sec for this lock to be granted: ## 等待了78s record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4663 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 ## 线程4在等待往test.emp中的主键上加x锁,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ------------------ table lock table `test`.`emp` trx id 4663 lock mode ix ## 在给主键行上加x锁之前,先要在表上加意向锁ix record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4663 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ---transaction 4662, active 81 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 7, os thread handle 0x7f813c5c6700, query id 148 localhost root updating update emp set sal=3500 where empno=7788 ------- trx has been waiting 81 sec for this lock to be granted: ## 等待了81s record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4662 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 ## 线程7在等待往test.emp中的主键上加x锁,page num=3 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ------------------ table lock table `test`.`emp` trx id 4662 lock mode ix ## 在给主键行上加x锁之前,先要在表上加意向锁ix record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4662 lock_mode x locks rec but not gap waiting record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; ---transaction 4615, active 1579 sec, thread declared inside innodb 1222 mysql tables in use 2, locked 0 2 lock struct(s), heap size 360, 1 row lock(s) mysql thread id 3, os thread handle 0x7f813c659700, query id 147 localhost root sending data select count (*) from t3 a,t3 b ## 这是线程3当前正在执行的sql trx read view will not see trx with id >= 4662, sees < 4659 table lock table `test`.`emp` trx id 4615 lock mode ix ## 线程3中正在拥有表上的意向ix锁,并且有test.emp表上主键的行级x锁,page num=3 record locks space id 16 page no 3 n bits 88 index ` primary ` of table `test`.`emp` trx id 4615 lock_mode x locks rec but not gap record lock, heap no 9 physical record: n_fields 10; compact format; info bits 0 0: len 4; hex 80001e6c; asc l;; 1: len 6; hex 000000001018; asc ;; 2: len 7; hex 91000001420084; asc b ;; 3: len 5; hex 53434f5454; asc scott;; 4: len 7; hex 414e414c595354; asc analyst;; 5: len 4; hex 80001d8e; asc ;; 6: len 4; hex 208794f0; asc ;; 7: len 4; hex 80000bb8; asc ;; 8: sql null ; 9: len 4; hex 80000014; asc ;; |
为什么线程3当前执行的是一个select t3表操作,但却锁住了test.emp表上page num=3?
有可能是线程3之前对test.emp表的操作事务没有及时提交导致。
所以得出:线程3阻塞了线程7,而线程7又阻塞了线程4,所以根因就是线程3,让线程3尽快提交或是kill掉即可。
3.5、 查看表锁的情况:
|
mysql> show status like 'table%' ; + ----------------------------+---------+ | variable_name | value | + ----------------------------+---------+ | table_locks_immediate | 100 | | table_locks_waited | 11 | + ----------------------------+---------+ |
3.6、查看innodb_row_lock状态变量来分析系统上的行锁的争夺情况:
|
mysql> show status like 'innodb_row_lock%' ; + -------------------------------+--------+ | variable_name | value | + -------------------------------+--------+ | innodb_row_lock_current_waits | 0 | | innodb_row_lock_time | 159372 | | innodb_row_lock_time_avg | 39843 | | innodb_row_lock_time_max | 51154 | | innodb_row_lock_waits | 4 | + -------------------------------+--------+ 5 rows in set (0.01 sec) mysql> |
4. 结论
在分析innodb中锁阻塞时,几种方法的对比情况:
(1)使用show processlist查看不靠谱;
(2)直接使用show engine innodb status查看,无法判断到问题的根因;
(3)使用mysqladmin debug查看,能看到所有产生锁的线程,但无法判断哪个才是根因;
(4)开启innodb_lock_monitor后,再使用show engine innodb status查看,能够找到锁阻塞的根因。
参考:http://www.zzvips.com/article/147156.html
到此这篇关于mysql查看死锁与去除死锁的文章就介绍到这了,更多相关mysql查看死锁与去除死锁内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://www.cnblogs.com/duanxz/p/4394641.html
- mysql中默认排序教程(基于mysql 默认排序规则的坑)
- mysql数据库迁移命令(MySQL数据迁移相关总结)
- mysql的日志怎么清除(MySQL删除了记录不生效的原因排查)
- mysqlexplain解析(Mysql explain用法与结果深入分析)
- navicat15.0.28注册码(Navicat for MySQL 11注册码激活码汇总)
- mysql缓存是什么(详解mysql查询缓存简单使用)
- mysql8.0使用(MySQL 8.0新特性 — 检查性约束的使用简介)
- mysql各种备份方式(MySQL 逻辑备份与恢复测试的相关总结)
- navicat怎么和mysql连接(Navicat Premium远程连接MySQL数据库的方法)
- mysql查询条件的优化(MySQL查询优化之查询慢原因和解决技巧)
- mysql出现锁表的原因(导致MySQL做全表扫描的几种情况)
- druid数据库连接池原理详解(阿里巴巴Druid,轻松实现MySQL数据库加密!)
- navicat连接mysql报错(解决Navicat Premium 连接 MySQL 8.0 报错"1251"的问题分析)
- 适合小白入门的mysql教程索引简介(一篇带给你MySQL高性能索引)
- 怎么查看mysql运行日志(通过Query Profiler查看MySQL语句运行时间的操作方法)
- mysql 8.0.22 winx64安装配置方法图文教程(mysql 8.0.22 winx64安装配置方法图文教程)
- 哪几个历史人物被影协主席李雪健演的活灵活现(哪几个历史人物被影协主席李雪健演的活灵活现)
- 王伦狭隘,晁盖霸道,宋江奸诈骨头软,只有鲁智深才适合当寨主(王伦狭隘晁盖霸道)
- 他是梁山最早的头目,江湖人称 旱地忽律 ,宋江几乎将其遗忘(他是梁山最早的头目)
- 梁山创始人杜迁,为何不受宋江待见,只排名83位(梁山创始人杜迁)
- 法国面包(法国面包法棍)
- 微信(微信分身)
热门推荐
- python定义dataframe(对python dataframe逻辑取值的方法详解)
- laraveljob怎么运行脚本(Laravel-admin之修改操作日志的方法)
- C#常用的命名规范
- python3正则表达式详解(Python正则表达式和re库知识点总结)
- python图片模板匹配(python实现简单图片物体标注工具)
- mysql8修改默认端口(MySQL 8.0新特性 — 管理端口的使用简介)
- SQL SERVER中调用存储过程
- mysql完全快速清空一个表(浅谈MySQL如何优雅的做大表删除)
- php怎么引用文件(php引用和拷贝的区别知识点总结)
- 用户 IIS APPPOOLDefaultAppPool 登录失败的解决方法
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9