postgresql 并发访问_PostgreSQL 并发控制机制（1）：隔离级别

最新推荐文章于 2023-12-01 10:16:35 发布
最新推荐文章于 2023-12-01 10:16:35 发布
阅读量491 收藏 1
点赞数
文章标签： postgresql 并发访问
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39618176/article/details/112808119
版权

并发控制是多个事务在并发运行时，数据库保证事务一致性(Consistency)和隔离性(Isolation)的一种机制。主流商用关系数据库使用的并发控制技术主要有两种：严格两阶段封锁(S2PL)和多版本并发控制(MVCC)。

PostgreSQL使用了多版本并发控制技术的一种变体：快照隔离Sanpshot Isolation(简称SI)。通过SI，PostgreSQL提供了与ANSI SQL标准相应的四个隔离级别，但实现与标准定义有一些差异。

### 一、基础知识

本节介绍用于抽象表达的形式化语言和标准隔离级别。

#### 1、形式化语言

为了更好的描述，使用以下更一般化(抽象)的形式化语言来描述数据库事务操作。

**读写操作**

w和r，示例：w1(x),r2(y)

其中w和r分别表示写和读，1和2分别表示事务T1和T2，x和y表示数据项。

注意：这里的w(写)泛指对数据变更的操作，包括但不限于插入、删除等；数据项是一个宽泛的概念，包括但不限于元组tuple、页page甚至是整个关系relation。

**事务操作**

c和a，示例：c1,a2

c表示commit提交，其后的数字指代事务，如c1表示事务T1提交

a表示abort回滚，a2表示事务T2回滚

**谓词**

谓词P，通常与读写操作一起出现，示例：r1[P],w2[P]

r1[P]表示事务T1读满足谓词P的结果集，

w2[P]表示事务T2对满足谓词P的结果集进行变更(写)。

#### 2、ANSI SQL标准隔离级别

ANSI SQL标准通过现象(Phenomena)定义(除此之外还可以通过锁、数据流图定义)，下面首先使用形式化语言来定义以下几种现象：

*脏写*P0 – (Dirty Write)

w1(x) w2(x) (c1 or a1)

*脏读*P1 – Dirty Read

w1(x) r2(x) (c1 or a1)

*不可重复读*P2 – Fuzzy or Non-Repeatable Read

r1(x) w2(x) (c1 or a1)

*幻读*P3 – Phantom

r1(P)w2(y in P) (c1 or a1)

这里的P表示逻辑术语谓词(Predicate)

ANSI SQL标准通过禁止上述现象的形式定义了四个隔离级别，分别是读未提交、读已提交、可重复读和可串行化：

|隔离级别|P0 Dirty Write|P1 Dirty Read|P2 Fuzzy Read|P3 Phantom|

| ---- | ---- | ----- | ---- | ---- |

|Read Uncommitted |Not Possible|Possible|Possible|Possible|

|Read Committed |Not Possible|Not Possible|Possible|Possible|

|Repeatable Read |Not Possible|Not Possible|Not Possible|Possible|

|Serializable|Not Possible|Not Possible|Not Possible|Not Possible|

可串行化：禁止所有并发现象，效果如同事务以串行的方式执行

可重复读：除幻读外，禁止其他现象

读已提交：禁止脏读和脏写

读未提交：禁止脏写

### 二、PostgreSQL隔离级别

PostgreSQL与ANSI SQL标准相应，也定义了四种隔离级别

```

testdb=# \help set transaction

Command: SET TRANSACTION

Description: set the characteristics of the current transaction

Syntax:

SET TRANSACTION transaction_mode [, ...]

...

where transaction_mode is one of:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }

...

```

#### 1、READ COMMITTED vs READ UNCOMMITTED

基于SI实现的并发控制，READ UNCOMMITTED和READ COMMITTED这两个级别在PostgreSQL中没有任何区别。

见下面例子，session 1启动事务，插入数据到表t1中，但不提交事务；这时候session 2读取t1的数据，按READ UNCOMMITTED的定义，应可“脏读”t1的数据，但实际上并没有获取脏数据。

```

-- session 1

testdb=# \d t1

Table "public.t1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

id | integer | | |

testdb=# select * from t1;

id

----

(0 rows)

testdb=# START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION

testdb=# insert into t1 values(1);

INSERT 0 1

testdb=#

-- session 2

testdb=# START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION

testdb=# select * from t1;

id

----

(0 rows)

```

详细可参考PostgreSQL手册[1]。

#### 2、REPEATABLE READ

在PostgreSQL 9.0或以下版本并没有该隔离级别，在9.1之后，PostgreSQL实现了Serializable Snapshot Isolation(简称SSI)，原来的SERIALIZABLE变为新增的REPEATABLE READ，新的SSI成为SERIALIZABLE。实质上，PostgreSQL中的REPEATABLE READ隔离级别是Snapshot Isolation，比起ANSI SQL标准定义的可重复读，除了P2 Non-Repeatable Read现象之外，还可以禁止P3 Phantom。

```

-- session 1

[local]:5432 postgres@testdb=# insert into t1 select generate_series(1,5);

INSERT 0 5

Time: 3.638 ms

[local]:5432 postgres@testdb=# select * from t1 where id <= 10;

id

----

1

2

3

4

5

(5 rows)

Time: 0.607 ms

[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION

Time: 0.292 ms

[local]:5432 postgres@testdb=#* select txid_current();

txid_current

--------------

502

(1 row)

Time: 0.284 ms

-- session 2

[local]:5432 postgres@testdb=# insert into t1 select generate_series(6,10);

INSERT 0 5

Time: 1.511 ms

[local]:5432 postgres@testdb=# select * from t1;

id

----

1

2

3

4

5

6

7

8

9

10

(10 rows)

Time: 0.434 ms

-- session 1

[local]:5432 postgres@testdb=#* select * from t1 where id <= 10;

id

----

1

2

3

4

5

(5 rows)

Time: 0.536 ms

```

如上所示，得益于快照隔离提供的一致性读，session 1查询数据表t1，返回1-5，session 2插入5行数据(6-10)。session 1再次查询表t1，返回的数据仍然是1-5(事务启动时的快照)。

从理论上来说，快照隔离SI要求满足以下两个规则：

Rule 1：事务T读取数据对象x，其中x是T启动前已提交事务产生的最新版本

Rule 2：并发事务的写集合之间不相交。使用形式化语言描述，wset(T)表示事务T的写集合，设有两个并发事务T1和T2，SI要求wset(T1) ∩ wset(T2) = ∅，否则出现冲突，其中一个事务必须回滚。

冲突处理常用的协议包括FCW(First Commit Wins，先提交者胜)和FUW(First Updater Wins，先更新者胜)。

FCW：事务Ti准备提交时，检查是否存在其他已提交的事务变更了数据对象x，如存在，则回滚，否则提交

FUW：如事务Tj已持有数据对象x的锁，同时Ti希望变更x，则Ti必须等待直至Tj提交或回滚；如Tj提交，则Ti回滚，如Tj回滚，则Ti可以获取x的写锁，可以继续执行

PostgreSQL在REPEATABLE READ级别使用FUW协议。

|时间点|T1|T2|

| ------ | ------ | ------ |

|t1|START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |

|t2| |START TRANSACTION ISOLATION LEVEL REPEATABLE READ;|

|t3|update t1 set id = 1 where id = 5;|

|t4| |update t1 set id = 11 where id = 5;|

|t5|commit;|

|t6| |提示出错|

执行输出如下：

```

-- T1

[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION

Time: 0.197 ms

-- T2

[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION

Time: 0.181 ms

-- T1

[local]:5432 postgres@testdb=#* update t1 set id = 1 where id = 5;

UPDATE 1

Time: 0.430 ms

-- T2

[local]:5432 postgres@testdb=#* update t1 set id = 11 where id = 5;

---------->wait

-- T1

[local]:5432 postgres@testdb=#* commit;

COMMIT

Time: 3.241 ms

-- T2

[local]:5432 postgres@testdb=#* update t1 set id = 11 where id = 5;

ERROR: could not serialize access due to concurrent update

Time: 3172.768 ms (00:03.173)

```

#### 3、SERIALIZABLE

REPEATABLE READ隔离级别已然禁止了P3 Phantom幻读，PostgreSQL为何还提供SERIALIZABLE隔离级别？原因是可重复读(其实是SI)不能实现可串行化，还存在不可串行化的写偏序(Write Skew)现象。

**写偏序(Write Skew)**

使用形式化语言来表示：设rset(Tn)是事务Tn读数据涉及的数据项集合，wset(Tn)是事务Tn写数据涉及的数据项集合。

现有两个并发事务Ti和Tj，

1.Ti和Tj执行前，rset(Ti)和rset(Tj)之间满足某种业务约束C

2.wset(Ti) ∩ rset(Tj) ≠ ∅ 且 rset(Ti) ∩ wset(Tj) ≠ ∅

3.Ti和Tj执行后，rset(Ti)和rset(Tj)不再满足业务约束C

以上现象，称为Write Skew现象。

一个经典的例子，现有数据表doctor，业务约束要求处于oncall状态的医生人数不能少于1人，但如果存在并发事务同时更新不同医生的状态，则能会出现Write Skew现象。下面是一个例子，测试数据如下：

|name|oncall|remark|

| ------ | ------ | ------ |

|Alice|true||

|Bob|true||

|Jacky|false||

|Susan|false||

测试脚本：

```

drop table if exists doctor;

create table doctor(name varchar,oncall boolean,remark varchar);

insert into doctor(name,oncall,remark) values('Alice',true,'');

insert into doctor(name,oncall,remark) values('Bob',true,'');

insert into doctor(name,oncall,remark) values('Jacky',false,'');

insert into doctor(name,oncall,remark) values('Susan',false,'');

CREATE OR REPLACE FUNCTION change_doctor_status(pi_name in varchar)

RETURNS boolean

AS

$$

declare

v_count int := 0;

begin

-- 获取处于oncall状态的医生数量

select count(*) from doctor into v_count where oncall = true;

-- 大于等于2，则设置医生oncall为false

if (v_count >= 2) then

update doctor

set oncall = false

where name = pi_name;

-- 成功返回t

return true;

end if;

-- 失败返回f

return false;

end;

$$

LANGUAGE plpgsql;

```

并发事务T1和T2的操作序列：

|时间点|T1|T2|

| ------ | ------ | ------ |

|t1|START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |

|t2| |START TRANSACTION ISOLATION LEVEL REPEATABLE READ;| |

|t3|select change_doctor_status('Alice');| |

|t4| |select change_doctor_status('Bob');| |

|t5|commit;| |

|t6| |commit;|

T1和T2完成后，数据如下：

|name|oncall|remark|

| ------ | ------ | ------ |

|Alice|false||

|Bob|false||

|Jacky|false||

|Susan|false||

处于oncall状态的医生数变为0，违反了oncall状态医生人数不能少于1人的业务约束。

**Serializable Snapshot Isolation**

PostgreSQL在9.1之后实现了SSI，设置隔离级别为SERIALIZABLE可禁止Write Skew现象，出现冲突时遵循FCW协议。

重新初始化数据，仍然使用上述操作序列，隔离级别设置为SERIALIZABLE：

|时间点|T1|T2|

| ------ | ------ | ------ |

|t1|START TRANSACTION ISOLATION LEVEL SERIALIZABLE;| |

|t2| |START TRANSACTION ISOLATION LEVEL SERIALIZABLE;| |

|t3|select change_doctor_status('Alice');| |

|t4| |select change_doctor_status('Bob');| |

|t5|commit;| |

|t6| |commit;|

处于SERIALIZABLE隔离级别下，T2在执行commit的时候会报错：

```

-- T1

[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION

-- T2

[local]:5432 postgres@testdb=# START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION

-- T1

[local]:5432 postgres@testdb=#* select change_doctor_status('Alice');

change_doctor_status

----------------------

t

(1 row)

Time: 0.682 ms

-- T2

[local]:5432 postgres@testdb=#* select change_doctor_status('Bob');

change_doctor_status

----------------------

t

(1 row)

-- T1

[local]:5432 postgres@testdb=#* commit;

COMMIT

-- T2

[local]:5432 postgres@testdb=#* commit;

ERROR: could not serialize access due to read/write dependencies among transactions

DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.

HINT: The transaction might succeed if retried.

Time: 0.289 ms

```

SSI的实现原理，简单来说，PostgreSQL的SSI机制会在读操作时对读对象加SIRead锁，但该锁不会阻塞写也不会阻塞读仅用于判断是否会出现不可串行化的事务调度，通过该锁跟踪事务之间的冲突关系(rw依赖冲突)，如可能出现环型结构，则终止其中一个事务。由于实现算法已超出本文的范畴，在此不再展开。

值得一提的是，如果数据表上没有索引，PostgreSQL会在整个relation上加SIRead锁，这意味着对relation的所有写操作只能串行执行，极大的降低并发度。

|时间点|T1|T2|

| ------ | ------ | ------ |

|t1|START TRANSACTION ISOLATION LEVEL SERIALIZABLE;|

|t2| |START TRANSACTION ISOLATION LEVEL SERIALIZABLE;|

|t3|update tbl set c1 = 'x' where id = 1;|

|t4| |update tbl set c1 = 'x' where id = 50000;|

|t5|commit;|

|t6| |commit;|

示例表tbl没有索引，有10万行数据，值为1和50000的tuple不在同一个page中，在t6时刻T2提交时会报错。

### 三、参考资料

[1].[PostgreSQL Manual](https://www.postgresql.org/docs/12/sql-set-transaction.html)

[2].[A Critique of ANSI SQL Isolation Levels](https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf)

[3].[Concurrency Control](http://www.interdb.jp/pg/pgsql05.html)

weixin_39618176
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
深入理解PostgreSQL的MVCC并发处理方式
12-16
Postgre数据库的很大的卖点之一就是它处理并发的方式。我们的期望很简单：读永远不阻塞写，反之亦然。Postgres通过一个叫做 多版本并发控制(MVCC) 的机制做到了这一点。这个技术并不是Postgres所特有的：还有好几种数据库都实现了不同形式的MVCC，包括 Oracle、Berkeley DB、CouchDB 等等 。当你使用PostgreSQL来设计高并发的应用时，理解它的MVCC是怎么实现的很重要。它事实上是复杂问题的一种非常优雅和简单的解法。 MVCC如何工作 在Postgres中，每一个事务都会得到一个被称作为 XID 的事务ID。这里说的事务不仅仅是被 BEGIN – C
MVCC PostgreSQL实现事务和多版本并发控制的精华1
08-08
PostgreSQL 是一款强大的开源关系型数据库管理系统，它在实现ACID（原子性、一致性、隔离性和持久性）事务特性时，采用了多版本并发控制（MVCC）和Write-Ahead Logging（WAL）技术。MVCC保证了读写操作的并发性，而...
解读年度数据库PostgreSQL：如何处理并发控制（一）
Enmotech的博客
07-04 656
墨墨导读：最近电子工业出版社博文视点出版了《PostgreSQL指南：内幕探索》，日前「数据和云」公众号推荐了这本书并赠送了五本，百多位用户参与，几十条留言未能放出，为了...
Postgresql管理系列-第五章 并发控制
魂醉的一亩二分地
07-09 1918
当数据库中多个事务并发运行时，并发控制机制用于保持一致性和隔离性，这两个特性是ACID的两个属性。 有三种广泛的并发控制技术,即多版本并发控制(MVCC),严格两阶段锁(S2PL),和乐观并发控制(OCC),每个技术又有许多变体。在MVCC,每个写操作会创建一个新版本的数据项,同时保留旧版本。当一个事务读取数据项,系统选择一个版本,以确保事物之间的隔离性。MVCC的主要优势是,“读不会阻塞写，写也...
pgsql 在可重复读级别的情况下，报异常：ERROR: could not serialize access due to concurrent update
最新发布
祁东握力的博客
12-01 1092
第一个事务不论是修改提交或者是删除提交，被阻塞的第二个事务都不能修改、删除或者锁住第一个事务所影响的行。（2）那么第二个事务同样用锁方式（增删改查导致的锁都可以）操作相同数据行的数据，仅仅会被阻塞，不会抛出异常。此时第二个被阻塞的事务只要等第一个事务释放锁之后（事务提交或回滚），就可以继续执行。（1）第一个事务如果是仅仅查询导致的锁(select for update/share)，而不是修改的导致的锁。
PostgreSQL事物隔离级别之可重复读
ergdfhrt的博客
12-01 1753
可重复读 开启可重复读事务 BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; PostgreSQL 在该隔离级别下不会出现 幻读。 使用这个级别的应用必须准备好由于序列化失败而重试事务。 知识点 该隔离级别下，查询获取的是事务开始前提交的数据。 要注意的是： 读已提交获取的查询命令开始时已提交的数据，一个事务中前后查询到的数据可能不一致。 可重复读获取的是事务开始前已提交
postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费
07-27
PostgreSQL 的特性包括支持复杂查询、事务处理、多版本并发控制（MVCC）、以及丰富的数据类型等，这使得它在各种业务场景下都能展现出强大的适应性。 PostgreSQL 的市场地位是不容忽视的，它与商业数据库系统竞争，...
PostgreSQL教程(九)：事物隔离介绍
12-16
在SQL数据库系统中，事务隔离级别是确保多用户并发访问数据时保持数据一致性的重要机制。在本篇关于PostgreSQL教程的第九部分，我们将探讨“读已提交”和“可串行化”这两种事务隔离级别，这是PostgreSQL在9.1版本前...
2022-05-25 postgres中的并发控制的可重复读
技术之路
05-25 1099
目录 摘要: postgres的可重复读说明: postgres的可重复读的具体操作测试: 修改配置文件postgresql.conf，设置默认的隔离级别： 重新加载配置: 查看默认的隔离级别: 二. 先开启事务A 三. 开启事务B，修改特定行，并提交 四. 在事务A中， 修改同一行，造成可重复读的状态变化，查看postgres的处理 结论： 一. postgres对于可重复读的隔离级别，如果出现读取行的状态变化，则本事务将会提交失败并回滚 二. mysql的可重复读，如果出现读..
事务 / 分布式事务的常见解决方案
BigBug_500的博客
05-30 506
分布式事务的实现方案 XA 方案：两阶段提交。事务时间长,锁数据时间长，吞吐量低（实现方式 JTA） TCC 方案（Try Confirm Cancel）：强一致性，吞吐量较方案一高 SAGA 方案：保证最终一致性 本地消息表 可靠消息最终一致性方案 最大努力通知方案 ...
数据库---多事务并发访问问题 --ZT
dandfly的专栏
04-26 193
数据库---多事务并发访问问题(脏读...) (2009-05-08 21:39:35) 转载 标签： it 分类：hibernate理解 数据库带来的并发问题包括：     　　1.丢失或覆盖更新。(幻像读)  　　2.未确认的相关性（脏读）。  　　3.不一致的分析（非重复读）。 详细描述如下： 一，丢失更新   当多个事务选择同一...
odoo16 更新bus_presence报错
jiafeitutu的博客
11-16 205
odoo16 更新bus_presence报错
金仓数据库KingbaseES数据库概念（四）--事务
arthemis_14的博客
07-13 1314
金仓数据库KingbaseES数据库概念（四）--事务
POSTGRESQL 通过例子来验证POSTGRESQL 的序列化
sql server的专栏
03-01 376
隔离级别是有一套理论的，在每种数据库中，实现的方式不同，实现的结果也不同，最近会有几期的文字对不同的数据库的隔离级别探探究竟。传统数据库中的隔离级别是配合MVCC 多版本控制来完成读取不会...
数据库事务隔离之repeatable read
greywolf5的博客
06-10 1777
Repeatable read isolation level 只能看见transaction开始前提交的数据；看不见未提交的数据和在当前transaction执行当中，其他并发transaction提交的数据。在同一个transaction内，当前命名可以看到上一条语句执行的结果。在PostgreSQL里，repeatable read解决了出serialization anomalies外所有的缺陷。在标准里面，只说了具体数据库的实现中各个的isolation level最少要处理哪些缺陷，没说不能多处
postgres -- 一个问题引发的事务探究（二）
lwgzj的博客
09-04 4693
这一篇来了解下 数据库 的隔离机制 说道隔离机制，就要先说下数据库事务的4大特性： ⑴ 原子性（Atomicity） 　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。 ⑵ 一致性（Consistency） 　　一致性是指事务必须使数
PostgreSQL问题解决--连接失败
热门推荐
ASIN的专栏
06-26 7万+
问题在pgAdmin中，将本地数据库连接的host由localhost或127.0.0.1改为自己的真实ip–10.xxx.xxx.xxx后，连接失败，报错如下：psql: could not connect to server: Connection refused Is the server running on host "my host name" (IP) and accepting
Postgrseql - transaction - 事务操作及隔离级别
chuckchen1222的博客
12-03 701
事务的操作有 begin(start transaction), savepoint, rollback, commit, release   事务开始： begin/start transaction [ transaction_mode [, ...] ] 设置事务隔离级别，读写/只读， ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ |...
StickOut: A Desktop Sticky Notes Application in the .NET Framework 2.0
huayang912的专栏
12-01 5353
AL ZabirOmarAlZabir@gmail.comMarch 2006Applies to:Microsoft .NET Framework 2.0Microsoft .NET RemotingMicrosoft OfficeMicrosoft Visual StudioWindows FormsSummary: StickOut is a desktop sticky n
postgresql -数据库事务与并发控制
09-16
总而言之，PostgreSQL 提供了强大的数据库事务和并发控制机制，通过锁机制、MVCC 以及事务隔离级别来处理并发操作和保证数据的一致性。这些特性使得 PostgreSQL 成为处理高并发场景下数据操作的理想选择。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值