mysql innodb 死锁分析

cy_88

mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper #mysql版本 5.7.16　　

　　
Connection id: 10042
　　
Current database: china9129
　　
Current user: root@localhost
　　
SSL: Not in use
　　
Current pager: stdout
　　
Using outfile: ''
　　
Using delimiter: ;
　　
Server version: 5.7.16-log MySQL Community Server (GPL)
　　
Protocol version: 10
　　
Connection: Localhost via UNIX socket
　　
Server characterset: utf8
　　
Db characterset: utf8
　　
Client characterset: utf8
　　
Conn. characterset: utf8
　　
UNIX socket: /data/mysql/mysql9129/sock/mysql9129.sock
　　
Uptime: 4 days 6 hours 2 min 31 sec
　　

　　

　　
Threads: 4 Questions: 31133 Slow queries: 0 Opens: 254 Flush tables: 3 Open tables: 58 Queries per second avg: 0.084
　　

　　
SESSION 1:
　　
"root@localhost:mysql9129.sock [(none)]>set global TRANSACTION ISOLATION LEVEL REPEATABLE READ;
　　
"root@localhost:mysql9129.sock [(none)]>select @@tx_isolation;
　　
+-----------------+
　　
| @@tx_isolation |
　　
+-----------------+
　　
| REPEATABLE-READ |
　　
+-----------------+
　　
1 row in set (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [china9129]>show create table t100;
　　
+-------+----------------------------------------------------------------------------------------------------------+
　　
| Table | Create Table |
　　
+-------+----------------------------------------------------------------------------------------------------------+
　　
| t100 | CREATE TABLE `t100` (
　　
  `id` int(11) NOT NULL,
　　
  PRIMARY KEY (`id`)
　　
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
　　
+-------+----------------------------------------------------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [china9129]>select * from t100;
　　
+-----+
　　
| id |
　　
+-----+
　　
| 1 |
　　
| 2 |
　　
| 3 |
　　
| 4 |
　　
| 5 |
　　
| 111 |
　　
+-----+
　　

　　
"root@localhost:mysql9129.sock [china9129]>begin;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [china9129]>select * from t100 where id=5 for update;
　　
+----+
　　
| id |
　　
+----+
　　
| 5 |
　　
+----+
　　
1 row in set (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [china9129]>delete from t100 where id =3;
　　
Query OK, 1 row affected (3.62 sec)
　　

　　
==========================================================================================================
　　
SESSION 2:
　　

　　
"root@localhost:mysql9129.sock [(none)]>begin;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [(none)]>use china9129;
　　
Database changed
　　
"root@localhost:mysql9129.sock [china9129]>select * from t100 where id=3 for update;
　　
+----+
　　
| id |
　　
+----+
　　
| 3 |
　　
+----+
　　
1 row in set (0.00 sec)
　　

　　
"root@localhost:mysql9129.sock [china9129]>delete from t100 where id =5;
　　
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
　　

　　

　　
#查看死锁信息
　　
"root@localhost:mysql9129.sock [china9129]>show engine innodb status \G
　　
*************************** 1. row ***************************
　　
  Type: InnoDB
　　
  Name:
　　
Status:
　　
=====================================
　　
2016-12-20 21:57:45 0x7fa0bffff700 INNODB MONITOR OUTPUT
　　
=====================================
　　
Per second averages calculated from the last 24 seconds
　　
-----------------
　　
BACKGROUND THREAD
　　
-----------------
　　
srv_master_thread loops: 1133 srv_active, 0 srv_shutdown, 362529 srv_idle
　　
srv_master_thread log flush and writes: 363662
　　
----------
　　
SEMAPHORES
　　
----------
　　
OS WAIT ARRAY INFO: reservation count 4540
　　
OS WAIT ARRAY INFO: signal count 5766
　　
RW-shared spins 0, rounds 4683, OS waits 474
　　
RW-excl spins 0, rounds 134731, OS waits 1327
　　
RW-sx spins 6332, rounds 138603, OS waits 2382
　　
Spin rounds per wait: 4683.00 RW-shared, 134731.00 RW-excl, 21.89 RW-sx
　　
------------------------
　　
LATEST DETECTED DEADLOCK #监测出最近的死锁信息
　　
------------------------
　　
2016-12-20 21:56:29 0x7fa0bffff700
　　
*** (1) TRANSACTION: #第一个事物
　　
TRANSACTION 13899, ACTIVE 51 sec starting index read #事物id 13899，活跃了51秒
　　
mysql tables in use 1, locked 1 #mysql有一个表在使用，一个表被锁住
　　
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) #有3个锁链表，内存中堆的大小1136, 两行记录被锁定
　　
MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root updating
　　
#mysql线程id 10041, query id 31128, localhost root用户执行update操作
　　
delete from t100 where id =3 #执行了这个SQL语句的时候，发生了锁等待
　　
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #等待这个锁被释放
　　
RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13899 lock_mode X locks rec but not gap waiting
　　
#类型：行锁，等待在t100的主键上 page num 3，加一个X锁(not gap waiting)，锁住80 bits。
　　
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
　　
0: len 4; hex 80000003; asc ;;
　　
1: len 6; hex 00000000363b; asc 6;;;
　　
2: len 7; hex c7000001660110; asc f ;;
　　

　　

　　
*** (2) TRANSACTION:#第二个事物
　　
TRANSACTION 13900, ACTIVE 31 sec starting index read #事物id 13900，活跃了31秒
　　
mysql tables in use 1, locked 1 #mysql有一个表在使用，一个表被锁住
　　
3 lock struct(s), heap size 1136, 2 row lock(s) #有3个锁链表，内存中堆的大小1136, 两行记录被锁定
　　
MySQL thread id 10042, OS thread handle 140328392718080, query id 31129 localhost root updating
　　
#mysql线程id 10042, query id 31129, localhost root用户执行update操作
　　
delete from t100 where id =5 #执行了这个SQL语句的时候，发生了锁等待
　　
*** (2) HOLDS THE LOCK(S): #事物 13900 持有的锁
　　
RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap
　　
#类型：行锁，事物 id 13900，在t100的主键上 page num 3，加一个X锁(not gap 没有间隙锁)，锁住80 bits
　　
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
　　
0: len 4; hex 80000003; asc ;;
　　
1: len 6; hex 00000000363b; asc 6;;;
　　
2: len 7; hex c7000001660110; asc f ;;
　　

　　

　　
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: #当事物2在执行delete from t100 where id =5的时候，发生锁等待
　　
RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap waiting
　　
#类型：行锁，等待在t100的主键上 page num 3，加一个X锁(not gap waiting 没有间隙锁)，锁住80 bits。
　　
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
　　
0: len 4; hex 80000005; asc ;;
　　
1: len 6; hex 00000000363d; asc 6=;;
　　
2: len 7; hex c90000014c0110; asc L ;;
　　

　　

　　
*** WE ROLL BACK TRANSACTION (2)
　　
#事物2，TRANSACTION 13900被回滚了。报错信息：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
　　
------------
　　
TRANSACTIONS
　　
------------
　　
Trx id counter 13905
　　
Purge done for trx's n:o < 13905 undo n:o < 0 state: running but idle
　　
History list length 568
　　
LIST OF TRANSACTIONS FOR EACH SESSION:
　　
---TRANSACTION 421807334705888, not started
　　
0 lock struct(s), heap size 1136, 0 row lock(s)
　　
---TRANSACTION 13899, ACTIVE 127 sec
　　
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
　　
MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root cleaning up
　　
-------
　　

　　
备注:
　　
(1) 在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了
　　
主键索引，MySQL就会锁定这条主键索引；如果一 条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
　　
在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking
　　
(2) 把死锁信息写入到error.log。
　　
  set global innodb_print_all_deadlocks = 1;