56,678
社区成员
发帖
与我相关
我的任务
分享压测下，mysql产生死锁，请大神详解
今天给数据库压测的时候，通过终端查看innodb状态的时候，发现了死锁情况，日志如下：
------------------------
LATEST DETECTED DEADLOCK
------------------------
150106 14:44:17
*** (1) TRANSACTION:
TRANSACTION 0 16654328, ACTIVE 0 sec, process no 22120, OS thread id 47012738062656 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 4759135, query id 29462576 10.161.145.2 kkyoo statistics
SELECT money,money_verify FROM users WHERE uin=1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654328 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000fe1ff9; asc ;; 2: len 7; hex 000000010125de; asc % ;; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 5; hex 61646d696e; asc admin;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 61646d696e; asc admin;; 11: SQL NULL; 12: len 5; hex 61646d696e; asc admin;; 13: len 5; hex 61646d696e; asc admin;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 80000000c65c3b76; asc \;v;; 17: len 30; hex 306632613637303639663335336563353935323936333134393362343032; asc 0f2a67069f353ec59529631493b402;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 16654329, ACTIVE 0 sec, process no 22120, OS thread id 47012747913536 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 4760918, query id 29462577 10.161.145.2 kkyoo statistics
SELECT money,money_verify FROM users WHERE uin=4501 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654329 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000fe1ff9; asc ;; 2: len 7; hex 000000010125de; asc % ;; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 5; hex 61646d696e; asc admin;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 61646d696e; asc admin;; 11: SQL NULL; 12: len 5; hex 61646d696e; asc admin;; 13: len 5; hex 61646d696e; asc admin;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 80000000c65c3b76; asc \;v;; 17: len 30; hex 306632613637303639663335336563353935323936333134393362343032; asc 0f2a67069f353ec59529631493b402;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654329 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00001195; asc ;; 1: len 6; hex 000000fe1ff8; asc ;; 2: len 7; hex 00000001021527; asc ';; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 9; hex e69cbae599a8e4baba; asc ;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 726f626f74; asc robot;; 11: SQL NULL; 12: len 5; hex 726f626f74; asc robot;; 13: len 5; hex 726f626f74; asc robot;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 8000000033355060; asc 35P`;; 17: len 30; hex 346133346438663664343664666361393133633961313763366137393733; asc 4a34d8f6d46dfca913c9a17c6a7973;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
有没有哪位大婶给详细的解释下，为什么这两个事务会产生死锁，该如何优化解决。
小白真心求教 谢了
------------------------
LATEST DETECTED DEADLOCK
------------------------
150106 14:44:17
*** (1) TRANSACTION:
TRANSACTION 0 16654328, ACTIVE 0 sec, process no 22120, OS thread id 47012738062656 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 4759135, query id 29462576 10.161.145.2 kkyoo statistics
SELECT money,money_verify FROM users WHERE uin=1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654328 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000fe1ff9; asc ;; 2: len 7; hex 000000010125de; asc % ;; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 5; hex 61646d696e; asc admin;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 61646d696e; asc admin;; 11: SQL NULL; 12: len 5; hex 61646d696e; asc admin;; 13: len 5; hex 61646d696e; asc admin;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 80000000c65c3b76; asc \;v;; 17: len 30; hex 306632613637303639663335336563353935323936333134393362343032; asc 0f2a67069f353ec59529631493b402;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 16654329, ACTIVE 0 sec, process no 22120, OS thread id 47012747913536 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 4760918, query id 29462577 10.161.145.2 kkyoo statistics
SELECT money,money_verify FROM users WHERE uin=4501 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654329 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000fe1ff9; asc ;; 2: len 7; hex 000000010125de; asc % ;; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 5; hex 61646d696e; asc admin;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 61646d696e; asc admin;; 11: SQL NULL; 12: len 5; hex 61646d696e; asc admin;; 13: len 5; hex 61646d696e; asc admin;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 80000000c65c3b76; asc \;v;; 17: len 30; hex 306632613637303639663335336563353935323936333134393362343032; asc 0f2a67069f353ec59529631493b402;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 30 page no 6 n bits 160 index `PRIMARY` of table `pomelo`.`users` trx id 0 16654329 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 00001195; asc ;; 1: len 6; hex 000000fe1ff8; asc ;; 2: len 7; hex 00000001021527; asc ';; 3: len 0; hex ; asc ;; 4: len 0; hex ; asc ;; 5: SQL NULL; 6: SQL NULL; 7: len 9; hex e69cbae599a8e4baba; asc ;; 8: len 0; hex ; asc ;; 9: len 1; hex 81; asc ;; 10: len 5; hex 726f626f74; asc robot;; 11: SQL NULL; 12: len 5; hex 726f626f74; asc robot;; 13: len 5; hex 726f626f74; asc robot;; 14: len 4; hex 00000000; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 8; hex 8000000033355060; asc 35P`;; 17: len 30; hex 346133346438663664343664666361393133633961313763366137393733; asc 4a34d8f6d46dfca913c9a17c6a7973;...(truncated); 18: SQL NULL; 19: len 4; hex 5487f8e5; asc T ;; 20: len 2; hex 8000; asc ;; 21: len 1; hex 01; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
有没有哪位大婶给详细的解释下，为什么这两个事务会产生死锁，该如何优化解决。
小白真心求教 谢了
...全文
请发表友善的回复…
发表回复
知道就是你 2015-01-17
- 打赏
- 举报
的确写错了 ：
show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
fblack1 2015-01-16
- 打赏
- 举报
命令写错了把
表级锁：table_lock
行级锁：innodb_row_lock
商科程序员 2015-01-16
- 打赏
- 举报
把两个进程执行的SQL都弄出来，用两个命令行在不手动提交方式下一步步执行。
然后用查锁的方法去查锁写情况。一点点分析
知道就是你 2015-01-15
- 打赏
- 举报
关于是表锁，还是行，可以使用命令查看：
检查是否是“表级锁”，执行命令“show status like 'table_locks%';”
检查是否是“行级锁”，执行命令“show status like 'table_locks%';”
知道就是你 2015-01-14
- 打赏
- 举报
InnoDB会自动的检测死锁进行回滚，或者终止死锁的情况。
修改事务相关部分部分代码，基于autocommit=0，添加innodb_table_locks=1
具体含义： InnoDB会留意表的死锁，和MySQL层面的行级锁。另外，InnoDB不会检测MySQL的Lock Tables命令和其他存储引擎死锁。可以通过设置设置innodb_lock_wait_timeout来解决这个问题。innodb_lock_wait_timeout是Innodb放弃行级锁的超时时间。（官网提供，可信度较高）
innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(此时需要考虑应用端的频繁异常处理会消耗性能，不能设置过小)，最大可设置1073741824秒以上(再大就会被截断了，不过这样业务一直死循环等待下去而不能将资源使用来做其他的事情也是很浪费的一件事情)
fblack1 2015-01-14
- 打赏
- 举报
@cug_jiang126com
uin在这个表里面是主键索引和普通索引，但是在我的这种就两个简单的查询sql情况下，不管是锁定聚簇索引还是非聚簇索引，也只会锁行把，根据eplain看用到的是主键索引 我想@chouy说法应该更靠近事实些
欢迎拍砖
胡儿胡儿 2015-01-13
- 打赏
- 举报
我意思是你索引建重复了，show index from users看看就知你的uin列不仅是主键，还是非主键索引。而造成你死锁的两个查询列正好都是uid，有可能是两个查询交替锁住uin普通索引和uin primary key，导致了死锁。
个人猜测。
fblack1 2015-01-12
- 打赏
- 举报
@chouy
你分析的对的，过程应该是这样的，session1执行 sql1 然后执行sql2 ，session2执行sql2 然后执行sql1 在同时执行的时候产生了死锁，这种情况可以从业务的执行流程上得到优化，尽量让数据库执行的sql顺序一致，减小发生的概率
@cug_jiang126com
PRIMARY KEY (`uin`),KEY `uin` (`uin`) 这种设置主键的方法可行的
胡儿胡儿 2015-01-10
- 打赏
- 举报
PRIMARY KEY (`uin`),KEY `uin` (`uin`),你重复创建索引了，在mysql锁中锁住的不是具体数据，而是索引。删除掉多余的key应该可以。
fblack1 2015-01-09
- 打赏
- 举报
@chouy
请问，可以有效避免这种死锁吗
fblack1 2015-01-09
- 打赏
- 举报
语句1 SELECT money,money_verify FROM users WHERE uin=1 FOR UPDATE
请求等待对表users主键索引添加排它锁
语句2 SELECT money,money_verify FROM users WHERE uin=4501 FOR UPDATE
已经获取了对users表的主键索引上添加的排它锁，但是为什么语句2还是会去申请锁？
商科程序员 2015-01-08
- 打赏
- 举报
估计是你业务上一个业务更新完行1更新行2；另一个业务更新完行2更新行1。结果两个线程都执行完第一步后，都在等对方放开锁。
fblack1 2015-01-07
- 打赏
- 举报
我感觉应该是锁表了，两条语句执行的uin是不一样的，如果锁行，就不会产生死锁情况了
表结构如下：
CREATE TABLE `users` (
`uin` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
`password` varchar(32) NOT NULL,
`mobile` varchar(32) DEFAULT NULL,
`email` varchar(32) DEFAULT NULL,
`nickname` varchar(32) DEFAULT NULL,
`realname` varchar(32) DEFAULT NULL,
`gender` tinyint(1) DEFAULT '0',
`platform` varchar(64) DEFAULT NULL,
`logplatform` varchar(255) DEFAULT NULL,
`regapp` varchar(128) DEFAULT NULL,
`logapp` varchar(128) DEFAULT NULL,
`exp` int(10) unsigned DEFAULT '0',
`charm` int(10) DEFAULT '0',
`money` bigint(20) NOT NULL DEFAULT '0',
`money_verify` varchar(32) DEFAULT NULL,
`sigtime` int(10) unsigned DEFAULT NULL,
`regtime` int(11) unsigned DEFAULT NULL,
`msgid` smallint(6) DEFAULT '0',
`flag` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`uin`),
KEY `uin` (`uin`),
KEY `regtime` (`regtime`)
) ENGINE=InnoDB AUTO_INCREMENT=400000 DEFAULT CHARSET=utf8
可是到底为什么是锁表，不是锁行呢？
分析如有错误，请指正 灰常感谢
