PG的事务和并发控制

PG的事务和并发控制

1 概述

事务与并发控制
数据完整性有关
并发与吞吐量有关

并发体现的方面
网络吞吐量
CPU使用率
IO使用率
内存资源使用率
其他资源消耗

** 并发可能会引起事务的混乱**
ACID特性
控制数据库并发
基于锁的并发控制（ Locked- based Concurrency Control
基于多版本的并发控制（ MVCC Mutil Version Concurrency Control）

广义上的并发控制
MVCC
读写互相不阻塞
S2PL（严格两阶段锁定 Strict two phase locking）
写入数据会阻塞数据读取
** OCC**（乐观并发控制 Optimistic Concurrency Contro）

事务
实际上就是一个执行的语句块
BEGIN
执行语句块
EnD
在 PostgreSQL中，通常对于一个事务需要显式指定.TRANSACTION（事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

2 事务的属性ACID:

事务具有以下四个标准属性的缩写ACID，通常被称为： yiibai.com

• 原子性: 确保工作单位内的所有操作都成功完成，否则，该事务所被中止在故障点，和以前的操作将回滚到以前的状态。

ATM取钱
用户1转账给用户2
用户2账户上操作失败了，本次事务就整体失败
用户1和用户2账户都不会被修改

• 一致性: 确保数据库正确地改变状态后成功提交的事务。

业务逻辑整体完整
用户1给用户2转账，无论事务操作是否成功
两个用户账户的总金额不会改变
1—>2转账成功500用户1：1000-500=500
用户2：2000+500=2500
2转账失败500用户1：1000
用户2:2000
数据库的完整性
表结构上面的约束，索引都是完整正确的。

• 隔离性: 使交易操作相互独立的和透明的。
• 持久性: 确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

表1:事务的4个特征ACID及响应的实现技术

ACID	实现技术
原子性	MVCC
一致性	约束（主键，外键等）
隔离性	MVCC
持久性	WAL

可以看到PostgreSQL中支撑ACID的主要是MVCC和WAL两项技术。MVCC和WAL是两个比较成熟的技术，通常的关系数据库中都有相应的实现，但每个数据库具体的实现方式又存在很大差异。下面介绍一下PostgreSQL中MVCC和WAL的基本实现原理。

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

3 事务控制:

使用下面的命令来控制事务：

• BEGIN TRANSACTION: 开始事务.
• COMMIT: 保存更改，或者可以使用END TRANSACTION命令.
• ROLLBACK: 回滚事务。

4 事务的隔离级别

四种隔离级别

SQL标准定义了四种隔离级别。最严格的是可序列化，在标准中用了一整段来定义它，其中说到一组可序列化事务的任意并发执行被保证效果和以某种顺序一个一个执行这些事务一样。其他三种级别使用并发事务之间交互产生的现象来定义，每一个级别中都要求必须不出现一种现象。注意由于可序列化的定义，在该级别上这些现象都不可能发生。

在各个级别上被禁止出现的现象是：

脏读：一个事务读取了另一个并行未提交事务写入的数据。

时间	事务A	事务B
T1		开始事务
T2	开始事务
T3		查询账户余额1000
T4		去除500元，余额500
T5	查询余额为500(脏读)

不可重复读：一个事务重新读取之前读取过的数据，发现该数据已经被另一个事务（在初始读之后提交）修改。

时间	事务A	事务B
T1	开始事务
T2	查询余额为1000	开始事务
T3		查询账户余额1000
T4		去除500元，余额500
T5		提交事务
T6	查询余额为500
T7	提交事务

幻读：一个事务重新执行一个返回符合一个搜索条件的行集合的查询， 发现满足条件的行集合因为另一个最近提交的事务而发生了改变。
序列化异常：成功提交一组事务的结果与一次运行这些事务的所有可能顺序不一致。

时间	事务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标准和PostgreSQL实现的事务隔离级别如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-al766qXg-1589641294695)(D:\SYBASE&informix&DB2\2 Postgresql\3 markdown笔记\体系架构\晟数体系架构7天之旅\4 PG的事务.assets\20190311164109266.png)]

PostgreSQL事务的隔离级别目前有4种，分别是：读未提交，读已提交，可重复读，串行化。在PostgreSQL里，你可以请求四种可能的事务隔离级别中的任意一种。但是在内部， 实际上只有三种独立的隔离级别，分别对应读已提交，可重复读和可串行化。如果你选择了读未提交的级别， 实际上你用的是读已提交，在重复读的PostgreSQL执行时，幻读是不可能的， 所以实际的隔离级别可能比你选择的更严格。这是因为把标准隔离级别映射到 PostgreSQL 的多版本并发控制架构的唯一合理的方法。

该表格也显示 PostgreSQL 的可重复读实现不允许幻读。而 SQL 标准允许更严格的行为：四种隔离级别只定义了哪种现像不能发生，但是没有定义哪种现像必须发生。某些PostgreSQL数据类型和函数关于事务的行为有特殊的规则。特别是，对一个序列的修改（以及用serial声明的一列的计数器）是立刻对所有其他事务可见的，并且在作出该修改的事务中断时也不会被回滚。

要设置一个事务的事务隔离级别，使用SET TRANSACTION命令。

SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

例如：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000003-0000001B-1
(1 row)

或者：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

1. 读已提交(读未提交相同)
   读已提交是PostgreSQL中的默认隔离级别。 当一个事务运行使用这个隔离级别时， 一个查询只能看到查询开始之前已经被提交的数据， 而无法看到未提交的数据或在查询执行期间其它事务提交的数据。务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变，造成了不可重复读。

2. 可重复读
   可重复读隔离级别只看到在事务开始之前被提交的数据；它从来看不到未提交的数据或者并行事务在本事务执行期间提交的修改（不过，查询能够看见在它的事务中之前执行的更新，即使它们还没有被提交）。这个级别与读已提交不同之处在于，一个可重复读事务中的查询可以看见在事务中第一个非事务控制语句开始时的一个快照，而不是事务中当前语句开始时的快照。因此，在一个单一事务中的后续SELECT命令看到的是相同的数据，即它们看不到其他事务在本事务启动后提交的修改。

3. 可序列化
   可序列化隔离级别提供了最严格的事务隔离。这个级别为所有已提交事务模拟序列事务执行；就好像事务被按照序列一个接着另一个被执行，而不是并行地被执行。但是，和可重复读级别相似，使用这个级别的应用必须准备好因为序列化失败而重试事务。事实上，这个给力级别完全像可重复读一样地工作，除了它会监视一些条件，这些条件可能导致一个可序列化事务的并发集合的执行产生的行为与这些事务所有可能的序列化（一次一个）执行不一致。这种监控不会引入超出可重复读之外的阻塞，但是监控会产生一些负荷，并且对那些可能导致序列化异常的条件的检测将触发一次序列化失败。

实验

duyeweb=# \h begin
Command:     BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

读未提交

表示可以读到其他会话未提交的数据（postgresql不支持）。

读已提交

表示可以读到其他会话已提交的数据。

1. 创建一张表为test，插入一条记录

duyeweb=# create table test(a int, b varchar(128));
CREATE TABLE
duyeweb=# insert into test values(1, 'hello');
INSERT 0 1

2 在会话1中打开事务进行查询

duyeweb=# begin;
BEGIN
duyeweb=# select * from test;
 a |   b   
---+-------
 1 | hello
(1 row)

3 在会话2中打开事务进行更新

duyeweb=# begin;
BEGIN
duyeweb=# update test set b='xxxx';
UPDATE 1

4 此时在会话2中还没有关闭事务，在会话1中进行查询

duyeweb=# select * from test;
 a |   b   
---+-------
 1 | hello
(1 row)

5 发现会话1中的记录并没有进行改变。当提交会话2中的事务，在会话1中进行查询值已经改变

duyeweb=# begin;
BEGIN
duyeweb=# update test set b='xxxx';
UPDATE 1
duyeweb=# commit;
COMMIT

再次查询：

duyeweb=# select * from test;
 a |  b   
---+------
 1 | xxxx
(1 row)

可重复读

表示在一个事务中，执行同一条SQL，读到的是同样的数据（即使被读的数据可能已经被其他会话修改并提交）。

1. 在会话1中打开可重复读事务，进行查询

duyeweb=# begin transaction isolation level repeatable read;
BEGIN
duyeweb=# select * from test;
 a |  b   
---+------
 1 | xxxx
(1 row)

2 在会话2中进行更新操作

duyeweb=# update test set b='yyyyy';
UPDATE 1

3 在会话1中进行，发现会话1中的记录没有因为会话2的提交而变化

duyeweb=# select * from test;
 a |   b   
---+-------
 1 | xxxx
(1 row)

4 在会话1中进行提交，再查询，发现会话1中的记录变化了

duyeweb=# commit;
duyeweb=# select * from test;
 a |   b   
---+-------
 1 | yyyyy
(1 row)

串行化

表示并行事务模拟串行执行，违反串行执行规则的事务，将回滚。

1 在会话 1中打开事务串行

duyeweb=# begin transaction isolation level serializable;

2 在会话2中打开事务串行

duyeweb=# begin transaction isolation level serializable;

3 在会话1中进行插入操作

duyeweb=# insert into test select * from test; 
INSERT 0 1
duyeweb=# select * from test;                 
 a |   b   
---+-------
 1 | yyyyy
 1 | yyyyy
(2 rows)

4 在会话2中进行插入操作

duyeweb=# insert into test select * from test; 
INSERT 0 1
duyeweb=# select * from test; 
 a |   b   
---+-------
 1 | yyyyy
 1 | yyyyy
(2 rows)

5 提交会话1中的事务，能够正常提交，进行查询能够查到插入的记录

duyeweb=# end;
COMMIT
duyeweb=# select * from test;
 a |   b   
---+-------
 1 | yyyyy
 1 | yyyyy
(2 rows)

6 当提交会话2的事务时会报错

duyeweb=# end;
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.

其他的测试办法可以想见https://www.jianshu.com/p/2463578b2639

从上面的三个测试例子中我们可以看出，

1、在读提交的隔离级别中，查询可以看到select语句开始执行前的所有session 提交的所有数据更改；

2、在可重复读和序列化读级别，查询只能看到事务开启时刻之前的所有 session提交的数据，保留的数据是事务开启时刻的快照版本，一直到commit这一时间点；

3、PG中序列化读取级别比可重复读级别更加严格，可重复读不能修改其他事务语句修改的记录，但是可以修改快照里面的其他记录，而序列化读只要有事务进行了修改，那么他做的所有的操作都会被回滚

ANSISQL可串行化
PostgreSQL中：可串行化异常
PostgreSQL使用MVCC的变体（SI） Snapshot Isolation


事务A(窗口一)
sdedu@sdedudb=>BEGIN
sdedu@sdedudb=>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET
sdedu@sdedudb=>SELECT * FROM t1 WHERE id=3,
id name
3|DB2
事务B(窗口二)
BEGIN
sdeduasdedudb=>UPDATE t1 SET name= 'SQLServer' WHERE id=3;
UPDATE 1
sdedu@sdedudb=>END
COMMIT
事务A(窗口一)
sdedu@sdedudb=>UPDATE t1 SET name ='Redis' WHERE id=3;
ERROR: could not serialize access due to concurrent update

5 查看和修改隔离级别

设置事务的隔离等级
会话级别
SET SESSION TRANACTION ISOLATION LEVEL隔离等级
系统级别
ALTER SYSTEM SET TRANSACTION ISOLATION

postgresql. conf文件修改
查看事务的隔离等级
sdedu@sdedudb->SELECT name,setting FROM pg_settings
WHERE name ~ ‘transaction’;
name
default transaction isolation read committed
transaction isolation read committed

查看事务隔离级别

(postgres@[local]:5432)[akendb01]#show transaction_isolation ;

transaction_isolation

read committed
(1 row)
(postgres@[local]:5432)[akendb01]#

临时修改会话的事务隔离级别：

(postgres@[local]:5432)[postgres]#begin transaction isolation level repeatable read;
BEGIN
(postgres@[local]:5432)[postgres]#show transaction_isolation ;

transaction_isolation

repeatable read
(1 row)
(postgres@[local]:5432)[postgres]#begin transaction isolation level read committed;
WARNING: there is already a transaction in progress
(postgres@[local]:5432)[postgres]#end;
COMMIT
(postgres@[local]:5432)[postgres]#show transaction_isolation ;

read committed
(1 row)
(postgres@[local]:5432)[postgres]#

永久修改事务隔离级别，需要修改postgresql.conf文件参数值。

[postgres@akendb01]KaTeX parse error: Expected 'EOF', got '#' at position 52: …ostgresql.conf #̲default_transac…
或者直接alter system将参数值写到postgresql.auto.conf参数文件中

(postgres@[local]:5432)[postgres]#alter system set default_transaction_isolation=“repeatable read”;
ALTER SYSTEM
(postgres@[local]:5432)[postgres]#show default_transaction_isolation;

default_transaction_isolation

read committed
(1 row)
重新reload生效：

(postgres@[local]:5432)[postgres]#exit
[postgres@akendb01]$p{g}_{c}tlreloadserversignaled[postgres@akendb01]$psql
psql (11.5)
Type “help” for help.
(postgres@[local]:5432)[postgres]#show default_transaction_isolation;

default_transaction_isolation

repeatable read
(1 row)
(postgres@[local]:5432)[postgres]#
默认为read committed，建议改回来：

(postgres@[local]:5432)[postgres]#alter system set default_transaction_isolation=“read committed”;
ALTER SYSTEM
(postgres@[local]:5432)[postgres]#exit
[postgres@akendb01]$p{g}_{c}tlreloadserversignaled[postgres@akendb01]$psql
psql (11.5)
Type “help” for help.
(postgres@[local]:5432)[postgres]#show default_transaction_isolation;

default_transaction_isolation

read committed
(1 row)
(postgres@[local]:5432)[postgres]#