进阶数据库系列（十四）：PostgreSQL 事务与并发控制

当多个事务并发执行时, 即使每个单独的事务都正确执行, 数据库的一致性也可能被破坏.。

为了控制 并发事务 之间的相互影响, 解决并发可能带来的数据不一致问题, 数据库的并发控制系统 引入了 基于锁的并发控制(Lock-Based Concurrency Control) 和 基于多版本的并发控制机制 MVCC (Mult-Version。

事务

事务 是数据库系统执行过程中最小的逻辑单位。

当事务被提交时, 数据库管理系统 要确保一个事务中的 所有操作都成功完成, 并在数据库中永久保存; 如果一个事务中的一部分没有成功, 则系统会把数据库回滚到操作执行之前的状态。

事务有 4 个特性:

1. 原子性(Atomicity): 一个事务的所有操作, 要么全部执行, 要么全部不执行。
2. 一致性(Consistency): 保证数据库从一个正确的状态(满足约束)到另一个正确的状态。
3. 隔离性(Isolation): 事务并发执行时, 可能会交叉执行, 从而导致不一致的情况发生. 确保事务并发执行时, 每个事务都感觉不到有其他事务在并发的执行。
4. 持久性(Durability): 一个事务完成后, 它对数据库的改变应该永久保存在数据库中。

这 4 个特性也称之为 ACID.

• 事务一致性 由主键, 外键这类约束保证。
• 持久性 由预写日志(WAL) 和数据库管理系统的恢复子系统保证。
• 原子性和隔离性 由 事务管理器 和 MVCC 来控制。

事务并发引发的问题

如果所有的事务都按照顺序执行, 那么执行时间就没有重叠交错, 也就不会有并发问题。

PostgreSQL 把 事务并发 导致的问题 总结为:

• 脏读(Dirty read), 事务A 读取了 事务B 已经修改但是还没有提交的数据。
• 不可重复读(Non-repeatable read), 事务A 读取了数据X; 然后 事务B 修改了数据X 并提交; 然后事务A 再次读取数据X. 对于事务A来说, 两次读取结果不一致. 这种现象就是 不可重复读。
• 幻读(Phantom read), 一个事务的两次执行相同的查询, 结果集数目不一致. 幻读 可以 认为是 受 INSERT 和 DELETE 影响 不可重复读 的特例。
• 序列化异常(Serialization anomaly), 在可重复读情况下, 可能会出现序列化异常. 比如 X=1 事务A 执行 X++ 操作; 在事务A 提交之前, 事务B 修改 X=10 并提交成功; 由于事务A 是可重复读的, 事务A 看到的数据还是 X=1. 这就发生了序列化异常: 先执行事务A 和 先执行事务B 的结果是不一样的。
• ANSI SQL 标准的事务隔离级别

为了避免 事务与事务之间 并发执行 引发的副作用, 最简单的方法是 串行地 执行事务, 但是 串行化 会大幅降低系统吞吐量, 降低系统资源利用率。

为此, ANSI(American National Standards Institute, 美国国家标准学会) SQL 标准定义了 4 类事务隔离级别:

• 读未提交(Read Uncommitted): 所有事务都可以看到其他未提交事务的执行结果. 可以看到 读未提交 允许 脏读 发生, 脏读是非常危险的, 查询结果非常不可控, 所以 读未提交 事务隔离级别 很少实际应用。
• 读已提交(Read Committed): 这是 PostgreSQL 默认的隔离级别, 它满足了 一个事务 只能看到 已提交事务 对关联数据所做的改变。
• 可重复读(Repeatable Read): 确保同一个事务在看到的数据内容是一致的。
• 可序列化(Serializable): 最高的隔离级别, 通过强制事务排序, 使之不可能相互冲突, 从而解决幻读问题。

下表是 ANSI SQL 标准定义的事务隔离级别与读现象的关系:

对于同一个事务来说, 不同的事务隔离级别执行结果可能不同。

事务隔离级别越高, 越能保证数据的完整性和一致性, 但增加了阻塞其他事务的概率, 并发性能越差, 吞吐量也越低。

对于大多应用程序, 优先考虑 Read Committed 隔离级别，它可以避免脏读, 而且有较好的并发性能。尽管它会导致不可重复读,幻读问题,这类问题可以由应用程序加锁来控制。

PostgreSQL 的事务隔离级别

postgresql中的两种隔离级别如下：

读已提交：

读已提交是postgresql里的默认级别。当一个事务运行在这个隔离级别时，一个select查询只能看到查询开始之前已提交的数据，而无法看到未提交的数据或者在查询执行期间其他事务已经提交的数据。

如果两个事务在对同一组数据进行更新操作，那么第二个事务需要等待第一个事务提交或者更新回滚。如果第一个事务进行提交，系统将重新计算查询条件，符合条件后第二个事务继续进行更新操作；如果第一个事务进行更新回滚，那么他的作业将被忽略，第二个事务将继续更新最初发现的行。

可串行化：

可串行化基本提供最严格的事务隔离。这个级别模拟串行的事务执行，就好像事务将一个接着一个地串行（而不是并行）执行。不过，使用这个级别的应用必须准备在串行化失败的时候重新启动事务。

如果两个事务在对同一组数据进行更新操作，那么串行化事务就将等待第一个正在更新的事务提交或回滚。如果第一个事务提交了，那么串行化事务将回滚，从头开始重新进行整个事务；如果第一个事务回滚，那么它的影响将被忽略，这个可串行化的事务就可以在该元祖上进行更新操作。

下面的表格是 PostgreSQL 中不同的事务隔离级别与读现象的关系:

PostgreSQL 只实现了 3 种 隔离级别。在 PostgreSQL 中, Read Uncommitted 和 Read Committed 是一样的。

查看和设置数据库的事务隔离级别

查看 PostgreSQL 全局事务隔离级别:

SELECT name, setting 
FROM pg_settings 
WHERE name = 'default_transaction_isolation';

-- 或者
SELECT  current_setting('transaction_isolation');

修改全局的事务隔离级别:

ALTER SYSTEM 
SET default_transaction_isolation TO  'REPEATABLE READ';

-- 修改之后 reload 实例使之生效
SELECT pg_reload_conf();

PostgreSQL 事务管理

在postgresql里，一个事务是通过把SQL命令用：begin 和 commit命令包围实现的。事务块是指包围在begin 和 commit之间的语句。

事务控制命令仅用于DML命令INSERT，UPDATE和DELETE。创建表或删除它们时不能使用它们，因为这些操作会在数据库中自动提交。

事务控制命令

BEGIN TRANSACTION：开始事务

BEGIN TRANSACTION命令：可以使用BEGIN TRANSACTION或简单的BEGIN命令来开始事务。这样的事务通常会持续下去，直到遇到下一个COMMIT或ROLLBACK命令。但如果数据库关闭或发生错误，则事务也将ROLLBACK。 以下是启动/开始事务的简单语法：

BEGIN;
or
BEGIN TRANSACTION;

COMMIT：保存更改

或者您可以使用END TRANSACTION命令

COMMIT命令是用于将事务调用的更改保存到数据库的事务命令。COMMIT命令自上次的COMMIT或ROLLBACK命令后将所有事务保存到数据库。COMMIT命令的语法如下：

COMMIT;
or
END TRANSACTION;

ROLLBACK：回滚更改

ROLLBACK命令ROLLBACK命令是用于还原尚未保存到数据库的事务的事务命令。自上次发出COMMIT或ROLLBACK命令以来，ROLLBACK命令只能用于撤销事务。ROLLBACK命令的语法如下：

ROLLBACK;

普通的事务操作

打开/关闭自动提交

在使用psql等一些客户端的工具时，事务的自动提交功能是默认打开的，所以我们每次执行一条SQL语句都会自动提交。在psql中手动的打开自动提交的方法是执行以下命令：

postgres=# set AUTOCOMMIT on
postgres-# 
postgres-# set AUTOCOMMIT off
postgres-# 
postgres-# \echo :AUTOCOMMIT 
on
postgres-# 

手动开启事务

Postgresql中可以使用BEGIN命令来手动开启事务，手动开启事务之后也就相当于关闭了事务自动提交的功能。

如果我们要手动控制事务，建议使用这种方式:

• 1.使用 BEGIN 可以更见显式的提醒我们当前是在手动事务中，需要手动commit或者rollback；
• 2.\set AUTOCOMMIT off这种方式，只适合在psql中使用，因为AUTOCOMMIT是psql的一个变量。

下面的例子，就是使用BEGIN开启一个事务：

postgres=# create table t1(id int,name text);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# insert into t1 (id,name) values (1,'李四');
INSERT 0 1
postgres=*# insert into t1 (id,name) values (2,'张三');
INSERT 0 1
postgres=# 

此时，还没有提交，我们可以开启另外一个会话查看t1表，发现表中还是空的：

postgres=# select * from t1;
 id | name 
----+------
(0 rows)

postgres=#

提交的方式有两种，第一种是显示的使用commit命令：

postgres=*# commit;
COMMIT
postgres=# 

使用这种方式提交并不会关闭当前的事务，要想提交时同时结束事务，需要使用end命令：

postgres=# begin;
BEGIN
postgres=*# insert into t1 (id,name) values (3,'麻子');
INSERT 0 1
postgres=*# end;
COMMIT
postgres=# 

# 提交前
postgres=# select * from t1;
 id | name 
----+------
  1 | 李四
  2 | 张三
(2 rows)

# 提交后
postgres=# select * from t1;
 id | name 
----+------
  1 | 李四
  2 | 张三
  3 | 麻子
(3 rows)

postgres=#

不管使用哪种方式，提交之后，我们在另外的session中都会查看到t1表中新增的数据。

在手动开启事务时，也可以使用检查点savepoint。

下面的语句，就是在插入第一条数据之后保存了一个检查点，然后继续insert，最后回滚到保存的检查点再进行提交，最终的效果是只有第一条数据插入有效：

postgres=# begin;
BEGIN
postgres=*# insert into t1 (id,name) values (4,'李四');
INSERT 0 1
postgres=*# savepoint apoint;
SAVEPOINT
postgres=*# insert into t1 (id,name) values (5,'张三');
INSERT 0 1
postgres=*# rollback to savepoint apoint;
ROLLBACK
postgres=*# end;
COMMIT
postgres=#

查询以上语句执行的结果：

postgres=# select * from t1;
 id | name 
----+------
  1 | 李四
  2 | 张三
  3 | 麻子
  4 | 李四
(4 rows)

postgres=# 

DDL事务

DDL事务是Postgresql很有特色的一个功能，其它的关系型数据库很多是不支持DDL事务的。所谓DDL事务就是在执行create table、alter table等这些DDL语句时，支持事务的回滚或提交。

DDL事务创建的方式其实和普通事务一致，都是使用BEGIN命令开启一个事务，也可以设置savepoint，然后进行commit或者rollback。下面的例子就是开启了一个DDL事务，创建了t3、t4然后设置savepiont，再创建t5，最后rollback到保存的检查点并进行提交。最终的效果就是只创建了t3和t4没有创建t5。

postgres=# begin;
BEGIN
postgres=*# create table t3(id int);
CREATE TABLE
postgres=*# create table t4(id int);
CREATE TABLE
postgres=*# savepoint bpoint;
SAVEPOINT
postgres=*# create table t5(id int);
CREATE TABLE
postgres=*# rollback to savepoint bpoint;
ROLLBACK
postgres=*# end;
COMMIT

我们可以查看创建的结果，以验证符合我们的预期（t1和t2是之前创建好的表）：

postgres=# \dt t*
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
 public | t3   | table | postgres
 public | t4   | table | postgres
(3 rows)

分布式事务

目前分布式架构系统的的势头愈演愈烈，在分布式的架构中一个回避不了的问题就是怎么把不同服务或者不同数据块实例间的操作放到同一个事务，也就是支持分布式事务？

目前比较成熟的方案有两阶段提交、三阶段提交、以及一些比较成熟的第三方框架：阿里的seata、txlcn等等。Postgresql为了支持分布式的事务，实现了两阶段提交的事务方式。

所谓的两阶段提交，就是将事务的提交分成了两个过程：

• （1）在执行完成DML语句（update、insert、delete）之后，先进行事务的预提交。预提交的过程不会真的提交数据，但是数据库可以保证只要进行了预提交，数据就不会再丢失，即使数据库发生了重启、宕机。Postgresql中使用PREPARE TRANSACTION命令进行预提交。
• （2）完成了预提交之后，就可以真正的提交事务了，Postgresql中使用COMMIT PREPARED命令进行数据的最终提交。

使用分布式事务修改下postgresql.conf中max_prepared_transactions的参数，这个参数默认是0，表示不支持分布式事务;需要改成一个大于0的数字，然后重启数据库。

max_prepared_transactions只能通过修改postgresql.conf完成，如果通过set命令修改会报错：

postgres=# set max_prepared_transactions=10;
ERROR:  parameter "max_prepared_transactions" cannot be changed without restarting the server

例子还是向t1表中插入两条数据，然后用分布式事务的方式进行提交。不过在此之前，

step 1.开启事务并执行insert语句：

postgres=# begin;
BEGIN
postgres=*# insert into t1 values(1,'tom');
INSERT 0 1
postgres=*# insert into t1 values(2,'jerry');
INSERT 0 1

step 2.使用PREPARE TRANSACTION进行预提交：

postgres=*# PREPARE TRANSACTION 'transaction_001';
PREPARE TRANSACTION
postgres=# 

transaction_001是我们为分布式事务定义的一个事务ID，通过这个事务ID可以保证所有的数据库实例的事务属于同一个分布式的事务，它需要保证在全局的共享和唯一。

step 3.完成了上述步骤之后，就完成了事务的预提交过程，此时如果我们重启了数据库，预提交的数据也不会丢失。

pg_ctl restart

step 4.使用COMMIT PREPARED进行最终提交：

重启了数据库之后，我们先查看下t1表里面有没有我们插入的数据，以验证预提交阶段是不会实际插入数据的：

postgres=# select * from t1;
 id | name 
----+------
  1 | 李四
  2 | 张三
  3 | 麻子
  4 | 李四
(4 rows)

postgres=# c

step 5.查询的数据为空，接下来再执行最终提交的命令：

postgres=# commit prepared 'transaction_001';
COMMIT PREPARED
postgres=#

再去查询t1表，发现数据被成功插入进去。

postgres=# select * from t1;
 id | name  
----+-------
  1 | 李四
  2 | 张三
  3 | 麻子
  4 | 李四
  1 | tom
  2 | jerry
(6 rows)

postgres=# 

PostgreSQL 的并发控制

为了保证事务的隔离性, 系统必须对 并发事务 之间的相互作用加以控制, 这就是数据库管理系统的 并发控制器 要做的事情。

并发控制模型有 基于锁的并发控制(Lock-Based Concurrency Control) 和 基于多版本的并发控制(Multi-Version Concurrency Control)。

基于锁的并发控制

为了解决并发问题, 数据库引入了 “锁” 的概念。

有两种锁类型: 排它锁(Exclusive locks, X 锁) 和 共享锁(Share locks, S 锁)。

加锁对象的大小称为 锁粒度(granularity)。

加锁的对象可以是 逻辑单元: 属性值, 属性值的集合, 关系, 索引项, 甚至整个数据库。

也可以是物理单元: 页(数据页或索引页), 物理记录等。

基于多版本的并发控制(MVCC)

MVCC通过把数据项的旧值保存在系统中, 来保证并发事务的正确性。

一般把 基于锁的并发控制 称为 悲观机制; 把 MVCC 称为 乐观机制. 这是因为 锁 是一种预防性机制, 写会阻塞读, 读会阻塞写; MVCC 是一种后验性机制, 等到提交的时候才检查是否有冲突。

由于 MVCC 读写不会相互阻塞, 避免了大粒度和长时间的锁定, 能更好地适应 对读的响应速度 和 并发性要求高的场景, 常见的数据库如 Oracle, PostgreSQL, MySQL(Innodb) 都使用 MVCC 做 并发控制机制。

在 MVCC 中, 每一个写操作会创建一个新的版本. 当事务发起一个读操作时, 并发控制器选择一个版本读, 连同版本号一起读出, 在更新时对此版本号加一。

PostgreSQL 为每个事务分配一个递增的, int32 整型 数作为 唯一的事务ID, 即 xid. 。

PostgreSQL 内部数据结构中, 每个元组(行记录) 有 4 个与事务可见性相关的 隐藏列:

• xmin, 创建该行数据的 xid;
• xmax, 删除改行的xid;
• cmin, 插入该元组的命令在事务中的命令序列号;
• cmax, 删除该元组的命令在事务中的命令序列号.

参考文章：https://blog.csdn.net/fsdgsddaer/article/ details/120606535 http://dbaselife.com/doc/1588/