Postgresql事务级别为SERIALIZABLE的特性实测-prefect-ChinaUnix博客

DBA随笔 ^_^kylehuang.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

prefect

博客访问： 334668
博文数量： 81
博客积分： 3813
博客等级：中校
技术积分： 945
用户组：普通用户
注册时间： 2005-08-24 18:14

文章分类

全部博文（81）

Python（0）
思考（0）
C++（0）
Linux（27）
Perl（0）
PostgreSQL（36）

PostgreSQL入门（7）
office（2）
DBA（3）
shell（6）

Korn Shell（1）
CentOS（3）
perfect（2）
未分配的博文（2）

文章存档

2013年（1）

2012年（2）

2011年（54）

2010年（15）

2009年（9）

我的朋友

相关博文

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

分类： Mysql/postgreSQL

2011-09-20 12:40:30

一、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

Create Session A
1. postgres@T09:~/data> createdb -h /data/pgsql/data/ kyle
2. postgres@T09:~/data> psql -h /data/pgsql/data/ kyle
3. psql (9.1.0)
4. Type "help" for help.
6. kyle=# CREATE TABLE t1(id integer not null, sl float8 not null,primary key(id));
7. NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
8. CREATE TABLE
9. kyle=# insert into t1 values(1,10);
10. INSERT 0 1
11. kyle=# insert into t1 values(2,20);
12. INSERT 0 1
13. kyle=# insert into t1 values(3,30);
14. kyle=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
15. SET
-- 上面是创建一个实验表和三条实验记录，并且将SESSION A连接的事务隔离级别设置成SERIALIZABLE
Create Session B
1. postgres@T09:~> psql -h /data/pgsql/data/ kyle
2. psql (9.1.0)
3. Type "help" for help.
5. kyle=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
6. SET
-- 将SESSION B连接的事务隔离级别设置成SERIALIZABLE

三、开始测试

测试2个事务同事更新同一条记录
Session A：
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# UPDATE t1 SET sl=1 WHERE id =1;
4. UPDATE 1
5. kyle=#
Session B:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# UPDATE t1 SET sl=2 WHERE id = 1;
此时，可以看到session B的更新语句在等待SESSION A事务提交。
Session A:继续执行下面语句
1. kyle=# END ;
2. COMMIT
3. kyle=#
4. ----如果此步骤Session A rollback --------------------------------
6. kyle=# ROLLBACK ;
7. ROLLBACK
8. kyle=#
Session B：窗口内看到以下错误提示信息
1. ERROR: could not serialize access due to concurrent update
2. --错误: 由于同步更新而无法串行访问
3. ----如果Session A rollback --------------------------------
5. kyle=# UPDATE t1 SET sl=2 where id =1;
6. UPDATE 1
7. kyle=#
从上面的过程看，二个事务同时更新同一条记录在事务级别是SERIALIZABLE是符合我们的要求的
测试2个事务从开始到结束有重叠
初始状态：
1. kyle=# select * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 10
7. (3 rows)
Session A：
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# UPDATE t1 SET sl=1 WHERE id=1;
4. UPDATE 1
5. kyle=#
Session B:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=#
Session A:
1. kyle=# END ;
2. COMMIT
3. kyle=#
Session B:
1. kyle=# UPDATE t1 SET sl=2 WHERE id=1;
2. UPDATE 1
3. kyle=# END ;
4. COMMIT
5. kyle=#
从上面的过程看，怎么在SESSION B执行UPDATE t1 SET sl=2 WHERE id=1;没有提示“无法串行访问”,这个跟ORACLE怎么不一致的，他们的事务开始到结束可是有重叠哦,不要紧张，我们再看下面这个实验
重做测试2，但有些小变化
现在的数据：
1. kyle=# SELECT * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 2
7. (3 rows)
Session A:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# UPDATE t1 SET sl=1 WHERE id=1;
4. UPDATE 1
5. kyle=#
Session B:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# SELECT 1;
4. ?column?
5. ----------
6. 1
7. (1 row)
9. kyle=#
Session A:
1. kyle=# END ;
2. COMMIT
3. kyle=#
Session B:
1. kyle=# UPDATE t1 SET sl=1 WHERE id=1;
2. ERROR: could not serialize access due to concurrent update
3. kyle=#
从上面的过程看，二个事务从开始到结束有重叠时更新相同的记录时提示出错是符合SERIALIZABLE级别的，看来Postgresql有些蛋疼，发出事务后竟然还要发出一条操作语句(我测试过了，应该任意有交往的DML,DDL,DCL都行)才会产生一个MVCC的版本，这一点大家在做应用时要特别注意，建议大家在做应用时自定义一个事务开始的函数，方便以后有变化时了维护
测试SERIALIZABLE级别的脏读、非重复读、幻像是否会产生
初始数据：
1. kyle=# select * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 1
7. (3 rows)
Create Session C:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# INSERT INTO t1 VALUES(6,60);
4. INSERT 0 1
5. kyle=#
Session A:
1. kyle=# select * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 1
7. (3 rows)
9. kyle=# UPDATE t1 SET sl=10 WHERE id=1;
10. UPDATE 1
11. kyle=#
Session B:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=#
Session A:
1. kyle=# end;
2. COMMIT
3. kyle=# INSERT INTO t1 VALUES(4,40);
4. INSERT 0 1
Session B:
1. kyle=# select * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 10
7. 4 | 40
8. (4 rows)
10. kyle=#
So，我们可以看到的记录包括已经更新的记录和新增的记录,看到这样的结果是正确的，因为postgresql在事务开始后的第一条语名执行时才产生一个MVCC版本，这个版本包含所有读已提交的数据，不包括未提交的数据，即不会出现脏读
Session A:
1. kyle=# INSERT INTO t1 VALUES(5,50);
2. INSERT 0 1
3. kyle=# UPDATE t1 SET sl=3 WHERE id=3;
4. UPDATE 1
5. kyle=# DELETE FROM t1 WHERE id =2;
6. DELETE 1
7. kyle=#
Session B:
1. kyle=# select * from t1;
2. id | sl
3. ----+----
4. 2 | 20
5. 3 | 30
6. 1 | 10
7. 4 | 40
8. (4 rows)
10. kyle=#
没错，这时SESSION C新增的记录,SESSION A删除的记录，修改的记录都没有在SESSION B中都没反映出来，即不会出现非重
测试SERIALIZABLE、幻像会产生的问题
Session A:
1. kyle=# CREATE TABLE t2(zs float8);
2. CREATE TABLE
3. kyle=# SELECT * from t1;
4. id | sl
5. ----+----
6. 6 | 60
7. 1 | 10
8. 4 | 40
9. 5 | 50
10. 3 | 3
11. (5 rows)
Session B:
1. kyle=# BEGIN ;
2. BEGIN
3. kyle=# SELECT 1;
4. ?column?
5. ----------
6. 1
7. (1 row)
Session A:
1. kyle=# INSERT INTO t1 VALUES(2,2);
2. INSERT 0 1
3. kyle=#
Session B:
1. kyle=# INSERT INTO t2 SELECT SUM(sl) FROM t1 WHERE sl<10;
2. INSERT 0 1
3. kyle=# END ;
4. COMMIT
5. kyle=# SELECT * FROM t2;
6. zs
7. ----
8. 3
9. (1 row)
明显上面的结果不是我们所需要的，为了保证不幻像，但结果却是不正确的，这里最好效果应该是报错，提示无法串行访问，然后退出，不过这样系统的开销可能很可观