PostgreSql 事务

一、事务的 ACID 特性

  在日常操作中，对于一组相关操作，通常需要其全部成功或全部失败。在关系型数据库中，将这组相关操作称为事务。事务具有的四个特性简称为 ACID。

• 原子性（Atomicity）：保证事务中的操作要么全部成功，要么全部失败，不会只成功一部分。
• 一致性（Consistency）：数据修改的有效性，并且遵循一定的业务规则。
• 隔离性（Isolation）：决定了并发事务之间的可见性和相互影响程度。
• 持久性（Durability）：确保已经提交的事务必须永久生效。

二、事务的使用

2.1 自动提交事务

postgres=# \echo :AUTOCOMMIT
on
postgres=# create table accounts(id serial primary key,user_name varchar(50),balance numeric(10,4));
CREATE TABLE
postgres=# alter table accounts add constraint bal_check check(balance >= 0);
ALTER TABLE
postgres=# insert into accounts(user_name, balance) values ('usera', 6000);
INSERT 0 1
postgres=# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
(1 row)

2.2 手动提交事务

postgres=# \set AUTOCOMMIT off
postgres=# \echo :AUTOCOMMIT
off
postgres=# begin;
BEGIN
postgres=*# insert into accounts(user_name, balance) values ('userb', 0);
INSERT 0 1
postgres=*# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
(2 rows)

postgres=*# commit;
COMMIT
postgres=# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
(2 rows)

2.3 手动回滚事务

postgres=# begin;
BEGIN
postgres=*# insert into accounts(user_name, balance) values ('userc', 2000);
INSERT 0 1
postgres=*# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
  3 | userc     | 2000.0000
(3 rows)

postgres=*# rollback;
ROLLBACK
postgres=# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
(2 rows)

2.4 手动回滚事务至保存点

postgres=# begin;
BEGIN
postgres=*# insert into accounts(user_name, balance) values ('userc', 2000);
INSERT 0 1
postgres=*# savepoint sv1;
SAVEPOINT
postgres=*# insert into accounts(user_name, balance) values ('userd', 0);
INSERT 0 1
postgres=*# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
  4 | userc     | 2000.0000
  5 | userd     |    0.0000
(4 rows)

postgres=*# rollback to sv1;
ROLLBACK
postgres=*# commit;
COMMIT
postgres=# table accounts;
 id | user_name |  balance
----+-----------+-----------
  1 | usera     | 6000.0000
  2 | userb     |    0.0000
  4 | userc     | 2000.0000
(3 rows)

三、并发与隔离

多个用户访问相同数据时，可能导致如下问题：

• 脏读（dirty read）：一个事务能够读取其他事务未提交的修改。
• 不可重复读（nonrepeatable read）：一个事务读取某个记录后，再次读取该记录时数据发生了改变（被其他事务修改并提交）。
• 幻读（phantom read）：一个事务按照某个条件查询一些数据后，再次执行相同查询时结果的数量发生了变化（另一个事务增加或者删除了某些数据并且完成提交）。幻读和非重复读有点类似，都是由于其他事务修改数据导致的结果变化。
• 更新丢失（lost update）：当两个事务同时读取某一记录，然后分别进行修改提交，就会造成先提交的事务的修改丢失。

为了解决并发问题， SQL 标准定义了 4 种不同的事务隔离级别（从低到高）：

• Read Uncommitted（读未提交）：最低的隔离级别，实际上就是不隔离，任何事务都可以看到其他事务未提交的修改；该级别可能产生各种并发异常。不过，PostgreSQL 消除了 Read Uncommitted 级别时的脏读，因为它的实现等同于 Read Committed。
• Read Committed（读已提交）：一个事务只能看到其他事务已经提交的数据，解决了脏读问题，但是存在不可重复读、幻读和更新丢失问题。这是 PostgreSQL 的默认隔离级别。
• Repeated Read（可重复读）：一个事务对于同某个数据的读取结果不变，即使其他事务对该数据进行了修改并提交；不过如果其他事务删除了该记录，则无法再查询到数据（幻读）。SQL 标准中的可重复读可能出现幻读，但是 PostgreSQL 在可重复读级别消除了幻读。
• Serializable（可串行化）：最高的隔离级别，事务串行化执行，没有并发。

修改隔离级别示例：

--显示数据库隔离级别
show transaction_isolation;

--事务中修改隔离级别
begin;
set transaction isolation level {serializable|repeatable read|read committed|read uncommitted};
……
……
commit;

四、两阶段提交

  PostgreSQL 数据库支持两阶段提交协议（默认禁用此功能，仅在分布式架构中使用）。在分布式系统中，事务中往往包含了多台数据库上的操作，虽然单台数据库的操作能够保证原子性，但多台数据库之间的原子性就需要通过两阶段提交来实现了，两阶段提交是实现分布式事务的关键。

  两阶段提交协议有如下 5 个步骤:

• 应用程序先调用各台数据库做一些操作，但不提交事务。然后应用程序调用事务协调器（该协调器可能也是由应用自己实现的）中的提交方法。
• 事务协调器将联络事务中涉及的每台数据库，并通知它们准备提交事务，这是第一阶段的开始，此时 PostgreSQL 中调用 PREPARE TRANSACTION 命令。
• 各台数据库接收到 PREPARE TRANSACTION 命令后，PostgreSQL 会将已准备好提交的信息写入持久存储区中，如果无法完成此，会直接返回失败给事务协调器。
• 事务协调器接收所有数据库的响应。
• 在第二阶段，如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令 “ROLLBACK PREPARED” 给各台数据库。如果所有数据库的响应都是成功的，则向各台数据库发送 COMMIT PREPARED 命令，通知各台数据库事务成功。

示例：

--修改 max_prepared_transactions 参数，重启数据库生效
show max_prepared_transactions;
alter system set max_prepared_transactions = 10;
pg_ctl restart
show max_prepared_transactions;

--创建测试表
create table testtab01(id int primary key);

--开启事务，插入数据并进行第一阶段提交
postgres=# begin;
BEGIN
postgres=*# insert into testtab01 values(1);
INSERT 0 1
postgres=*# PREPARE TRANSACTION 'osdba_global_trans_0001';
PREPARE TRANSACTION
postgres=# table testtab01;
 id
----
(0 rows)

--重启数据库后进行第二阶段提交
[postgres@localhost ~]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-07-24 17:01:33.206 CST [10882] LOG:  00000: redirecting log output to logging collector process
2023-07-24 17:01:33.206 CST [10882] HINT:  Future log output will appear in directory "logs".
2023-07-24 17:01:33.206 CST [10882] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@localhost ~]$ psql
psql (13.6)
Type "help" for help.

postgres=# table testtab01;
 id
----
(0 rows)

postgres=# COMMIT PREPARED 'osdba_global_trans_0001';
COMMIT PREPARED
postgres=# table testtab01;
 id
----
  1
(1 row)