mysql 死锁详细分析（三）

1、测试场景

• MySQL版本： 5.7.34
• 事务级别： RR（可重复读）
• 提交模式：手动

2、测试数据准备

-- 准备表
CREATE TABLE `user_1` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int DEFAULT NULL COMMENT '年龄',
  `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

-- 先插入2条基础数据，并提交事务
insert into user_1 values (null, 10, 'aa');
insert into user_1 values (null, 20, 'ee');
1
2
3
4
5
6
7
8
9
10
11
12

注意：

• name是普通索引
• age没有索引

3、操作前检查

• 检查事务模式和事务隔离级别
• 使用DataGrip打开两个会话窗口
• 已写入的测试数据
• 原始数据如下：

mysql> select * from user_1;
+----+------+------+
| id | age  | name |
+----+------+------+
|  1 |   10 | aa   |
|  2 |   20 | ee   |
+----+------+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

4、死锁测试操作

① 会话1先执行：执行成功

-- step1：update name= 'ee'，不提交事务
## 执行成功
update user_1 set age = 21 where name= 'ee';
1
2
3

② 会话2再执行: 执行成功

-- step2：update name= 'aa'，不提交事务
## 执行被阻塞
update user_1 set age = 11 where name= 'aa';
1
2
3

③ 会话1再执行：执行被阻塞**

-- step3：插入cc，不提交
insert into user_1 values (null, 15, 'cc');
1
2

问题：step3中，此处会话1为什么被阻塞，在等待什么？

④ 会话2再执行：发生死锁**

-- step4：插入cc，不提交
insert into user_1 values (null, 30, 'gg');
1
2

5、step3会话1被阻塞和step4死锁分析

5.1 查看innodb当前的锁（字段含义见 8.3）

• 注意：以下操作是执行完step3，不提交事务

mysql> select * from information_schema.INNODB_LOCKS;
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 5900:33:4:3 | 5900        | X,GAP     | RECORD    | `mysql`.`user_1` | idx_name   |         33 |         4 |        3 | 'ee', 2   |
| 5901:33:4:3 | 5901        | X,GAP     | RECORD    | `mysql`.`user_1` | idx_name   |         33 |         4 |        3 | 'ee', 2   |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

5.2 查看锁的等待关系（字段含义见 8.4）

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5900              | 5900:33:4:3       | 5901            | 5901:33:4:3      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

5.3 查看当前运行的所有事务信息

（由于列比较多，所以改成竖排显示，（字段含义见 8.2））

mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 5901
                 trx_state: RUNNING
               trx_started: 2022-08-25 20:06:05
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 5
       trx_mysql_thread_id: 3
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 5900
                 trx_state: LOCK WAIT
               trx_started: 2022-08-25 20:06:01
     trx_requested_lock_id: 5900:33:4:3
          trx_wait_started: 2022-08-25 20:06:09
                trx_weight: 6
       trx_mysql_thread_id: 2
                 trx_query: /* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 4
         trx_rows_modified: 2
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

5.4 分析：

1. 先查看information_schema.innodb_trx

- 会话1主要信息：【trx_id:5900 trx_state: LOCK WAIT trx_started:2022-08-25 20:06:01】
- 会话2主要信息：【trx_id:5901 trx_state: RUNNING   trx_started:2022-08-25 20:06:05】
1
2

• 会话1的语句，事务5900在等待锁状态，请求的锁id是：【trx_requested_lock_id: 5900:33:4:3】
• 会话2的语句，事务5901是运行状态，没有等待锁

2. 再查看锁等待关系information_schema.INNODB_LOCK_WAITS

+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5900              | 5900:33:4:3       | 5901            | 5901:33:4:3      |
+-------------------+-------------------+-----------------+------------------+
1
2
3
4
5

• 当前持有锁的会话2事务是5901，请求锁的会话1事务是5900
• 会话1在等待会话2释放锁。

问题：会话1为什么要等待会话2释放锁？

3. 再查看当前innodb所有的锁信息information_schema.INNODB_LOCKS

+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 5900:33:4:3 | 5900        | X,GAP     | RECORD    | `mysql`.`user_1` | idx_name   |         33 |         4 |        3 | 'ee', 2   |
| 5901:33:4:3 | 5901        | X,GAP     | RECORD    | `mysql`.`user_1` | idx_name   |         33 |         4 |        3 | 'ee', 2   |
+-------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
1
2
3
4
5
6

• 会话1和会话2的事务都是行级别排他锁和间隙锁（X,GAP）
• 并且锁住的索引是普通索引idx_name 【lock_index：idx_name】
• 锁的的字段值都是 ‘ee’, 2 【lock_data ： ‘ee’, 2】

进一步看information_schema.innodb_trx，从上面看到

- 会话1的语句，事务5900在等待锁，   锁定的行数是3【trx_rows_locked: 4 trx_rows_modified: 2】
- 会话2的语句，事务5901是运行状态， 锁定的行数是3【trx_rows_locked: 3 trx_rows_modified: 1】
1
2

4. 会话1和会话2各自持有的锁

① 会话1先执行：执行成功

-- step1：update name= 'ee'，不提交事务
## 执行成功
update user_1 set age = 21 where name= 'ee';
1
2
3

• name列上有普通的索引，所以UPDATE 操作不会进行表锁，而是的每条记录上设置一个排他的 next-key 锁。
• 当前表里有两条数据。可见锁定的范围是：(10,20]，(20,+∝)
• 锁定总范围：(10,+∝)
• 
  ② 会话2再执行: 执行成功

-- step2：update name= 'aa'，不提交事务
## 执行被阻塞
update user_1 set age = 11 where name= 'aa';
1
2
3

• 同样，当前表里有两条数据。可见锁定的范围是：(-∝,10]，(10,20)
• 锁定总范围：(-∝,20)
  
  ③ 会话1再执行：执行被阻塞**

-- step3：插入cc，不提交
insert into user_1 values (null, 15, 'cc');
1
2

• 复习下INSERT 加锁整个流程
• 首先对插入的间隙加插入意向锁(Insert Intension Locks)
• 如果该间隙已被加上了GAP锁或 Next-Key 锁，则加锁失败进入等待。如果没有，则加锁成功，表示可以插入。
  
• 可以看到‘cc’是在’aa’和’ee’的gap之间，并且这个gap已经被加了间隙锁，所以step3会进入等待。
• 等待哪个锁呢？因为该插入是会话1执行的，所以此时等待的是会话2持有的间隙锁：(10,20)

④ 会话2再执行：发生死锁

-- step4：插入cc，不提交
insert into user_1 values (null, 30, 'gg');
1
2

• 可以看到’gg’是在’ee’之后，并且这个gap也已经被加了间隙锁，所以step4会进入等待。
• 等待哪个锁呢？因为该插入是会话2执行的，所以此时等待的是会话1持有的间隙锁：(20,+∝)
• 接着便会发生死锁，由于mysql有死锁自动检测算法，会自动释放权重小的事务。
• 为什么会发生死锁呢？如下图分析：

• 在T4时刻，会话1在等待会话2的锁(10,20)，而会话2也在等待会话1的锁 (20,+∝)，进入了相互等待，产生了死锁。

7、生产死锁原因分析

• 在发生死锁时，如果开启死锁检测，InnoDB一般都能通过算法(wait-for graph)自动检测到。
• 所以 生产上如果发生死锁，直接看到的是mysql自动检测到死锁，并且可已经自动释放权重小的事务。看不到当时事务的情况和锁等待的关系。此时分析死锁，要通过如下方法：

开启主动死锁检测，默认开启：
innodb_deadlock_detect=on
1
2

7.1 查看死锁的详细信息

mysql> show engine innodb status;
 
2022-08-25 20:06:36 0x4c38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 7898 srv_idle
srv_master_thread log flush and writes: 7906
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 62
OS WAIT ARRAY INFO: signal count 59
RW-shared spins 0, rounds 63, OS waits 31
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 63.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-08-25 20:06:33 0x82a8
*** (1) TRANSACTION:🧡
TRANSACTION 5900, ACTIVE 32 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 32748, query id 312 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:❌
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5900 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 6565; asc ee;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:💚
TRANSACTION 5901, ACTIVE 28 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 33448, query id 325 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 30, 'gg')
*** (2) HOLDS THE LOCK(S):✅
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 6565; asc ee;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:❎
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)🆗
------------
TRANSACTIONS
------------
Trx id counter 5907
Purge done for trx's n:o < 5907 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284557950381568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284557950379824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5901, ACTIVE 31 sec
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 33448, query id 334 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
429 OS file reads, 198 OS file writes, 130 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.96 writes/s, 0.75 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.74 non-hash searches/s
---
LOG
---
Log sequence number 3401095
Log flushed up to   3401095
Pages flushed up to 3401095
Last checkpoint at  3400989
0 pending log flushes, 0 pending chkp writes
87 log i/o's done, 0.46 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137297920
Dictionary memory allocated 105651
Buffer pool size   8192
Free buffers       7765
Database pages     427
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 393, created 34, written 89
0.00 reads/s, 0.00 creates/s, 0.42 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 427, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=15540, Main thread ID=18340, state: sleeping
Number of rows inserted 2, updated 6, deleted 0, read 24
0.02 inserts/s, 0.04 updates/s, 0.00 deletes/s, 0.07 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

7.2 逐步分析

1. 查看最近检测到的死锁

------------------------
LATEST DETECTED DEADLOCK
------------------------
1
2
3

2. 查看事务5900发生死锁时的信息

2022-08-25 20:06:33 0x82a8
*** (1) TRANSACTION: 
-- **事务5900，在执行insert操作，活跃了32秒**
TRANSACTION 5900, ACTIVE 32 sec inserting
-- **该事务使用一个mysql表，有一个锁**
mysql tables in use 1, locked 1 
-- **当前有4行记录被锁**
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
-- **当前事务的线程id、查询id和数据库ip及账号**
MySQL thread id 2, OS thread handle 32748, query id 312 localhost 127.0.0.1 root update
-- **当前事务发生死锁时的执行语句**
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 15, 'cc')

-- **当前在等待的锁**
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- **索引idx_name的插入意向间隙锁在等待。等待的锁位置是“heap no 3”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5900 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-- **等待锁的范围是(ee,未知)**
 0: len 2; hex 6565; asc ee;;
 1: len 4; hex 80000002; asc     ;;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3. 查看事务5901发生死锁时的信息

*** (2) TRANSACTION:
**事务5900，在执行insert操作，活跃了32秒**
TRANSACTION 5901, ACTIVE 28 sec inserting
-- **该事务使用一个mysql表，有一个锁**
mysql tables in use 1, locked 1
-- **当前有4行记录被锁**
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
-- **当前事务的线程id、查询id和数据库ip及账号**
MySQL thread id 3, OS thread handle 33448, query id 325 localhost 127.0.0.1 root update
-- **当前事务发生死锁时的执行语句**
/* ApplicationName=DataGrip 2021.1 */ insert into user_1 values (null, 30, 'gg')

-- **当前事务持有的锁**
*** (2) HOLDS THE LOCK(S):
-- **持有的锁是索引idx_name的间隙排他锁。持有的锁位置是“heap no 3”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-- **持有锁的范围是(ee,未知)**
 0: len 2; hex 6565; asc ee;;
 1: len 4; hex 80000002; asc     ;;

-- **等待的锁**
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- **索引idx_name的插入意向间隙锁在等待。等待的锁位置是“heap no 1”**
RECORD LOCKS space id 33 page no 4 n bits 72 index idx_name of table `mysql`.`user_1` trx id 5901 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
-- **等待的锁范围是（未知，+∝）**
 0: len 8; hex 73757072656d756d; asc supremum;;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

4. 分析

• 通过该日志可以看到，事务5900在等待锁的位置是“heap no 3”，而此位置的锁在事务5901那里，而5901等待的锁位置是“heap no 1”，可能是死锁已经释放，所以日志看不出改位置的锁在事务5900那里。

5. 检测到死锁，并回滚

*** WE ROLL BACK TRANSACTION (1)
1

• 此时回滚的事务1。

8、mysql分析锁的参考命令

-- 查看数据库版本
select version();
 
-- 查看默认值
show variables like '%engine%';
 
-- 查看事务隔离级别
show global variables like 'tx_isolation';
 
-- 查看是否开启自动提交
show variables like 'autocommit';
 
-- 查看是否开启间隙锁
show variables like 'innodb_locks_unsafe_for_binlog';
 
-- 查看锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;
 
-- 查看innodb当前的事务和锁
select * from information_schema.innodb_trx;
 
mysql5.6版本后，可以通过下面的命令来进行锁的状态监控
set global innodb_status_output=on;
set global innodb_status_output_locks=on;
 
-- 开启锁的状态监控后，查看详细的innodeb信息
show engine innodb status;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

8.1关闭间隙锁：

-- 1、查看是否开启间隙锁：show variables like 'innodb_locks_unsafe_for_binlog';
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
-- 注意：默认值为OFF(0)，即启用gap lock。
-- 这个参数最主要的作用就是控制innodb是否对gap加锁。
-- 但是，这一设置变更并不影响外键和唯一索引（含主键）对gap进行加锁的需要。
 
-- 2、关闭间隙锁(gap lock)方法:
-- 在my.cnf里面的[mysqld]添加：
innodb_locks_unsafe_for_binlog = 1
-- 重启MySQL后生效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

8.2 information_schema.innodb_trx字段说明–当前运行的所有事务

8.3 information_schema.innodb_locks字段说明–当前出现的锁

8.4 information_schema.innodb_lock_waits字段说明–锁等待的对应关系

9、锁的优化建议

• 尽量使用INSERT … ON DUPLICATE KEY UPDATE代替REPLACE INTO
• REPLACE INTO有很多副作用，比如自增id会快速增大； slave 提升为 master后，可能会发生duplicate key error
• 在能正确完成业务的前提下，为确保效率，尽量使用较低的隔离级别（必须避免脏读）
• 设计合理的索引并尽量使用索引访问数据，使加锁更准确，减少锁冲突的机会，提高并发能力
• 选择合理的事务大小，小事务发生锁冲突的概率小（事务越大，包含的SQL越多，可能包含更多的表资源和行资源的锁，增大了锁冲突的概率）
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以- 固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响（其实等值查询也会加间隙锁）
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁（在已提交读和可重复读隔离级别，MVCC提供了读取机制，不需要手动加锁）