事务、并发和锁
什么是 ACID
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
DDL 事务
在 PostgreSQL 中，与其他数据库最大的不同是，大多数 DDL 也是可以包含在一个事务中的，而且也是可以回滚的。
适合场景：
- PostgreSQL 作为 Sharding 分布式数据系统的底层数据库。
- Sharding 中，常常需要在多个节点中建相同的表，此时可以考虑把建表语句放在同一个事务中，这样就可以在各个节点上先启动一个事务，然后再执行建表语句，保证整个集群的一致性。
事务的使用方法
在 psql 的默认配置下，自动提交功能AUTOCOMMIT是打开的。可以通过设置 psql 中的内置变量AUTOCOMMIT来关闭自动提交功能：
\setAUTOCOMMIToff;BEGIN;SAVEPOINT（保存点）
PostgreSQL 支持保存点（SAVEPOINT）的功能，在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。
案例：
BEGIN;INSERTINTOtesttab01VALUES(1);INSERTINTOtesttab01VALUES(2);SAVEPOINTmy_savepoint01;INSERTINTOtesttab02VALUES(1);INSERTINTOtesttab02VALUES(1);-- 假设这里违反唯一约束，报错如果发生错误，只需回滚到上一个保存点：
ROLLBACKTOmy_savepoint01;事务隔离级别
数据库的事务隔离级别有以下 4 种：
- READ UNCOMMITTED：读未提交
- READ COMMITTED：读已提交
- REPEATABLE READ：可重复读
- SERIALIZABLE：串行化
对于并发事务，我们不希望发生不一致的情况，这类情况的级别从高到低排序如下：
- 脏读
- 不可重复读
- 幻读
PostgreSQL 中只支持三种隔离级别：
- 读已提交
- 可重复读
- 串行化
需要说明：
- 在 PostgreSQL 的一个事务中不可能读到其他事务中未提交的数据。
- 在选择可重复读级别的时候，实际上仍是可串行化，所以实际的隔离级别可能比你选择的更加严格。
两阶段提交
多台数据库之间的原子性就需要通过两阶段提交来实现，两阶段提交是实现分布式事务的关键。PostgreSQL 数据库支持两阶段提交协议。
两阶段提交协议的步骤
- 应用程序调用事务协调器
- 通知准备提交事务（PostgreSQL 中一般是调用
PREPARE TRANSACTION命令） - 接收到
PREPARE TRANSACTION命令，保证将自己置于准备提交中的状态 - 事务协调器接收所有数据库的响应
- 如果任何一个数据库在第一阶段返回失败，则事务协调器将会发一个回滚命令
ROLLBACK PREPARED给各台数据库
PostgreSQL 持久化事务案例
前提：max_prepared_transactions必须设置为一个大于 0 的数字。
-- 设置 max_prepared_transactions（需在 postgresql.conf 中配置）SETmax_prepared_transactions=10;-- 如果配置文件中未设置，此处会报错-- 创建表CREATETABLEtesttab01(idINTPRIMARYKEY);-- 启动事务BEGIN;INSERTINTOtesttab01VALUES(1);-- 准备事务PREPARETRANSACTION'global_trans_1';-- 全局事务 ID-- 模拟数据库重启-- pg_ctl stop -D $PGDATA-- pg_ctl start -D $PGDATA-- 连接数据库提交两阶段事务COMMITPREPARED'global_trans_1';-- 查询数据SELECT*FROMtesttab01;表级锁
表级锁只有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[,...][INlockmodeMODE][NOWAIT];NOWAIT：表示不等待锁，如果无法立即获取锁则报错。
行级锁命令
SELECT...FOR{UPDATE|SHARE}[OFtable_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：
CREATETABLEtesttab02(idINTPRIMARYKEY);INSERTINTOtesttab02VALUES(1);BEGIN;LOCKTABLEtesttab02;查询锁：
SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;结果解释：
- 其中一行显示的是事务在自己的
virtualxid上加的ExclusiveLock锁，这是必定会加上的。 - 另一行才是我们实际在表上加的锁
AccessExclusiveLock。
Session 2：
BEGIN;LOCKTABLEtesttab02;-- 将被阻塞查询锁：
SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;行锁查询案例
Session 1：
SELECT*FROMtesttab02WHEREid=1;BEGIN;SELECT*FROMtesttab02WHEREid=1FORUPDATE;第三个窗口查询锁：
SELECTlocktype,relation::regclassASrel,virtualxidASvxid,transactionidASxid,virtualtransactionASvxid2,pid,mode,grantedFROMpg_locks;问题 1：为什么没有发现行锁？
答案：实际上pg_locks并不能显示出每个行锁的信息，原因也很简单，行锁信息并不会记录到共享内存中。
问题 2：在pg_locks中没有行锁信息，如何知道一个进程被另一个进程的行锁阻塞了呢？
Session 2：
BEGIN;SELECT*FROMtesttab02WHEREid=1FORUPDATE;-- 将被阻塞想查看哪个进程被阻塞住了，只需要查看granted字段值为False的 PID。
SELECTpg_backend_pid();-- 查看当前会话的 PID通过pg_locks中granted = false的记录可以找到被阻塞的进程。
