↓ 跳过正文
老冯云数
Background Image
  1. PostgreSQL大法师/

事务隔离等级注意事项

·3083 字·7 分钟· ·
PostgreSQL PG开发
冯若航
作者
冯若航
Pigsty 创始人, @Vonng
目录

PostgreSQL实际上只有两种事务隔离等级：读已提交（Read Commited）与可序列化（Serializable）

基础
#

SQL标准定义了四种隔离级别，但PostgreSQL实际上只有两种事务隔离等级：读已提交（Read Commited）与可序列化（Serializable）

SQL标准定义了四种隔离级别，但实际上这也是很粗鄙的一种划分。详情请参考并发异常那些事。

查看/设置事务隔离等级
#

通过执行：SELECT current_setting('transaction_isolation'); 可以查看当前事务隔离等级。

通过在事务块顶部执行 SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } 来设定事务的隔离等级。

或者为当前会话生命周期设置事务隔离等级：

SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode

Actual isolation levelP4G-singleG2-itemG2
RC（monotonic atomic views）----
RR（snapshot isolation）✓✓--
Serializable✓✓✓✓

隔离等级与并发问题
#

创建测试表 t ，并插入两行测试数据。

CREATE TABLE t (k INTEGER PRIMARY KEY, v int);
TRUNCATE t; INSERT INTO t VALUES (1,10), (2,20);

更新丢失（P4）
#

PostgreSQL的 读已提交RC 隔离等级无法阻止丢失更新的问题，但可重复读隔离等级则可以。

丢失更新，顾名思义，就是一个事务的写入覆盖了另一个事务的写入结果。

在读已提交隔离等级下，无法阻止丢失更新的问题，考虑一个计数器并发更新的例子，两个事务同时从计数器中读取出值，加1后写回原表。

T1T2Comment
begin;
begin;
SELECT v FROM t WHERE k = 1T1读
SELECT v FROM t WHERE k = 1T2读
update t set v = 11 where k = 1;T1写
update t set v = 11 where k = 1;T2因T1阻塞
COMMITT2恢复，写入
COMMITT2写入覆盖T1

解决这个问题有两种方式，使用原子操作，或者在可重复读的隔离等级执行事务。

使用原子操作的方式为：

T1T2Comment
begin;
begin;
update t set v = v+1 where k = 1;T1写
update t set v = v + 1 where k = 1;T2因T1阻塞
COMMITT2恢复，写入
COMMITT2写入覆盖T1

解决这个问题有两种方式，使用原子操作，或者在可重复读的隔离等级执行事务。

在可重复读的隔离等级

读已提交（RC）
#

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2

update t set v = 11 where k = 1; -- T1
update t set v = 12 where k = 1; -- T2, BLOCKS
update t set v = 21 where k = 2; -- T1

commit; -- T1. This unblocks T2
select * from t; -- T1. Shows 1 => 11, 2 => 21
update t set v = 22 where k = 2; -- T2


commit; -- T2
select * from test; -- either. Shows 1 => 12, 2 => 22
T1T2Comment
begin; set transaction isolation level read committed;
begin; set transaction isolation level read committed;
update t set v = 11 where k = 1;
update t set v = 12 where k = 1;T2会等待T1持有的锁
SELECT * FROM t2:20, 1:11
update pair set v = 21 where k = 2;
commit;T2解锁
select * from pair;T2看见T1的结果和自己的修改
update t set v = 22 where k = 2
commit

提交后的结果

1

 relname | locktype | virtualtransaction |  pid  |       mode       | granted | fastpath
---------+----------+--------------------+-------+------------------+---------+----------
 t_pkey  | relation | 4/578              | 37670 | RowExclusiveLock | t       | t
 t       | relation | 4/578              | 37670 | RowExclusiveLock | t       | t

 relname | locktype | virtualtransaction |  pid  |       mode       | granted | fastpath
---------+----------+--------------------+-------+------------------+---------+----------
 t_pkey  | relation | 4/578              | 37670 | RowExclusiveLock | t       | t
 t       | relation | 4/578              | 37670 | RowExclusiveLock | t       | t
 t_pkey  | relation | 6/494              | 37672 | RowExclusiveLock | t       | t
 t       | relation | 6/494              | 37672 | RowExclusiveLock | t       | t
 t       | tuple    | 6/494              | 37672 | ExclusiveLock    | t       | f

 relname | locktype | virtualtransaction |  pid  |       mode       | granted | fastpath
---------+----------+--------------------+-------+------------------+---------+----------
 t_pkey  | relation | 4/578              | 37670 | RowExclusiveLock | t       | t
 t       | relation | 4/578              | 37670 | RowExclusiveLock | t       | t
 t_pkey  | relation | 6/494              | 37672 | RowExclusiveLock | t       | t
 t       | relation | 6/494              | 37672 | RowExclusiveLock | t       | t
 t       | tuple    | 6/494              | 37672 | ExclusiveLock    | t       | f

Testing PostgreSQL transaction isolation levels
#

These tests were run with Postgres 9.3.5.

Setup (before every test case):

create table test (id int primary key, value int);
insert into test (id, value) values (1, 10), (2, 20);

To see the current isolation level:

select current_setting('transaction_isolation');

Read Committed basic requirements (G0, G1a, G1b, G1c)
#

Postgres “read committed” prevents Write Cycles (G0) by locking updated rows:

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 12 where id = 1; -- T2, BLOCKS
update test set value = 21 where id = 2; -- T1
commit; -- T1. This unblocks T2
select * from test; -- T1. Shows 1 => 11, 2 => 21
update test set value = 22 where id = 2; -- T2
commit; -- T2
select * from test; -- either. Shows 1 => 12, 2 => 22

Postgres “read committed” prevents Aborted Reads (G1a):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value = 101 where id = 1; -- T1
select * from test; -- T2. Still shows 1 => 10
abort;  -- T1
select * from test; -- T2. Still shows 1 => 10
commit; -- T2

Postgres “read committed” prevents Intermediate Reads (G1b):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value = 101 where id = 1; -- T1
select * from test; -- T2. Still shows 1 => 10
update test set value = 11 where id = 1; -- T1
commit; -- T1
select * from test; -- T2. Now shows 1 => 11
commit; -- T2

Postgres “read committed” prevents Circular Information Flow (G1c):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 22 where id = 2; -- T2
select * from test where id = 2; -- T1. Still shows 2 => 20
select * from test where id = 1; -- T2. Still shows 1 => 10
commit; -- T1
commit; -- T2

Observed Transaction Vanishes (OTV)
#

Postgres “read committed” prevents Observed Transaction Vanishes (OTV):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
begin; set transaction isolation level read committed; -- T3
update test set value = 11 where id = 1; -- T1
update test set value = 19 where id = 2; -- T1
update test set value = 12 where id = 1; -- T2. BLOCKS
commit; -- T1. This unblocks T2
select * from test where id = 1; -- T3. Shows 1 => 11
update test set value = 18 where id = 2; -- T2
select * from test where id = 2; -- T3. Shows 2 => 19
commit; -- T2
select * from test where id = 2; -- T3. Shows 2 => 18
select * from test where id = 1; -- T3. Shows 1 => 12
commit; -- T3

Predicate-Many-Preceders (PMP)
#

Postgres “read committed” does not prevent Predicate-Many-Preceders (PMP):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from test where value = 30; -- T1. Returns nothing
insert into test (id, value) values(3, 30); -- T2
commit; -- T2
select * from test where value % 3 = 0; -- T1. Returns the newly inserted row
commit; -- T1

Postgres “repeatable read” prevents Predicate-Many-Preceders (PMP):

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where value = 30; -- T1. Returns nothing
insert into test (id, value) values(3, 30); -- T2
commit; -- T2
select * from test where value % 3 = 0; -- T1. Still returns nothing
commit; -- T1

Postgres “read committed” does not prevent Predicate-Many-Preceders (PMP) for write predicates – example from Postgres documentation:

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
update test set value = value + 10; -- T1
delete from test where value = 20;  -- T2, BLOCKS
commit; -- T1. This unblocks T2
select * from test where value = 20; -- T2, returns 1 => 20 (despite ostensibly having been deleted)
commit; -- T2

Postgres “repeatable read” prevents Predicate-Many-Preceders (PMP) for write predicates – example from Postgres documentation:

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
update test set value = value + 10; -- T1
delete from test where value = 20;  -- T2, BLOCKS
commit; -- T1. T2 now prints out "ERROR: could not serialize access due to concurrent update"
abort;  -- T2. There's nothing else we can do, this transaction has failed

Lost Update (P4)
#

Postgres “read committed” does not prevent Lost Update (P4):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, BLOCKS
commit; -- T1. This unblocks T2, so T1's update is overwritten
commit; -- T2

Postgres “repeatable read” prevents Lost Update (P4):

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, BLOCKS
commit; -- T1. T2 now prints out "ERROR: could not serialize access due to concurrent update"
abort;  -- T2. There's nothing else we can do, this transaction has failed

Read Skew (G-single)
#

Postgres “read committed” does not prevent Read Skew (G-single):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from test where id = 1; -- T1. Shows 1 => 10
select * from test where id = 1; -- T2
select * from test where id = 2; -- T2
update test set value = 12 where id = 1; -- T2
update test set value = 18 where id = 2; -- T2
commit; -- T2
select * from test where id = 2; -- T1. Shows 2 => 18
commit; -- T1

Postgres “repeatable read” prevents Read Skew (G-single):

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where id = 1; -- T1. Shows 1 => 10
select * from test where id = 1; -- T2
select * from test where id = 2; -- T2
update test set value = 12 where id = 1; -- T2
update test set value = 18 where id = 2; -- T2
commit; -- T2
select * from test where id = 2; -- T1. Shows 2 => 20
commit; -- T1

Postgres “repeatable read” prevents Read Skew (G-single) – test using predicate dependencies:

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where value % 5 = 0; -- T1
update test set value = 12 where value = 10; -- T2
commit; -- T2
select * from test where value % 3 = 0; -- T1. Returns nothing
commit; -- T1

Postgres “repeatable read” prevents Read Skew (G-single) – test using write predicate:

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where id = 1; -- T1. Shows 1 => 10
select * from test; -- T2
update test set value = 12 where id = 1; -- T2
update test set value = 18 where id = 2; -- T2
commit; -- T2
delete from test where value = 20; -- T1. Prints "ERROR: could not serialize access due to concurrent update"
abort; -- T1. There's nothing else we can do, this transaction has failed

Write Skew (G2-item)
#

Postgres “repeatable read” does not prevent Write Skew (G2-item):

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where id in (1,2); -- T1
select * from test where id in (1,2); -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 21 where id = 2; -- T2
commit; -- T1
commit; -- T2

Postgres “serializable” prevents Write Skew (G2-item):

begin; set transaction isolation level serializable; -- T1
begin; set transaction isolation level serializable; -- T2
select * from test where id in (1,2); -- T1
select * from test where id in (1,2); -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 21 where id = 2; -- T2
commit; -- T1
commit; -- T2. Prints out "ERROR: could not serialize access due to read/write dependencies among transactions"

Anti-Dependency Cycles (G2)
#

Postgres “repeatable read” does not prevent Anti-Dependency Cycles (G2):

begin; set transaction isolation level repeatable read; -- T1
begin; set transaction isolation level repeatable read; -- T2
select * from test where value % 3 = 0; -- T1
select * from test where value % 3 = 0; -- T2
insert into test (id, value) values(3, 30); -- T1
insert into test (id, value) values(4, 42); -- T2
commit; -- T1
commit; -- T2
select * from test where value % 3 = 0; -- Either. Returns 3 => 30, 4 => 42

Postgres “serializable” prevents Anti-Dependency Cycles (G2):

begin; set transaction isolation level serializable; -- T1
begin; set transaction isolation level serializable; -- T2
select * from test where value % 3 = 0; -- T1
select * from test where value % 3 = 0; -- T2
insert into test (id, value) values(3, 30); -- T1
insert into test (id, value) values(4, 42); -- T2
commit; -- T1
commit; -- T2. Prints out "ERROR: could not serialize access due to read/write dependencies among transactions"

Postgres “serializable” prevents Anti-Dependency Cycles (G2) – Fekete et al’s example with two anti-dependency edges:

begin; set transaction isolation level serializable; -- T1
select * from test; -- T1. Shows 1 => 10, 2 => 20
begin; set transaction isolation level serializable; -- T2
update test set value = value + 5 where id = 2; -- T2
commit; -- T2
begin; set transaction isolation level serializable; -- T3
select * from test; -- T3. Shows 1 => 10, 2 => 25
commit; -- T3
update test set value = 0 where id = 1; -- T1. Prints out "ERROR: could not serialize access due to read/write dependencies among transactions"
abort; -- T1. There's nothing else we can do, this transaction has failed

相关文章

前后端通信线缆协议
·1234 字·3 分钟
PostgreSQL PG开发 PG内核
了解PostgreSQL服务器与客户端通信使用的TCP协议，并使用Go语言打印消息
CDC 变更数据捕获机理
·9552 字·20 分钟
PostgreSQL PG开发 CDC
数据变更捕获是一种很有趣的ETL替代方案。
PostgreSQL中的锁
·7397 字·15 分钟
PostgreSQL PG开发 锁
详细介绍PostgreSQL中的各种锁
GIN搜索的O(n2)负载度
·710 字·2 分钟
PostgreSQL PG开发 GIN
GIN索引如果使用很长的关键词列表进行搜索，会导致性能显著下降。本文解释了为什么GIN索引关键词搜索的时间复杂度为O(n^2)
GeoIP 地理逆查询优化
·3025 字·7 分钟
PostgreSQL PG开发 扩展 GIS
在应用开发中，一个‘很常见’的需求就是GeoIP转换。将请求的来源IP转换为相应的地理坐标，或者行政区划（国家-省-市-县-乡-镇）
PostgreSQL的触发器使用注意事项
·2276 字·5 分钟
PostgreSQL PG开发 触发器
详细了解PostgreSQL中触发器的管理与使用
← → 故障档案：PG安装Extension导致无法连接 前后端通信线缆协议 → ←