事务、并发和锁

什么是 ACID

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

---

DDL 事务

在 PostgreSQL 中，与其他数据库最大的不同是，大多数 DDL 也是可以包含在一个事务中的，而且也是可以回滚的。

适合场景：

• PostgreSQL 作为 Sharding 分布式数据系统的底层数据库。
• Sharding 中，常常需要在多个节点中建相同的表，此时可以考虑把建表语句放在同一个事务中，这样就可以在各个节点上先启动一个事务，然后再执行建表语句，保证整个集群的一致性。

---

事务的使用方法

在 psql 的默认配置下，自动提交功能 AUTOCOMMIT 是打开的。可以通过设置 psql 中的内置变量 AUTOCOMMIT 来关闭自动提交功能：

\set AUTOCOMMIT off;
BEGIN;

---

SAVEPOINT（保存点）

PostgreSQL 支持保存点（SAVEPOINT）的功能，在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。

案例：

BEGIN;
INSERT INTO testtab01 VALUES (1);
INSERT INTO testtab01 VALUES (2);
SAVEPOINT my_savepoint01;
INSERT INTO testtab02 VALUES (1);
INSERT INTO testtab02 VALUES (1);  -- 假设这里违反唯一约束，报错

如果发生错误，只需回滚到上一个保存点：

ROLLBACK TO my_savepoint01;

---

事务隔离级别

数据库的事务隔离级别有以下 4 种：

1. READ UNCOMMITTED：读未提交
2. READ COMMITTED：读已提交
3. REPEATABLE READ：可重复读
4. SERIALIZABLE：串行化

对于并发事务，我们不希望发生不一致的情况，这类情况的级别从高到低排序如下：

• 脏读
• 不可重复读
• 幻读

PostgreSQL 中只支持三种隔离级别：

• 读已提交
• 可重复读
• 串行化

需要说明：

• 在 PostgreSQL 的一个事务中不可能读到其他事务中未提交的数据。
• 在选择可重复读级别的时候，实际上仍是可串行化，所以实际的隔离级别可能比你选择的更加严格。

---

两阶段提交

多台数据库之间的原子性就需要通过两阶段提交来实现，两阶段提交是实现分布式事务的关键。PostgreSQL 数据库支持两阶段提交协议。

两阶段提交协议的步骤

1. 应用程序调用事务协调器
2. 通知准备提交事务（PostgreSQL 中一般是调用 PREPARE TRANSACTION 命令）
3. 接收到 PREPARE TRANSACTION 命令，保证将自己置于准备提交中的状态
4. 事务协调器接收所有数据库的响应
5. 如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令 ROLLBACK PREPARED 给各台数据库

PostgreSQL 持久化事务案例

前提：max_prepared_transactions 必须设置为一个大于 0 的数字。

-- 设置 max_prepared_transactions（需在 postgresql.conf 中配置）
SET max_prepared_transactions = 10;  -- 如果配置文件中未设置，此处会报错

-- 创建表
CREATE TABLE testtab01 (id INT PRIMARY KEY);

-- 启动事务
BEGIN;
INSERT INTO testtab01 VALUES (1);

-- 准备事务
PREPARE TRANSACTION 'global_trans_1';  -- 全局事务 ID

-- 模拟数据库重启
-- pg_ctl stop -D $PGDATA
-- pg_ctl start -D $PGDATA

-- 连接数据库提交两阶段事务
COMMIT PREPARED 'global_trans_1';

-- 查询数据
SELECT * FROM testtab01;

---

表级锁

表级锁只有 SHARE 和 EXCLUSIVE 两种，表级锁共有八种模式，其存在于 PostgreSQL 的共享内存中，可以通过 pg_locks 系统视图查询。

锁模式	解释
ACCESS SHARE	SELECT 命令在被引用的表上会获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获取这种模式。
ROW SHARE	SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上会获得一个这种模式的锁。
ROW EXCLUSIVE	UPDATE、DELETE 和 INSERT 命令在目标表上会获得一个这种模式的锁。
SHARE UPDATE EXCLUSIVE	ALTER INDEX 和 ALTER TABLE 命令的变体会获得。这种模式保护一个表不受并发模式改变的影响。
SHARE	CREATE INDEX（不带 CONCURRENTLY）命令会获得。这种模式保护一个表不受并发数据改变的影响。
SHARE ROW EXCLUSIVE	CREATE TRIGGER 命令和某些形式的 ALTER TABLE 命令会获得。
EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY 命令会获得。

表锁特点

• 锁粒度非常大。
• 通过类似于 MySQL 意向锁的方式，PostgreSQL 中也是这样实现的，如 ROW SHARE、ROW EXCLUSIVE 这两个锁。

---

行级锁

行级锁模式比较简单，只有两种：

• 共享锁
• 排它锁

在 PostgreSQL 中由于有多版本的实现，所以实际读取行数据时，并不会在行上执行任何锁。

---

表级锁命令 LOCK TABLE

LOCK [TABLE] [ONLY] name [,...] [IN lockmode MODE] [NOWAIT];

• NOWAIT：表示不等待锁，如果无法立即获取锁则报错。

---

行级锁命令

SELECT ... FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [ ...];

• NOWAIT 关键字与在 LOCK TABLE 中相同，加了 NOWAIT 后，如果无法获得锁则直接报错，而不会一直等待。

---

锁的查看

pg_locks 可以查询到当前锁的信息，比如：哪个事务被哪个事务阻塞了，若执行一条 SQL 语句时阻塞住了，需要查询为什么阻塞，是谁阻塞住的。

名字	类型	引用	描述
locktype	text		可锁定对象的类型：relation, extend, page, tuple, transactionid, object, userlock
database	oid	pg_database.oid	对象所在的数据库的 OID
relation	oid	pg_class.oid	关系的 OID
page	integer		关系内部的页面编号
tuple	smallint		页面里面的元组编号
transactionid	xid		事务的 ID
classid	oid	pg_class.oid	包含该对象的系统表的 OID
objid	oid		对象在其系统表内的 OID
objsubid	smallint		对于表的一个字段，这是字段编号
transaction	xid		持有此锁或者在等待此锁的事务的 ID
pid	integer		持有或者等待这个锁的服务器进程的进程 ID
mode	text		这个进程持有的或者是期望的锁模式
granted	boolean		如果持有锁，为真；如果等待锁，为假

---

表锁查询案例

Session 1：

CREATE TABLE testtab02 (id INT PRIMARY KEY);
INSERT INTO testtab02 VALUES (1);
BEGIN;
LOCK TABLE testtab02;

查询锁：

SELECT locktype, relation::regclass AS rel, virtualxid AS vxid, transactionid AS xid,
       virtualtransaction AS vxid2, pid, mode, granted
FROM pg_locks;

结果解释：

• 其中一行显示的是事务在自己的 virtualxid 上加的 ExclusiveLock 锁，这是必定会加上的。
• 另一行才是我们实际在表上加的锁 AccessExclusiveLock。

Session 2：

BEGIN;
LOCK TABLE testtab02;  -- 将被阻塞

查询锁：

SELECT locktype, relation::regclass AS rel, virtualxid AS vxid, transactionid AS xid,
       virtualtransaction AS vxid2, pid, mode, granted
FROM pg_locks;

---

行锁查询案例

Session 1：

SELECT * FROM testtab02 WHERE id = 1;
BEGIN;
SELECT * FROM testtab02 WHERE id = 1 FOR UPDATE;

第三个窗口查询锁：

SELECT locktype, relation::regclass AS rel, virtualxid AS vxid, transactionid AS xid,
       virtualtransaction AS vxid2, pid, mode, granted
FROM pg_locks;

问题 1：为什么没有发现行锁？

答案：实际上 pg_locks 并不能显示出每个行锁的信息，原因也很简单，行锁信息并不会记录到共享内存中。

问题 2：在 pg_locks 中没有行锁信息，如何知道一个进程被另一个进程的行锁阻塞了呢？

Session 2：

BEGIN;
SELECT * FROM testtab02 WHERE id = 1 FOR UPDATE;  -- 将被阻塞

想查看哪个进程被阻塞住了，只需要查看 granted 字段值为 False 的 PID。

SELECT pg_backend_pid();  -- 查看当前会话的 PID

通过 pg_locks 中 granted = false 的记录可以找到被阻塞的进程。

---