LightDB-PostgreSQL事务特性与使用示例

概述

本篇介绍 PostgreSQL 中的数据库事务概念和 ACID 属性，演示了如何使用事务控制语句（TCL）对事务进行处理，包括BEGIN、COMMIT、ROLLBACK以及SAVEPOINT语句。

事务的特性

数据库中的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也就是 ACID 属性：

• 原子性 ：保证事务中的操作要么全部成功，要么全部失败，不会只成功一部分。比如从 A 账户转出 1000 元到 B 账户，如果从 A 账户减去 1000 元成功执行，但是没有往 B 账户增加 1000 元，意味着客户将会损失 1000 元。用数据库中的术语来说，这种情况导致了数据库的不一致性。
• 一致性 ：确保了数据修改的有效性，并且遵循一定的业务规则；例如，上面的银行转账事务中如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致（此时需要回滚已经执行的扣款操作）。另外，数据库还必须保证满足完整性约束，比如账户扣款之后不能出现余额为负数（可以在余额字段上添加检查约束）。
• 隔离性 ：决定了并发事务之间的可见性和相互影响程度。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目；如果多人同时向账户 B 转账，结果也应该保持一致性，就像依次转账的结果一样。SQL 标准定义了 4 种不同的隔离级别。
• 持久性 ：确保已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。对于 PostgreSQL 而言，使用的是预写式日志（WAL）的机制实现事务的持久性。

事务的用法

1. 事务开始，有三种写法：

1）BEGIN;
2) BEGIN TRANSACTION;
3) BEGIN WORK;

2. 事务结束，有两种情况：

1）COMMIT; --确认事务，等价于COMMIT WORK或者COMMIT TRANSACTION
2）ROLLBACK; --回滚事务

事务控制语句

执行以下命令创建示例表：

CREATE TABLE accounts(
  id serial PRIMARY KEY, 
  user_name varchar(50), 
  balance numeric(10,4)
);

ALTER TABLE accounts ADD CONSTRAINT bal_check CHECK(balance >= 0);

accounts 是一个简化的账户表，主要包含用户名和余额信息。我们为该表插入一条记录：

insert into accounts(user_name, balance) values ('UserA', 6000);

select * from accounts;

id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|

1 row(s) fetched.

默认情况下，PostgreSQL 自动为以上INSERT语句开始一个事务，执行插入操作之后自动提交该事务。
不过，我们也可以手动控制事务的开始和提交。例如：

begin;

insert into accounts(user_name, balance) values ('UserB', 0);

select * from accounts;

id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|

2 row(s) fetched.

1 row(s) modified.

其中，BEGIN用于开始一个新的事务，然后插入一条记录，查询显示了两条记录。

如果此时打开另一个数据库连接，查询 accounts 表只能看到一条记录。因为上面的事务还没有提交，事务的隔离性使得我们无法看到其他事务未提交的修改。

select * from accounts;
id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|

1 row(s) fetched.

我们将上面的第一个会话中的事务进行提交：

commit;

COMMIT用于提交事务，此时，其他事务就能看到用户 UserB 的记录了。

事务除了可以提交之外，也可以被回滚。我们演示一下如何回滚事务：

begin;

insert into accounts(user_name, balance) values ('UserC', 2000);

select * from accounts;

id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|
 3|UserC    |2000.0000|

3 row(s) fetched.

此时可以回滚该事务：

rollback;

select * from accounts;

id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|

2 row(s) fetched.

ROLLBACK用于回滚当前事务，也可以使用ROLLBACK WORK或者ROLLBACK TRANSACTION。回滚之后，事务中的数据修改都会被撤销，账户 UserC 并没有创建成功。

还有一个与事务控制相关的语句：SAVEPOINT，用于在事务中定义保存点。例如：

begin;

insert into accounts(user_name, balance) values ('UserC', 2000);

savepoint sv1;

insert into accounts(user_name, balance) values ('UserD', 0);

rollback to sv1;

commit;

select * from accounts;
id|user_name|balance  |
--+---------+---------+
 1|UserA    |6000.0000|
 2|UserB    |   0.0000|
 4|UserC    |2000.0000|

3 row(s) fetched.

开始一个事务之后，先插入账户 UserC，然后定义了保存点 sv1；接着插入账户 UserD，然后回滚到保存点 sv1；此时账户 UserD 被撤销，账户 UserC 仍然存在；最后提交事务。