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）BEGIN;
2) BEGIN TRANSACTION;
3) BEGIN WORK;
- 事务结束，有两种情况：
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 仍然存在；最后提交事务。