InnoDB Consistent Read

在InnoDB中,数据库通过multi-versioning为查询提供数据快照,查询语句不受非当前事务下的其他操作影响。在默认的REPEATABLE READ隔离等级下,事务中的一致性读操作都将基于该事务第一次执行查询时所建立的快照,这其中有两个值得注意的问题:

数据库的虚拟状态

由于事务中只能看到当前事务内对数据库的修改,因此要是在事务A中对数据库进行了修改,同时另一事务B也进行了修改操作并结束事务的话,事务A中此后看到的数据状态并没有真正存在过。以具体例子说明,首先创建一个测试数据库:

InnoDB Initialize Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
value SMALLINT,
msg CHAR(255)
);


CREATE INDEX idx ON test (value);

START TRANSACTION;
INSERT INTO test (value, msg) VALUES
(1, 'Hello'),
(3, 'World'),
(4, 'Foo'),
(5, 'Bar');

COMMIT;

在两个客户端中执行以下命令,按A.1 -> B.1 -> B.2 -> A.2的顺序执行:

MySQL Client A
1
2
3
4
5
START TRANSACTION;
SELECT * FROM test;
UPDATE test SET msg = 'Apple' WHERE value = 3; -- Statement A.1
SELECT * FROM test; -- Statement A.2
COMMIT;
MySQL Client B
1
2
3
START TRANSACTION;
UPDATE test SET msg = 'Banana' WHERE value = 4; -- Statement B.1
COMMIT; -- Statement B.2

根据一致性读原理,A.2的结果为(1, 'Hello'), (3, 'Apple'), (4, 'Foo'), (5, 'Bar'),但实际上该状态在任意时刻都不曾真正存在过。在事务A提交前,数据库的状态为(1, 'Hello'), (3, 'World'), (4, 'Banana'), (5, 'Bar'),在事务A提交后,状态为(1, 'Hello'), (3, 'Apple'), (4, 'Banana'), (5, 'Bar')

Snapshot的有效范围

数据库快照只适用于SELECT操作,对于DML(如UPDATEDELETE)来说,事务中的操作会影响其他事务提交的数据,同时操作所修改的数据也会在当前事务中变得可见。还是以具体例子说明,在默认的REPEATABLE READ级别下:

MySQL Client A
1
2
3
4
5
START TRANSACTION;
SELECT * FROM test WHERE value = 6; -- Statement A.1
SELECT * FROM test WHERE value = 6; -- Statement A.2
DELETE FROM test WHERE value = 6; -- Statement A.3
COMMIT;
MySQL Client B
1
2
3
START TRANSACTION;
INSERT INTO test (value, msg) VALUES (6, 'Poor'); -- Statement B.1
COMMIT; -- Statement B.2

按照A.1 -> B.1 -> B.2 -> A.2 -> A.3的顺序执行,在B.2执行完毕后,A.2在其所在的事务中依然无法读出(6, ‘Poor’),但当执行A.3将得到1 row affected的结果,在Client B中插入的数据被Client A删除了。

再看一个例子:

MySQL Client C
1
2
3
4
5
START TRANSACTION;
SELECT * FROM test WHERE value = 7; -- Statement C.1
UPDATE test SET msg = 'Fellow' WHERE value = 7; -- Statement C.2
SELECT * FROM test WHERE value = 7; -- Statement C.3
COMMIT;
MySQL Client D
1
2
3
START TRANSACTION;
INSERT INTO test (value, msg) VALUES (7, 'Guy'); -- Statement D.1
COMMIT; -- Statement D.2

各语句执行顺序C.1 -> D.1 -> D.2 -> C.2 -> C.3,可以看到在C.2执行完后,Client D插入的数据可以被C.3查询出来了!

最后,只能说RMDB真心复杂,各种一致性、隔离等级和锁的问题稍不留神就会掉进坑里。