基本概念
当前读与快照读
在MVCC中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且对返回的记录，都会加上锁，保证在事务结束前，这条数据都是最新版本。
- 快照读：简单的select操作，属于快照读，不加锁(Serializable除外)。
1
select * from table where ?;
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
1 2 3 4 5
select * from table where ? lock in share mode; select * from table where ? for update; insert into table values (); update table set ? where ?; delete from table where ?;
MySQL的隔离级别与加锁机制
-
Read Uncommitted 会发生脏读，即读到其他事务未提交的数据变更（插入、删除或更新）
-
Read Committed (RC) 针对当前读，RC隔离级别保证对读取到的记录加锁 (Gap Locking)，但是不提供range-locks，存在幻读现象。
-
Repeatable Read (RR) 针对当前读，RR隔离级别保证对读取到的记录加锁 (Record Locking)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (Gap Locking)，不存在幻读现象。
注：这里做一个解释，对于数据库来说RR隔离级别是可能存在幻读的，但是在MySQL数据库的实现中，在RR隔离级别下可以使用Next-Key Locking来解决对「已经上锁」的行数据的新增和删除，进而避免出现幻读现象。
- Serializable 所有的读操作均为退化为当前读，读写冲突，因此并发度急剧下降，不考虑。
测试脚本
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
-- 基本操作 --
-- 查询事务隔离级别，默认是RR
show variables like '%isolation%';
-- 设置事务隔离级别为RC
set session transaction isolation level read committed;
-- 再次查询事务隔离级别，已经改为是RC，但仅对当前session有效
show variables like '%isolation%';
-- 数据初始化 --
begin;
drop table if exists user;
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
`address` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_email` (`email`),
KEY `idx_age` (`age`)
);
insert into user (email, age, address) values ("[email protected]", 18, "address1");
insert into user (email, age, address) values ("[email protected]", 20, "address2");
insert into user (email, age, address) values ("[email protected]", 20, "address3");
commit;
-- 检查是否插入成功
select * from user;
-- 一、trx_id示例
begin;
-- 查询事务ID，此时结果为空
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
-- 执行SELECT
select * from user;
-- 再次查询事务ID，此时已经生成，也就是说事务ID是在SQL执行之后生成的
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
SHOW ENGINE INNODB STATUS;
update user set age = 22 where id = 3;
-- 查询事务id
SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
-- INNODB 引擎状态，可以看到各个session的事务ID
SHOW ENGINE INNODB STATUS;
commit;
-- 二、可重复读、不可重复读示例
-- session1
set session transaction isolation level read committed;
begin;
-- session2
set session transaction isolation level repeatable read;
begin;
-- session1
select * from user;
-- session2
select * from user;
-- session3
begin;
insert into user (email, age, address) values ("[email protected]", 30, "address4");
commit;
-- session1 这里因为是RC，使用当前读，所以可以读到trx3提交的新数据，这里如果是证明不可重复读的话应该使用update而不是insert
select * from user;
commit;
-- session2 这里因为是RR，使用MVCC，内部有read-view的可见性限制，所以不会读到比当前事务ID大的trx3提交的新数据
select * from user;
commit;
-- 总结：RC下发生了幻读，即读到了新插入的row，RR下没有（这里只讨论select的结果）
-- 三、快照读幻读示例
-- session1
set session transaction isolation level repeatable read;
begin;
-- 这里使用快照读
select * from user;
-- session2
begin;
insert into user (email, age, address) values ("[email protected]", 30, "address4");
commit;
select * from user;
-- session1
select * from user; -- 这里读不到test4@的数据，因为是RR，使用MVCC，内部有read-view的可见性限制，所以不会读到比当前事务ID大的trx2提交的新数据
-- 这里会失败，因为email唯一索引冲突了，数据库中已经存在了对应的数据，只不过是由于RR导致当前事务select「读不到」而已
insert into user (email, age, address) values ("[email protected]", 30, "address4");
-- 尝试更新那个「看不见的」记录，其中id=4是我们已知的，更新会成功
update user set age = 11 where id = 4;
-- 再次查询就可以查到了，因为当前事务已经通过update操作更新了对应的数据，造成该条记录对当前事务可见
select * from user;
commit;
-- 总结：RR下可以通过更新「读不到的记录」来「读到」这些row，这种不能算幻读
-- 四、当前读幻读示例
-- RC当前读
-- session1
set session transaction isolation level read committed;
begin;
-- 这里会对所有满足条件的age=20的记录加锁，因为是RC，所以没有GAP锁
delete from user where age = 20;
select * from user;
-- session2
set session transaction isolation level read committed;
begin;
-- 因为trx1没有加GAP锁，所以之类可以插入age=20的记录
insert into user (email, age, address) values ("[email protected]", 20, "address4");
select * from user; -- 可以查到4条数据，可以读到trx1的删除数据，因为是RC，trx1未提交所以没影响trx2
commit;
-- session1
select * from user; -- 可以读到trx2新插入的数据，虽然trx1是当前读，但是并未添加相应的next-key锁，没有阻止trx2的新数据插入，发生幻读
commit;
-- 总结：RC下不会对符合条件的记录的索引加GAP锁，所以可以插入新的记录，故而发生幻读。
--RR
-- session1
set session transaction isolation level repeatable read;
begin;
delete from user where age = 20;
select * from user;
-- session2
begin;
-- 这里会阻塞，因为trx1在age=20周围加了GAP锁：
-- 非唯一索引，首先，通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁；
-- 然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
insert into user (email, age, address) values ("[email protected]", 20, "address4");
-- 直到超时，ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 此时如果查询可以看到3条记录
commit;
-- session1
-- 此时只能看到1条记录，另外两条被删除了
select * from user;
commit;
-- 总结：RR下对对非唯一索引做条件判断时，会给对应的行加Gap锁，导致其他事务落入该区间内的索引不能被操作，包括插入删除和更新
-- 唯一索引（id）+RC
-- session1
set session transaction isolation level read committed;
begin;
delete from user where email = "[email protected]";
-- session2
begin;
-- 可以读到，因为trx2是RR
select * from user where email = "[email protected]";
-- 尝试更新这个记录的age，会阻塞直到超时，因为email是唯一索引已经被trx1锁住了，同时也会在对应的主键索引上加锁
-- 注意这里操作的id=3就是trx1中操作的email的同一行记录
-- 使用email作为条件也是一样，它也是unique的index
update user set age = 40 where id = 3;
-- session1
commit;
-- session2
commit;
-- 总结：在RC下对唯一索引的字段在条件判断时，会对对应的主键加行锁
-- 无索引（address）+RC
-- session1
set session transaction isolation level read committed;
begin;
-- 由于address字段无索引，所以Innodb会对所有行进行加锁，由MySQL server进行判断并释放锁
delete from user where address = "address3";
-- session2
set session transaction isolation level read committed;
begin;
-- 这一行会成功，因为这一行没有加锁（先加了后释放了）
update user set age = 10 where address = "address2";
-- 这一行同样会被阻塞，原因是它已经被trx1的语句加了锁了，全部符合条件的都加锁了
update user set age = 10 where address = "address3";
-- session1
commit;
-- session2
commit;
-- 总结：在RC下对无索引的字段在条件判断时，当前读操作，会对所有符合条件的行加行锁
-- 非唯一索引（age）+RR
-- session1
set session transaction isolation level repeatable read;
begin;
-- 这里会对age=20的所有行上锁以及row外的GAP锁
delete from user where age = 20;
-- session2
set session transaction isolation level repeatable read;
begin;
-- 这里会阻塞，因为trx1中已经锁住了age=20的记录以及加上了GAP锁，所以这里18已经落入锁区间，即20左边的区域(18,20)
insert into user (email, age, address) values ("[email protected]", 18, "address4");
-- 这样就会成功，因为age=17的索引并不在age=20的GAP锁范围内
insert into user (email, age, address) values ("[email protected]", 17, "address4");
-- session1
commit;
-- session2
commit;
-- 总结：在RR下对于非唯一索引的字段在条件判断时，对于UPDATE/DELETE/INSERT来说，会加对应记录的Row锁和GAP锁，其他落在该区域内的索引值只能阻塞等待
-- 无索引（address）+RR
-- session1
set session transaction isolation level repeatable read;
begin;
-- 没有索引，那么会锁上表中的所有记录，同时会锁上主键索引上的所有GAP，杜绝所有的并发更新操作
delete from user where address = "address3";
-- session2
set session transaction isolation level repeatable read;
begin;
-- 这里会阻塞，原因是主键已经被加上了GAP锁，所以新的插入不能执行成功
insert into user (email, age, address) values ("[email protected]", 18, "address4");
-- session1
commit;
-- session2
commit;
-- 总结：在RR下对无索引的字段在条件判断时，对于UPDATE/DELETE/INSERT来说，会锁住记录对应的主键索引并加GAP锁，这样就无法对标进行任何写操作，包括插入、更新和删除
-- 死锁 简单示例
-- session1
begin;
delete from user where id = 1;
-- session2
begin;
delete from user where id = 3;
-- session1
-- 这里会阻塞，因为id=3的主键上已经被trx1上了x锁
delete from user where id = 3;
-- seession2
-- 这里MySQL判断发生了死锁，中断了一个trx
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
delete from user where id = 1;
-- session1
rollback;
-- session2;
rollback;
-- 五、死锁 insert示例
drop table if exists t1;
begin;
create table t1 (
`id` bigint not null auto_increment,
primary key (`id`)
);
insert into t1 values(1);
insert into t1 values(5);
commit;
select * from t1;
-- session1
begin;
insert into t1 values (2);
-- sessioin2
begin;
-- 这里会阻塞，因为trx1已经上了gap锁
insert into t1 values (2);
-- session3
begin;
-- 这里会阻塞，因为trx1已经上了gap锁
insert into t1 values (2);
-- session1;
-- 此时回滚，trx2和trx3收到通知，MySQL自动中断一个trx，因为发生了死锁
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
rollback;
--session2;
rollback;
--session3;
rollback;
本文首次发布于 LiuShuo’s Blog, 转载请保留原文链接.
