进阶数据库系列（八）：PostgreSQL 锁机制

前面介绍了 PostgreSQL 常用管理命令、访问控制与认证、语法、数据类型和运算符、常用函数 相关的知识点，今天我将详细的为大家介绍 PostgreSQL 锁操作相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

锁存在的意义

在了解 PostgreSQL 锁之前，我们需要了解锁存在的意义是啥？

当多个会话同时访问数据库的同一数据时，理想状态是为所有会话提供高效的访问，同时还要维护严格的数据一致性。那数据一致性通过什么来维护呢，就是通过 MVCC（多版本并发控制） 。

MVCC（多版本并发控制）：每个SQL语句看到的都只是当前事务开始的数据快照，而不管底层数据的当前状态。

这样可以保护语句不会看到在相同数据上由其他连接执行更新的并发事务造成的不一致数据，为每一个数据库会话提供事务隔离。MVCC 避免了传统的数据库系统的锁定方法，将通过锁争夺最小化的方法来达到多会话并发访问时的性能最大化目的。

PostgreSQL 锁机制浅析

锁机制在 PostgreSQL 里非常重要 (对于其他现代的 RDBMS 也是如此)。对于数据库应用程序开发者（特别是那些涉及到高并发代码的程序员），需要对锁非常熟悉。对于某些问题，锁需要被重点关注与检查。大部分情况，这些问题跟死锁或者数据不一致有关系，基本上都是由于对 Postgres 的锁机制不太了解导致的。虽然锁机制在 Postgres 内部很重要，但是文档缺非常缺乏，有时甚至还是错误的，与文档所指出的结果不一致。我会告诉你精通 Postgres 的锁机制需要知道的一切，要知道对锁了解的越多，解决与锁相关的问题就会越快。

PostgreSQL 提供了多种锁模式用于控制对表中数据的并发访问，其中最主要的是表级锁与行级锁，除此之外还有页级锁、咨询锁等等，接下来主要介绍表级锁与行级锁。

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

表级锁

表级锁通常会在执行各种命令执行时自动获取，或者通过在事务中使用 LOCK 语句显示获取。

每种锁都有自己的冲突集合。

表级锁：两个事务在同一时刻不能在同一个表上持有互相冲突的锁，但是可以同时持有不冲突的锁。

表级锁共有八种模式，其存在于PG的共享内存中，可以通过 pg_locks 系统视图查阅。

ACCESS SHARE 访问共享

SELECT 命令在被引用的表上会获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获取这种表模式。

ROW SHARE 行共享

SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上会获得一个这种模式的锁。（加上在被引用但没有选择 FOR UPDATE / FOR SHARE 的任何其他表上的 ACCESS SHARE 锁。）

ROW EXCLUSIVE 行独占

UPDATE、DELETE 和 INSERT 命令在目标表上会获得一个这种模式的锁。（加上在任何其他被引用表上的 ACCESS SHARE锁。）通常，这种锁模式将被任何修改表中数据的命令取得。

SHARE UPDATE EXCLUSIVE 共享更新独占

VACUUM（不带FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、CREATE STATISTICS 命令以及某些 ALTER INDEX 和 ALTER TABLE 命令的变体会获得。这种模式保护一个表不受并发模式改变和 VACUUM 运行的影响。

SHARE 共享

CREATE INDEX（不带CONCURRENTLY） 命令会获得。

这种模式保护一个表不受并发数据改变的影响。

SHARE ROW EXCLUSIVE 共享行独占

CREATE TRIGGER 命令和某些形式的 ALTER TABLE 命令会获得。

这种模式保护一个表不受并发数据修改所影响，并且是自排他的，这样在同一个时刻只能有一个会话持有它。

EXCLUSIVE 排他

REFRESH METERIALIZED VIEW CONCURRENTLY 命令会获得。

这种模式只允许并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。

ACCESS EXCLUSIVE 访问独占

ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令会获得。很多形式的 ALTER INDEX 和 ALTER TABLE 也在这个层面上获得锁。这也是未显式指定模式的 LOCK TABLE 命令的默认锁模式。

这种模式与所有模式的锁冲突。这种模式保持者是访问该表的唯一事务。

表级锁模式冲突表

（注：X表示冲突。）

更多关于大数据 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。怎么去看上面这个图呢？我们下面举两个例子说明一下：

示例一

当一个会话运行了 update 语句，此时会话表上的锁模式为 ROW EXCLUSIVE，从上图我们可以看出 ROW EXCLUSIVE 与 SHARE、SHARE ROW、ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。

也就是说在这个会话未提交事务释放锁之前，我们不能做申请 SHARE、SHARE ROW、ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式相关的操作，例如 CREATE INDEX（不带CONCURRENTLY）、ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）等。

我们先创建一个测试数据库：

# 创建测试用户
create user root password 'root';
# 创建测试数据库
create database mydb owner root encoding UTF8;
# 创建和测试用户同名Schema
create schema AUTHORIZATION CURRENT_USER;

我们创建一张测试表 t_user，并插入一条测试数据：

create table "t_user" (
 "id" bigserial not null,
 "username" varchar (64) not null,
 constraint t_user_pk primary key (id)
);
insert into t_user values(1, 'ACGkaka');

会话一： 执行 update 语句。

begin;
update t_user set username='ACGkaka1' where id=1;

会话二： 执行 alter table 语句，这时会处于等待状态。

alter table t_user add dept_no int