写在前面
接上文，本文主要是根据前两篇的场景，扩展重现篇章最开始的死锁问题。
正文
上文说的，RR使用gap锁和next-key锁能解决大部分幻读问题，还有某些特定场景还是会出现幻读，如下：
步骤 事务1 事务2 1 select * from lotest where type=’B20’; success 1 row 2 select * from lotest where type=’B20’; success 1 row 3 insert into lotest (type) values(‘B20’); success 4 commit; 5 select * from lotest where type=’B20’; success 1 row 6 update lotest set type = ‘B30’ where type =’B20’; SUCCESS 7 select * from lotest where type=’B30’; success 2 row
这种场景就会产生幻读。
为什么？：因为RR下select都是快照读且没有锁控制，update是当前更新，所以update会更新到select快照读之外的数据。
解决方法：在第一步select的时候需要加锁控制，如：
1
2
3
4#第一种
select * from lotest where type='B20' lock in share mode;
#第二种
select * from lotest where type='B20' for update;这样的话，在其他事务并行insert的时候，会产生如下lock waiting ,问题解决
1
2
3
4
5
6
7
8
9
10---TRANSACTION 46828557, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 312891, OS thread handle 140714847319808, query id 16213555 10.11.33.169 root update
insert into lotest (type) values('B20')
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1647 page no 4 n bits 88 index idx_order_id of table `aiot_iiap_luoshu`.`lotest` trx id 46828557 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 423330; asc B30;;
1: len 6; hex 00000010f442; asc B;;重点，正是因为有了gap锁，且 gap不管是x还是s锁都可以共享存在，所以在特定情况下，会出现死锁，也就是文章开头出现的那个错误。示例步骤如下：
步骤 事务1 事务2 1 select * from lotest where type=’B20’ lock in share mode;(注：/update/delete也可,但前置条件：数据库没有该记录，如果有该记录，那么会获得record x锁，因为record x不能共享。导致事务2无法往下执行，进而无法重现死锁) success empty 2 select * from lotest where type=’B20’ lock in share mode;/update/delete success empty 3 insert into lotest (type) values(‘B20’); waiting 4 insert into lotest (type) values(‘B20’); 5 deadlock success
引擎日志如下
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*** (1) TRANSACTION:
TRANSACTION 46828905, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 312891, OS thread handle 140714847319808, query id 16219602 10.11.33.169 root update
insert into lotest (type) values('B20')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1647 page no 4 n bits 88 index idx_order_id of table `aiot_iiap_luoshu`.`lotest` trx id 46828905 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 423330; asc B30;;
1: len 6; hex 00000010f442; asc B;;
*** (2) TRANSACTION:
TRANSACTION 46828909, ACTIVE 1050 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 312903, OS thread handle 140715008841472, query id 16219644 10.11.33.169 root update
insert into lotest (type) values('B20')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1647 page no 4 n bits 88 index idx_order_id of table `aiot_iiap_luoshu`.`lotest` trx id 46828909 lock mode S locks gap before rec
Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 423330; asc B30;;
1: len 6; hex 00000010f442; asc B;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1647 page no 4 n bits 88 index idx_order_id of table `aiot_iiap_luoshu`.`lotest` trx id 46828909 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 423330; asc B30;;
1: len 6; hex 00000010f442; asc B;;
*** WE ROLL BACK TRANSACTION (2)日志和文章开头的错误日志如出一辙！问题复现！
未完待续……