免费注册 查看新帖 |

ChinaUnix.net

  平台 论坛 博客 文库
12下一页
最近访问板块 发新帖
查看: 8468 | 回复: 10

PostgreSQL与MySQL事务隔离级别的不同 [复制链接]

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-18 22:20:00数据库技术版块每日发帖之星
日期:2015-06-21 22:20:00数据库技术版块每日发帖之星
日期:2015-08-27 06:20:00
发表于 2013-03-15 21:17 |显示全部楼层
数据库的事务隔离级别有4种:

    READ UNCOMMITTED
    READ COMMITTED
    REPEATABLE READ
    SERIALIZABLE

而在PostgreSQL事务隔离级别中,READ UNCOMMITTED是之间被认为是READ COMMITTED,所以实际上只有三种。
PostgreSQL默认隔离级别是READ COMMTIED,而MySQL默认为REPEATABLE READ,另MySQL是有READ UNCOMMITTED这种级别的,不过一般这种事务隔离级别基本上没有什么用。

MySQL 在REPEATABLE READ隔离级别下,普通的select语句是看不到在事务启动之后已经提交的数据,但select for update却能看到,也就是说普通select与select for update看到的结果是不一样的,这是特别需要让开发人员注意的地方。
而PostgreSQL在REPEATABLE READ隔离级别,select for udpate语句和select语句的结果是一样的,都看不到在事务启动之后已经提交的数据。


另MySQL有一个很奇怪的间隙锁,如果表中的数据为(1,2,3,10,11),如果执行select * from t1 where id=6 for update;加锁后,其它的用户将不能插入(4,5,6,7,8,9)的主键值了;如果select for update锁住一个主键值比表中现有主键值还大的一个值时,则此表就不能插入比表中主键值更大的键值了。

下面演示一下:
在PostgreSQL中建测试表和数据:
osdba=# create table t1(id int primary key, info text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
osdba=# insert into t1 values(1,'11111');
INSERT 0 1
osdba=# insert into t1 values(2,'22222');
INSERT 0 1
osdba=# insert into t1 values(3,'33333');
INSERT 0 1
osdba=# insert into t1 values(10,'101010');
INSERT 0 1
osdba=# insert into t1 values(11,'111111');

osdba=# select * from t1;
id |  info
----+--------
  1 | 11111
  2 | 22222
  3 | 33333
10 | 101010
11 | 111111
(5 rows)

在MySQL中建测试数据:
mysql> create table t1(id int primary key, info varchar(255));
Query OK, 0 rows affected (0.13 sec)


mysql> insert into t1 values(1,'11111');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1 values(2,'22222');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values(3,'33333');
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(10,'101010');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+----+--------+
| id | info   |
+----+--------+
|  1 | 11111  |
|  2 | 22222  |
|  3 | 33333  |
| 10 | 101010 |
| 11 | 111111 |
+----+--------+
5 rows in set (0.00 sec)


先测试PostgreSQL:
在窗口1中,在“REPEATABLE READ”启动一个事务:

osdba=# begin isolation level REPEATABLE READ ;
BEGIN

在窗口2中,往表插入一条记录:
osdba=# insert into t1 values(5,'555555');
INSERT 0 1

然后再回到窗口1中:
osdba=# select * from t1 where id=5;
id | info
----+------
(0 rows)

osdba=# select * from t1 where id=5 for update;
id | info
----+------
(0 rows)

可以看到在PostgreSQL中不管是select语句不管加不加for update,看到的结果都是一样的,都看不到id=5记录。


而MySQL是不一样的,MySQL的普通select 语句是看不到id=5的记录,但select for update却能看到,见下面:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=5;
Empty set (0.00 sec)

mysql> select * from t1 where id=5 for update;
+----+-------+
| id | info  |
+----+-------+
|  5 | 55555 |
+----+-------+
1 row in set (0.00 sec)

mysql>

可以看到MySQL在REPEATABLE READ隔的级别下,看到的select for update与普通的select 看到的结果是不一样的。


在MySQL中,一旦设置了SERIALIZABLE级别后,在事务中一旦查询表,就会把查询的记录都锁住,而PostgreSQL中的SERIALIZABLE也与MySQL中完全不一样。查询不会锁表。
在PostgreSQL中,在窗口1中先开始一个事务,然后在窗口2中再开始一个事务,窗口2中修改id=3的记录,再到窗口1中修改id=3的记录时,会报错,由此实现了SERIALIZABLE的隔离级别。
由此可见在SERIALIZABLE的级别上,PostGreSQL的并发会比MySQL好很多。

论坛徽章:
0
发表于 2013-03-19 15:36 |显示全部楼层
pg是真正做到了SERIALIZABLE的级别,可惜这个级别使用的场合太少了

论坛徽章:
1
寅虎
日期:2013-09-29 23:15:15
发表于 2013-05-12 11:44 |显示全部楼层
回复 2# pix77


    请教, 那这个应该在什么场合上使用?

论坛徽章:
0
发表于 2013-05-13 09:50 |显示全部楼层
回复 3# congli
以前在看pg文档时,确实有:
http://www.postgresql.org/commun ... e-transaction-mode/
具体还是看应用,不过,我不用

   

论坛徽章:
1
寅虎
日期:2013-09-29 23:15:15
发表于 2013-05-13 14:12 |显示全部楼层
回复 4# pix77


   在自己开发的系统上,用SERIALIZABLE,感觉逻辑要简单点.

论坛徽章:
0
发表于 2013-05-13 15:49 |显示全部楼层
回复 5# congli


    呵呵,都是一条线了,排队来

论坛徽章:
1
寅虎
日期:2013-09-29 23:15:15
发表于 2013-05-14 10:33 |显示全部楼层
回复 6# pix77


   看资料,PG内部也只有两种级别读已提交(Read committed), 可串行化(Serializable),
小系统还是用Serializable来得简单.

论坛徽章:
0
发表于 2013-05-14 11:07 |显示全部楼层
回复 7# congli


    呵呵,电商要是用可串行化,要崩溃了,
    要专门的应用需要才有用

论坛徽章:
1
寅虎
日期:2013-09-29 23:15:15
发表于 2013-05-14 11:14 |显示全部楼层
回复 8# pix77


    呵~所以说是小系统嘛.
毕竟读已提交(Read committed),在一个事务里面需要考虑到其他已提交的事务, 情况就复杂多了.

论坛徽章:
0
发表于 2013-05-14 11:27 |显示全部楼层
回复 9# congli


    嗯,总是有用武之地
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

基于案例的 SQL 优化实战训练营

讲师:中电福富特级专家梁敬彬,参与本次课程培训,你将收获:
1. 能编写出较为高效的 SQL;
2. 能解决70%以上的数据库常见优化问题;
3. 能得到老师提供的高效的相关工具和解决方案;
4. 能举一反三,收获不仅仅是 SQL 优化。
现在购票享受8.8折优惠!
----------------------------------------
优惠时间:2019年3月20日前

大会官网>>
  

北京盛拓优讯信息技术有限公司. 版权所有 16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122
中国互联网协会会员  联系我们:huangweiwei@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP