不同数据库Serializable隔离级别的区别

不同数据库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是一种乐观方式，保证事务可以正常执行，只在提交时来判断事务是否存在串行化异常，从而决定事务提交还是回滚。