postgres 事务隔离级别

事务隔离

MVCC的实现方法有两种：
1.写新数据时，把旧数据移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来；
2.写数据时，旧数据不删除，而是把新数据插入。
PostgreSQL数据库使用第二种方法，而Oracle数据库和MySQL中的innodb引擎使用的是第一种方法。
与racle数据库和MySQL中的innodb引擎相比较，PostgreSQL的MVCC实现方式的优缺点如下。
优点：
1.事务回滚可以立即完成，无论事务进行了多少操作；
2.数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰；
缺点：
1.旧版本数据需要清理。PostgreSQL清理旧版本的命令成为Vacuum；
2.旧版本的数据会导致查询更慢一些，因为旧版本的数据存在于数据文件中，查询时需要扫描更多的数据块。
(本段转自《PostgreSQL修炼之道》)

各个级别不希望发生的现象是

  • 脏读（dirty reads）
    一个事务读取了另一个未提交的并行事务写的数据。
时间 事务A 事务B
T1 开始事务
T2 开始事务
T3 查询账户余额1000
T4 去除500元，余额500
T5 查询余额为500(脏读)
  • 不可重复读（non-repeatable reads）
    一个事务重新读取前面读取过的数据， 发现该数据已经被另一个已提交的事务修改过。
时间 事务A 事务B
T1 开始事务
T2 查询余额为1000 开始事务
T3 查询账户余额1000
T4 去除500元，余额500
T5 提交事务
T6 查询余额为500
T7 提交事务
  • 幻读（phantom read）
    当前事务中重复执行相同的查询,返回的记录数因另一个事物插入或删除而得到不同的结果
时间 事务A 事务B
T1 开始事务
T2 select count(*) from Foos where flag1=1 //(10条) 开始事务
T3 update Foos set flag2=2 where flag1=1 //(10条)
T4 insert into Foos (..,flag1,...) values (.., 1 ,..)
T5 提交事务
T6 select count(*) from Foos where flag1=1 //(11条)
T7 update Foos set flag2=2 where flag1=1 //(更新11条)
T8 提交事务

会看到新插入的那条数据会被更新

标准SQL事务隔离级别

隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 Allowed, but not in PG
可串行化 不可能 不可能 不可能

事务隔离级别

在PostgreSQL里，你可以请求四种可能的事务隔离级别中的任意一种。
但是在内部， 实际上只有三种独立的隔离级别，分别对应读已提交，可重复读和可串行化。
如果你选择了读未提交的级别， 实际上你用的是读已提交，
在Postgre的重复读下，幻读是不可能的， 所以实际的隔离级别可能比你选择的更严格。

  • 读未提交 Read Uncommitted
    另一个事务中只要更新的记录(不需要等到提交)， 当前事务就会读取到更新的数据 (脏读)

  • 读已提交 Read Committed
    读已提交是PostgreSQL里的缺省隔离级别。
    当一个事务运行在这个隔离级别时， SELECT查询(没有FOR UPDATE/SHARE子句)只能看到其它事务已提交的数据。
    实际上，SELECT 查询看到一个在查询开始运行的瞬间该数据库的一个快照。 不过，SELECT看得见其自身所在事务中之前的更新的执行结果，即使它们尚未提交。
    请注意， 在同一个事务里两个相邻的SELECT命令可能看到不同的快照，因为其它事务会坑你在两个SELECT执行期间提交。
    不会出现可脏读，但是不可重复读

  • 可重复读 Repeatable Read
    即使数据被其他事物修改， 当前事务也不会读取到新的数据
    重复读事务中的查询看到的是事务开始时的快照， 而不是该事务内部当前查询开始时的快照，这样， 同一个事务内部后面的SELECT命令总是看到同样的数据，
    也就是说，它们看不到 它们自身事务开始之后提交的其他事务所做出的改变。
    不会出现可脏读, 可重复读, 可以幻读

  • 可串行化 Serializable
    可串行化级别提供最严格的事务隔离。这个级别为所有已提交事务模拟串行的事务执行， 就好像事务将被一个接着一个那样串行(而不是并行)的执行。
    不过，正如可重复读隔离级别一样， 使用这个级别的应用必须准备在串行化失败的时候重新启动事务。
    事实上，该隔离级别和可重复读希望的完全一样， 它只是监视这些条件，以所有事务的可能的序列不一致的（一次一个）的方式执行并行的可串行化事务执行的行为。
    这种监测不引入任何阻止可重复读出现的行为，但有一些开销的监测，检测条件这可能会导致串行化异常 将触发串行化失败。

读已提交(Read Committed Isolation Level)

不可重复读

ActiveRecord::Base.isolation_level(:read_committed) do
   Foo.transaction do
        print Foo.first.bar # 1
        sleep(10) # 在此期间, 其它事务更新了Foo#bar
        print Foo.first.bar # 2
    end
end

可重复读(Repeatable Read Isolation Level)

可重复读

ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
        print Foo.first.bar # 1
        sleep(10) # 在此期间, 其它事务更新了Foo#bar
        print Foo.first.bar # 1
    end
end

该级别的应用必须准备好重试事务,因为可能会发生串行化失败。
下面这种情况事务T2会发生串行化失败

# 事务T1
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
      print Foo.where(id: 1).update_all(bar: 11)
      print Foo.find(1).bar
      sleep 5
    end
end

# 事务T2
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
      print Foo.where(id: 1).update_all(bar: 12)
      print Foo.find(1).bar
    end
end

下面这种情况事务T2不会发生串行化失败

# 事务T1
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
      print Foo.where(id: 1).update_all(bar: 11)
      print Foo.find(1).bar
      sleep 5
    end
end

# 事务T2
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
      sleep 6
      print Foo.where(id: 1).update_all(bar: 12)
      print Foo.find(1).bar
    end
end

在Postgre的重复读下，幻读是不可能的

但是测试的时候发现这种状况

# 事务T1
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
       Foo.create!(bar: 2)
       sleep 5
    end
end

# 事务T2
ActiveRecord::Base.isolation_level(:repeatable_read) do
   Foo.transaction do
      print Foo.where(bar: 2).count # 1
      sleep 10
      print Foo.where(bar: 2).count # 2
      Foo.where(bar: 2).update_all(bar: 1) # 2
    end
end

可串行化(Serializable Isolation Level)

可重复读下不会发生的串行化失败在可串行化会失败
下面这种情况事务T2会发生串行化失败

# 事务T1
ActiveRecord::Base.isolation_level(:serializable) do
   Foo.transaction do
      print Foo.where(id: 1).update_all(bar: 11)
      print Foo.find(1).bar
      sleep 5
    end
end

# 事务T2
ActiveRecord::Base.isolation_level(:serializable) do
   Foo.transaction do
      sleep 6
      print Foo.where(id: 1).update_all(bar: 12)
      print Foo.find(1).bar
    end
end

多个事务并发时可能遇到的问题

  • Lost Update 更新丢失

    • 第一类更新丢失，回滚覆盖：撤消一个事务时，在该事务内的写操作要回滚，把其它已提交的事务写入的数据覆盖了。
    • 第二类更新丢失，提交覆盖：提交一个事务时，写操作依赖于事务内读到的数据，读发生在其他事务提交前，写发生在其他事务提交后，把其他已提交的事务写入的数据覆盖了。这是不可重复读的特例。

  • Non-Repeatable Read 不可重复读：一个事务中两次读同一行数据，可是这两次读到的数据不一样。

  • Phantom Read 幻读：一个事务中两次查询，但第二次查询比第一次查询多了或少了几行或几列数据。

回滚覆盖

时间 事务A 事务B
T1 开始事务
T2 开始事务
T3 查询余额为1000
T4 取出100，余额改为900
T5 读余额为1000
T6 汇入100，余额改为1100
T7 提交事务，余额定为1100
T8 撤销事务，余额改回1000
T9 最终余额1000，更新丢失

这种更新丢失在pg的隔离级别下是不会发生的

提交覆盖

时间 事务A 事务B
T1 开始事务
T2 开始事务
T3 查询余额为1000
T4 读余额为10000
T5 取出100，余额改为900
T6 提交事务，余额定为900
T7 汇入100，余额改为1100
T8 提交事务，余额定为1100
T8 最终余额1100，更新丢失

不做并发控制的前提下， 读已提交隔离级别下很容易发生更新丢失的问题,
可重复读, 可串行化 可以避免更新丢失的问题
比如下面这段代码

# 事务T1
Foo.transaction do
  Foo = Foo.find(1)
  Foo.bar = Foo.bar + 10
  sleep 5
  Foo.save
end

# 事务T2
Foo.transaction do
  Foo = Foo.find(1)
  Foo.bar = Foo.bar + 5
  Foo.save
end

在读已提交隔离级别下可以通过锁来防止更新丢失

  • 拿掉代码中的临时变量
# 事务T1
Foo.transaction do
  Foo = Foo.find(1)
  Foo.increment!(:bar, 10)
  sleep 5
  Foo.increment!(:bar, 10)
end

# 事务T2
Foo.transaction do
  Foo = Foo.find(1)
  Foo.increment!(:bar, 10)
end
  • 锁
# 事务T1
Foo = Foo.find(1)
Foo.with_lock do
    Foo.bar = Foo.bar + 10
    Foo.save(validate: false)
    sleep 10
    Foo.bar = Foo.bar + 10
    Foo.save(validate: false)
end 

# 事务T1
Foo = Foo.find(1)
Foo.with_lock do
  Foo.bar = Foo.bar + 10
  Foo.save(validate: false)
end

tip

  • combine query
# bad
unless rerord.approved?
    # balabala # 多个thread可能同时到达这里
    rerord.update(approved: true)
end # 并发下会导致一些问题

# better
update_count = Rerord.where(id: id, approved: false).update_all(approved: true)
# 根据上面的理论 并发下 不会导致某个record会被重复更新
if update_count == 1
    # balabala
end

参考: https://www.postgresql.org/docs/9.5/static/transaction-iso.html

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 157,198评论 4赞 359
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 66,663评论 1赞 290
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 106,985评论 0赞 237
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,673评论 0赞 202
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 51,994评论 3赞 285
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,399评论 1赞 211
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,717评论 2赞 310
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,407评论 0赞 194
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,112评论 1赞 239
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,371评论 2赞 241
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,891评论 1赞 256
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,255评论 2赞 250
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 32,881评论 3赞 233
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,010评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,764评论 0赞 192
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,412评论 2赞 269
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,299评论 2赞 260