本文探讨Postgres中可重复读隔离级别的特性及其实现方式，对比MySQL的处理差异，并通过具体实例展示如何配置及测试。
目录
修改配置文件postgresql.conf，设置默认的隔离级别：
四. 在事务A中， 修改同一行，造成可重复读的状态变化，查看postgres的处理
一. postgres对于可重复读的隔离级别，如果出现读取行的状态变化，则本事务将会提交失败并回滚
二. mysql的可重复读， 如果出现读取行的状态变化，则本事务将会覆盖先前的事务
摘要:
可重复读的隔离级别控制，在postgres和mysql中有不同的处理。
而可重复读，与读已提交，在隔离级别上， 又必须要具体的明白到底有哪些区别。
本文主要记录postgres的可重复读的处理。
postgres的可重复读说明:
可重复读隔离级别只看到在事务开始之前被提交的数据；它从来看不到未提交的数据或者并
行事务在本事务执行期间提交的修改（不过，查询能够看见在它的事务中之前执行的更新，
即使它们还没有被提交）。这是比SQL标准对此隔离级别所要求的更强的保证，并且阻
止表 13.1中描述的除了序列化异常之外的所有现象。如上面所提到的，这是标准特别允许
的，标准只描述了每种隔离级别必须提供的最小保护。
这个级别与读已提交不同之处在于，一个可重复读事务中的查询可以看见在事务中第一个非
事务控制语句开始时的一个快照，而不是事务中当前语句开始时的快照。因此，在一个单
一事务中的后续SELECT命令看到的是相同的数据，即它们看不到其他事务在本事务启动后提
交的修改。
使用这个级别的应用必须准备好由于序列化失败而重试事务。
UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命
令在搜索目标行时的行为和SELECT一样： 它们将只找到在事务开始时已经被提交的
行。 不过，在被找到时，这样的目标行可能已经被其它并发事务更新（或删除或锁住）。
在这种情况下， 可重复读事务将等待第一个更新事务提交或者回滚（如果它还在进行
中）。 如果第一个更新事务回滚，那么它的作用将被忽略并且可重复读事务可以继续更新
最初发现的行。 但是如果第一个更新事务提交（并且实际更新或删除该行，而不是只锁住
它），则可重复读事务将回滚并带有如下消息
ERROR: could not serialize access due to concurrent update
因为一个可重复读事务无法修改或者锁住被其他在可重复读事务开始之后的事务改变的行
当一个应用接收到这个错误消息，它应该中断当前事务并且从开头重试整个事务。在第二次
执行中，该事务将见到作为其初始数据库视图一部分的之前提交的改变，这样在使用行的新
版本作为新事务更新的起点时就不会有逻辑冲突。
注意只有更新事务可能需要被重试；只读事务将永远不会有序列化冲突。
可重复读模式提供了一种严格的保证，在其中每一个事务看到数据库的一个完全稳定的视
图。不过，这个视图并不需要总是和同一级别上并发事务的某些序列化（一次一个）执行保
持一致。例如，即使这个级别上的一个只读事务可能看到一个控制记录被更新，这显示一个
批处理已经被完成但是不能看见作为该批处理的逻辑组成部分的一个细节记录，因为它读取
空值记录的一个较早的版本。如果不小心地使用显式锁来阻塞冲突事务，尝试用运行在这个
隔离级别的事务来强制业务规则不太可能正确地工作。
可重复读隔离级别是使用学术数据库文献和一些其他数据库产品中称为Snapshot
Isolation的已知的技术来实现的。 与使用传统锁技术并降低并发性的系统相比，可以观察
到行为和性能方面的差异。 一些其他系统甚至可以提供可重复读取和快照隔离作为具有不
同行为的不同隔离级别。 直到SQL标准开发出来之后，数据库研究人员才正式确定区分这两
种技术的允许现象，并且超出了本手册的范围。
postgres的可重复读的具体操作测试:
一. 开启会话级别的可重复读的隔离级别
修改配置文件postgresql.conf，设置默认的隔离级别：
vim postgresql.conf
default_transaction_isolation = ‘repeatable read’重新加载配置:
pg_ctl -D /data/pg reload
查看默认的隔离级别:
show default_transaction_isolation;postgres=# show default_transaction_isolation;
***(Single step mode: verify command)*******************************************
show default_transaction_isolation;
***(press return to proceed or enter x and return to cancel)********************
default_transaction_isolation
-------------------------------
repeatable read
(1 row)
二. 先开启事务A
务必先开启事务A， 然后在事务A开始后， 再开启事务B。
在事务B中执行完commit提交后， 再在事务A中修改同一行。
事务A操作：
test=# begin;
***(Single step mode: verify command)*******************************************
begin;
***(press return to proceed or enter x and return to cancel)********************
BEGIN
test=*# select * from company where id=11;
***(Single step mode: verify command)*******************************************
select * from company where id=11;
***(press return to proceed or enter x and return to cancel)********************
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
11 | Paul | 66 | California | 20000 | 2001-07-13
(1 row)
test=*# select * from company where id=11;
***(Single step mode: verify command)*******************************************
select * from company where id=11;
***(press return to proceed or enter x and return to cancel)********************
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
11 | Paul | 66 | California | 20000 | 2001-07-13
(1 row)
test=*# select * from company where id=11;
***(Single step mode: verify command)*******************************************
select * from company where id=11;
***(press return to proceed or enter x and return to cancel)********************
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
11 | Paul | 66 | California | 20000 | 2001-07-13
(1 row)
三. 开启事务B，修改特定行，并提交
test=# begin;
***(Single step mode: verify command)*******************************************
begin;
***(press return to proceed or enter x and return to cancel)********************
BEGIN
test=*#
test=*# select * from company where id=11;
***(Single step mode: verify command)*******************************************
select * from company where id=11;
***(press return to proceed or enter x and return to cancel)********************
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
11 | Paul | 66 | California | 20000 | 2001-07-13
(1 row)
test=*# update company set age=88 where id=11;
***(Single step mode: verify command)*******************************************
update company set age=88 where id=11;
***(press return to proceed or enter x and return to cancel)********************
UPDATE 1
test=*# select * from company where id=11;
***(Single step mode: verify command)*******************************************
select * from company where id=11;
***(press return to proceed or enter x and return to cancel)********************
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
11 | Paul | 88 | California | 20000 | 2001-07-13
(1 row)
test=*# commit;
***(Single step mode: verify command)*******************************************
commit;
***(press return to proceed or enter x and return to cancel)********************
四. 在事务A中， 修改同一行，造成可重复读的状态变化，查看postgres的处理
test=*# update company set age=99 where id=11;
***(Single step mode: verify command)*******************************************
update company set age=99 where id=11;
***(press return to proceed or enter x and return to cancel)********************
2022-05-24 12:50:53.649 EDT [443682] ERROR: could not serialize access due to concurrent update
2022-05-24 12:50:53.649 EDT [443682] STATEMENT: update company set age=99 where id=11;
ERROR: could not serialize access due to concurrent update
扫一扫
2106
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
