- 积分
- 35
- UID
- 17398
- 阅读权限
- 10
- 注册时间
- 2015-1-8
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
今天给数据库压测的时候,通过终端查看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
------------
有没有哪位大婶给详细的解释下,为什么这两个事务会产生死锁,该如何优化解决。
小白真心求教 谢了
|
|