Postgresql事务级别为SERIALIZABLE的特性实测

一、SERIALIZABLE隔离级别介绍

SERIALIZABLE事务隔离级别，中文叫串行化，他是postgresql事务级别中最高一级，postgresql默认事务隔离级别是read committed,不过这个可以通过配置postgresql.conf中的default_transaction_isolation参数来设置默认事务隔离级别，采用SERIALIZABLE事务隔离级别可以防止脏读（dirty read），非重复读（nonrepeatable read），和幻像（phantom read），一个事务如果进入了set traansaction isolation level serializable;就会独占这个事务需要的所有资源，其他任何修改同样资源的请求都会被推出，不过讲述postgresql事务隔离级别SERIALIZABLE的特性时是必需要结合postgresql的另一个实现机制MVCC(多版本控制 Multiversion Concurrency Control, MVCC)一起来说明。

脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改时数据就叫脏读

非重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，如果每次返回不同的结果集，就叫非重复读。

幻像（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，就叫幻像读。

二、测试环境

opensuse11.4 64bit ，postgresql 9.1 源码编译安装 autocommit

1. Create Session A
   

   postgres@T09:~/data> createdb -h /data/pgsql/data/ kyle
   postgres@T09:~/data> psql -h /data/pgsql/data/ kyle
   psql (9.1.0)
   Type "help" for help.
   
   kyle=# CREATE TABLE t1(id integer not null, sl 
   float8 not null,primary key(id));
   NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
   CREATE TABLE
   kyle=# insert into t1 values(1,10);
   INSERT 0 1
   kyle=# insert into t1 values(2,20);
   INSERT 0 1
   kyle=# insert into t1 values(3,30);
   kyle=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   SET

   
   

   -- 上面是创建一个实验表和三条实验记录，并且将SESSION A连接的事务隔离级别设置成SERIALIZABLE
2. Create Session B

3. postgres@T09:~> psql -h /data/pgsql/data/ kyle
   psql (9.1.0)
   Type "help" for help.
   
   kyle=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   SET

   
   

   -- 将SESSION B连接的事务隔离级别设置成SERIALIZABLE

三、开始测试

1. 测试2个事务同事更新同一条记录
   Session A：
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# UPDATE t1 SET sl=1 WHERE id =1;
   UPDATE 1
   kyle=#

   Session B:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# UPDATE t1 SET sl=2 WHERE id = 1;
   

   此时，可以看到session B的更新语句在等待SESSION A事务提交。
   Session A:继续执行下面语句
   

   kyle=# END ;
   COMMIT
   kyle=#
   ----如果此步骤Session A rollback --------------------------------
   
   kyle=# ROLLBACK ;
   ROLLBACK
   kyle=#

   Session B：窗口内看到以下错误提示信息
   

   ERROR: could not serialize access due to concurrent update
   --错误: 
   由于同步更新而无法串行访问
   ----如果Session A rollback --------------------------------
   
   kyle=# UPDATE t1 SET sl=2 where id =1;
   UPDATE 1
   kyle=#

   从上面的过程看，二个事务同时更新同一条记录在事务级别是SERIALIZABLE是符合我们的要求的
   
   
2. 测试2个事务从开始到结束有重叠
   初始状态：
   

   kyle=# select * from t1;
   id | sl
   ----+----
   2 | 20
   3 | 30
   1 | 10
   (3 rows)

   Session A：
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# UPDATE t1 SET sl=1 WHERE id=1;
   UPDATE 1
   kyle=#

   Session B:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=#

   Session A:
   

   kyle=# END ;
   COMMIT
   kyle=#

   Session B:
   

   kyle=# UPDATE t1 SET sl=2 WHERE id=1;
   UPDATE 1
   kyle=# END ;
   COMMIT
   kyle=#

   从上面的过程看，怎么在SESSION B执行UPDATE t1 SET sl=2 WHERE id=1;没有提示“无法串行访问”,这个跟ORACLE怎么不一致的，他们的事务开始到结束可是有重叠哦,不要紧张，我们再看下面这个实验
3. 重做测试2，但有些小变化
   现在的数据：
   

   kyle=# SELECT * from t1;
   id | sl
   ----+----
   2 | 20
   3 | 30
   1 | 2
   (3 rows)

   Session A:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# UPDATE t1 SET sl=1 WHERE id=1;
   UPDATE 1
   kyle=#

   Session B:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# SELECT 1;
   ?column?
   ----------
   1
   (1 row)
   
   kyle=#

   Session A:
   

   kyle=# END ;
   COMMIT
   kyle=#

   Session B:
   

   kyle=# UPDATE t1 SET sl=1 WHERE id=1;
   ERROR: could not serialize access due to concurrent update
   kyle=#

   从上面的过程看，二个事务从开始到结束有重叠时更新相同的记录时提示出错是符合SERIALIZABLE级别的，看来Postgresql有些蛋疼，发出事务后竟然还要发出一条操作语句(我测试过了，应该任意有交往的DML,DDL,DCL都行)才会产生一个MVCC的版本，这一点大家在做应用时要特别注意，建议大家在做应用时自定义一个事务开始的函数，方便以后有变化时了维护
   
   
   
4. 测试SERIALIZABLE级别的脏读、非重复读、幻像是否会产生
   初始数据：
   

   kyle=# select * from t1;
    id | sl
   ----+----
     2 | 20
     3 | 30
     1 |  1
   (3 rows)

   Create Session C:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# INSERT INTO t1 VALUES(6,60);
   INSERT 0 1
   kyle=#

   Session A:
   

   kyle=# select * from t1;
    id | sl
   ----+----
     2 | 20
     3 | 30
     1 |  1
   (3 rows)
   
   kyle=# UPDATE t1 SET sl=10 WHERE id=1;
   UPDATE 1
   kyle=#

   Session B:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=#

   Session A:
   

   kyle=# end;
   COMMIT
   kyle=# INSERT INTO t1 VALUES(4,40);
   INSERT 0 1

   Session B:
   

   kyle=# select * from t1;
    id | sl
   ----+----
     2 | 20
     3 | 30
     1 | 10
     4 | 40
   (4 rows)
   
   kyle=#

   So，我们可以看到的记录包括已经更新的记录和新增的记录,看到这样的结果是正确的，因为postgresql在事务开始后的第一条语名执行时才产生一个MVCC版本，这个版本包含所有读已提交的数据，不包括未提交的数据，即不会出现脏读
   Session A:
   

   kyle=# INSERT INTO t1 VALUES(5,50);
   INSERT 0 1
   kyle=# UPDATE t1 SET sl=3 WHERE id=3;
   UPDATE 1
   kyle=# DELETE FROM t1 WHERE id =2;
   DELETE 1
   kyle=#

   Session B:
   

   kyle=# select * from t1;
    id | sl
   ----+----
     2 | 20
     3 | 30
     1 | 10
     4 | 40
   (4 rows)
   
   kyle=#

   没错，这时SESSION C新增的记录,SESSION A删除的记录，修改的记录都没有在SESSION B中都没反映出来，即不会出现非重
   
   
   
5. 测试SERIALIZABLE、幻像会产生的问题
   Session A:
   

   kyle=# CREATE TABLE t2(zs float8);
   CREATE TABLE
   kyle=# SELECT * from t1;
    id | sl
   ----+----
     6 | 60
     1 | 10
     4 | 40
     5 | 50
     3 |  3
   (5 rows)

   Session B:
   

   kyle=# BEGIN ;
   BEGIN
   kyle=# SELECT 1;
    ?column?
   ----------
           1
   (1 row)

   Session A:
   

   kyle=# INSERT INTO t1 VALUES(2,2);
   INSERT 0 1
   kyle=#

   Session B:
   

   kyle=# INSERT INTO t2 SELECT SUM(sl) FROM t1 WHERE sl<10;
   INSERT 0 1
   kyle=# END ;
   COMMIT
   kyle=# SELECT * FROM t2;
    zs
   ----
     3
   (1 row)

   明显上面的结果不是我们所需要的，为了保证不幻像，但结果却是不正确的，这里最好效果应该是报错，提示无法串行访问，然后退出，不过这样系统的开销可能很可观
   
   
   

Oracle下，用JDBC连接

Oracle没有Begin语句，以其他语句开始事务。

例如一个修改数据库的DML语句，此外DDL如果成功会提交当前事务。

set transaction语句也会开始一个事务：

set transaction isolation level SERIALIZABLE

1、测试二个事务同时更新同一条记录

事务B挂起等待，此时：

事务A提交，事务B失败

事务A回滚，事务B成功

2、测试二个事务从开始到结束有重叠

事务B失败

3、将上面的“测试二”重做一次，不过这次增加一个小小的变化

Oracle没有这样的问题

4、测试SERIALIZABLE级别的脏读、非重复读、幻像是否会产生

第五步不会看到 (4,40)

第七步不会看到 (4,40)

5、测试SERIALIZABLE、幻像会产生的问题

同样结果

Reference:

http://www.postgresql.org/docs/current/static/transaction-iso.html