不同数据库Serializable隔离级别的区别
概述
Serializable(可串行化)是数据库最高等级的隔离级别。从概念上来讲,Serializable的定义是:多个并行的事务,在提交时其结果与串行执行完全相同。当前主流数据库几乎都支持名为Serializable的隔离级别,为什么要用加上名为?因为不同数据库对于Serializable的实现方式不同,有些数据库的Serializable其实做不到真正意义的可串行化。下面,我们来分别看看MySQL、PostgreSQL、Oracle在可串行化隔离级别下,针对同一个Write Skew(写偏斜)场景的表现。
这个场景,是一个很著名的场景(DDIA以及诸多论文均有提及):医院有一个值班系统来管理医生值班的情况。医生可以请假,但前提是确保至少有一位医生在值班。
假设,我们当前有两名医生在值班:
name | on_call |
---|---|
Jill | 1 |
Ada | 1 |
现在,Jill希望请假,于是正常的流程如下:
begin;
x <- SELECT COUNT(*) FROM doctors WHERE on_call = 1;
IF x >= 2 THEN
UPDATE doctors SET on_call = 0 WHERE name = 'Jill';
COMMIT;
如果Jill和Ada先后请假,那么在Ada请假时,她就会发现当前只有她一个医生在值班,所以不能请假。如果Jill和Ada同时请假呢?我们来看看三个数据库在这个场景下的表现。
准备工作
我们创建一张doctors表,并插入两个医生:
-- MySQL、PostgreSQL、Oracle通用语法
CREATE TABLE doctors(name char(32), on_call int);
INSERT INTO doctors VALUES('Jill',1);
INSERT INTO doctors VALUES('Ada',1);
commit;
Oracle
我们按照如下流程进行测试(执行顺序,从左至右,从上至下):
Jill | Ada |
---|---|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | SELECT COUNT(*) FROM doctors WHERE on_call = 1; |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | |
commit; | UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; |
commit; |
在Jill和Ada执行完SELECT COUNT(*)
之后,发现结果均为2。于是Jill执行了Update并提交。紧接着Ada执行Update操作,然后出现如下错误:
原因是,Oracle检测到了串行化异常。因为如果Ada成功执行Update并提交,那么将没有医生值班。
现在,我们改变一下执行顺序,再来看看:
Jill | Ada |
---|---|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | SELECT COUNT(*) FROM doctors WHERE on_call = 1; |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; |
commit; | commit; |
按照这个顺序,Jill和Ada都能正常执行成功并提交!最终导致没有医生值班。
Oracle的串行化本质上还是基于快照隔离,所以存在串行化异常,没有实现真正意义上的可串行化。
MySQL
接下来,我们再来看看MySQL的表现。
Jill | Ada |
---|---|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
begin; | begin; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | SELECT COUNT(*) FROM doctors WHERE on_call = 1; |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | |
commit; | UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; |
commit; |
在Jill和Ada执行完SELECT COUNT(*)
之后,发现结果均为2。于是Jill执行了Update,此时这个Update操作会被阻塞:
如果,这个时候Ada再来执行Update,则会发生死锁,MySQL的死锁检测机制会回滚Ada发起的事务,同时Jill发起的Update操作执行完成。
接下来,我们改变执行顺序:
Jill | Ada |
---|---|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
begin; | begin; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | SELECT COUNT(*) FROM doctors WHERE on_call = 1; |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; |
commit; | commit; |
显然这种执行顺序和之前没有任何区别。接着,我们再改变一下执行顺序:
Jill | Ada |
---|---|
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
begin; | begin; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | |
UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; | |
commit; | commit; |
在这种情况下,Jill可以正常执行SELECT和Update,当Ada执行SELECT时,查询会被阻塞,直到Jill commit事务之后,Ada才可以完成查询,而此时查询结果为1,所以Ada无法请假。
不难看出,MySQL的Serializable隔离级别很好的解决了写偏斜,实现了真正的可串行化。MySQL在Serializable隔离级别下,查询操作会阻塞修改操作。所以MySQL采用的是传统的两阶段锁(2 Phase Locking,2PL)的方式来实现串行化。
PostgreSQL
最后,我们来看看PostgreSQL的表现。
Jill | Ada |
---|---|
begin; | begin; |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
SELECT COUNT(*) FROM doctors WHERE on_call = 1; | SELECT COUNT(*) FROM doctors WHERE on_call = 1; |
UPDATE doctors SET on_call = 0 WHERE name = ‘Jill’; | |
commit; | UPDATE doctors SET on_call = 0 WHERE name = ‘Ada’; |
commit; |
在PostgreSQL中,Jill可以正常执行并提交事务,Ada可以正常执行,但在提交时会报错:
原因是,PostgreSQL检测到Ada提交的事务违反了可串行化原则,所以会回滚事务并报错。将执行顺序,进行修改后,依然会得到上述结果。不难看出,PostgreSQL也实现了真正的可串行化。
PostgreSQL采用的是一种称为可串行化快照隔离(Serializable Snapshot Isolation,SSI)的机制来实现可串行化。PostgreSQL在事务执行过程中,采用快照隔离来处理可见性问题,在事务提交时,会检测事务是否满足可串行化(比如,之前查询的元组是否被修改等),然后决定是提交事务还是回滚事务。
对比MySQL和PostgreSQL。MySQL采用的2LP是一种悲观的方式,在事务执行过程中,通过锁来阻塞其他的冲突事务的操作。而PostgreSQL的SSI是一种乐观方式,保证事务可以正常执行,只在提交时来判断事务是否存在串行化异常,从而决定事务提交还是回滚。