PostgreSQL锁机制，后悔没有早点遇见你
Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）
业务范围：数据库安装部署、日常维护、主备切换、故障处理、性能优化、技术培训等。
需要的伙伴或者商业合作请移步 公众号【呆呆的私房菜】获取联系方式。
阅读本文可以了解到什么是数据库锁、锁的类型数据库表锁、死锁、行锁等内容，读者亦可以通过相关的案例来辅助理解PostgreSQL锁机制。
01 数据库锁概述
锁是为了在高并发环境下保证数据库数据的一致性，在多个用户同时访问数据库的时候，如果不对并发操作加以控制，就有可能读取和存储不正确的数据，破坏数据库的一致性。
PostgreSQL实现并发控制的基本方法是使用锁来控制临界区互斥访问。后台进程访问磁盘文件时，需要获得锁，获得成功后才能进入临界区执行磁盘读写访问，访问完成后退出临界区并释放锁，否则进程睡眠之道别的后台进程唤醒。
02 锁类型
PostgreSQL数据库定义了3种锁，分别是spinlock、lwlock和regularLock。
- spinlock是最底层的锁，使用互斥信号量实现。特点是封锁时间短，没有等待队列和死锁检测机制，事务结束时不能自动释放。
- lwlock主要提供共享存储器的互斥访问。lwlock有两种模式，一种排他模式，一种共享模式，它是利用spinlock实现的。特点是有等待队列、没有死锁检测和能自动释放锁。
- regularlock指的是数据库管理中的锁，由lwlock实现。特点是有等待队列、有死锁检测和能自动释放锁。regularlock实际上又分为表级锁和行级锁。
一、 reguarlock表级锁模式有8种：
1. 访问共享锁：内部锁模式，进行查询时在被查询的表上获得；
2. 行共享锁：使用了select ... for update和for share命令；
3. 行排他锁：使用update \ delete \ insert命令；
4. 共享更新排他锁：使用vacuum \ analyze \ create index concurrently命令；
5. 共享锁：使用不带concurrently选项的create index语句请求；
6. 共享行排他锁：类似于排他锁；
7. 排他锁：阻塞行共享和select ... for update；
8. 访问排他锁：alter table \ drop table \ truncate \ reindex \ cluster \vacuum full；
排他模式的锁（ ShareRowExclusiveLock、ExclusiveLock、AccessExclusiveLock）表示事务执行期间组是其他任何类型锁作用于这张表；
共享模式的锁（ 非排他模式的锁）表示允许其他用户同时共享此锁，但在事务执行期间阻止排他型锁的使用。
排他模式和共享模式上的锁等可以工作在下列授权级别上：Access表示锁定这个表模式，Rows表示仅锁定单独的元组。
二、 reguarlock行级锁模式有2种：
1. 共享锁（读锁）
2. 排他锁（写锁）
由于多版本的实现，实际读取数据行的时候，并不会在行上执行任何锁；
两个事务永远不可能在相同的行上持有冲突的锁；
行级锁不影响数据查询，他们只阻塞同一行的写入者和加锁者。
下面我们通过一些案例来了解下PostgreSQL的锁。
# 创建表tt1
create table t1 (id int, c1 char(10));
# 场景1：事务执行查询时，会获得1个访问共享锁和1个意图读锁
postgres=# begin;
BEGIN
postgres=*# select * from t1;
id | c1
----+----
(0 rows)
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+-----------------+---------
relation | 13757 | 16384 | 3/31 | 733 | AccessShareLock | t
relation | 13757 | 16384 | 3/31 | 733 | SIReadLock | t
(2 rows)
postgres=*# commit;
COMMIT
# 场景2： 事务执行select ... for update的时候，会获得1个行共享锁和意图读锁
postgres=# begin;
BEGIN
postgres=*# select * from t1 for update;
id | c1
----+----
(0 rows)
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+--------------+---------
relation | 13757 | 16384 | 3/33 | 733 | RowShareLock | t
relation | 13757 | 16384 | 3/33 | 733 | SIReadLock | t
(2 rows)
postgres=*# rollback;
ROLLBACK
# 场景3： 事务执行insert\update\delelete时，会获得1个行独占锁
postgres=# begin;
BEGIN
postgres=*# insert into t1 values (1, 't1');
INSERT 0 1
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+------------------+---------
relation | 13757 | 16384 | 3/34 | 733 | RowExclusiveLock | t
(1 row)
postgres=*# commit;
COMMIT
# 场景4： 事务执行vacuum\analyze\create index concurrently时，会获得1个共享更新排他锁
postgres=# begin;
BEGIN
postgres=*# analyze t1;
ANALYZE
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+--------------------------+---------
relation | 13757 | 16384 | 3/35 | 733 | ShareUpdateExclusiveLock | t
(1 row)
postgres=*# commit;
COMMIT
# 场景5： 事务执行create index时，会获得1个共享锁
postgres=# begin;
BEGIN
postgres=*# create index idx_t1_c1 on t1 (c1);
CREATE INDEX
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+-----------+---------
relation | 13757 | 16384 | 3/36 | 733 | ShareLock | t
(1 row)
# 场景6：会话1执行for update产生排他锁，会话2执行for share产生共享锁，会获得2
# 会话1：执行update操作
postgres=# begin;
BEGIN
postgres=*# select * from t1 for update;
id | c1
----+-----
1 | tt1
(1 row)
# 会话2：执行for share操作，会话hung住
postgres=# begin;
BEGIN
postgres=*# select * from t1 where id = 1 for share;
# 会话3：查看数据库锁
postgres=# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+--------------+---------
relation | 13757 | 16384 | 3/50 | 733 | RowShareLock | t
relation | 13757 | 16384 | 4/271 | 179 | RowShareLock | t
tuple | 13757 | 16384 | 4/271 | 179 | RowShareLock | t
relation | 13757 | 16384 | 4/271 | 179 | SIReadLock | t
relation | 13757 | 16384 | 3/50 | 733 | SIReadLock | t
(5 rows)
# 场景7：会话1和会话2同时update t1表中的记录
# 会话1：
postgres=# begin;
BEGIN
postgres=*# update t1 set c1 = 'tt1' where id = 1;
UPDATE 1
# 会话2：会话会hung住
postgres=# begin;
BEGIN
postgres=*# update t1 set c1 = 'ttt1' where id = 1;
# 会话3：查看锁情况
postgres=# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+------------------+---------
relation | 13757 | 16384 | 3/52 | 733 | RowExclusiveLock | t
relation | 13757 | 16384 | 4/272 | 179 | RowExclusiveLock | t
tuple | 13757 | 16384 | 4/272 | 179 | ExclusiveLock | t
relation | 13757 | 16384 | 4/272 | 179 | SIReadLock | t
relation | 13757 | 16384 | 3/52 | 733 | SIReadLock | t
(5 rows)
# 场景8：表t1获得访问排他锁，其他访问t1表的事务都会被阻塞。
postgres=# begin;
BEGIN
postgres=*# alter table t1 add column d1 char(10);
ALTER TABLE
postgres=*# select locktype, database, relation, virtualtransaction, pid, mode, granted from pg_locks where relation = 't1'::regclass::oid;
locktype | database | relation | virtualtransaction | pid | mode | granted
----------+----------+----------+--------------------+-----+---------------------+---------
relation | 13757 | 16384 | 3/56 | 733 | AccessExclusiveLock | t
(1 row)
03 显式加锁命令
postgres=# \h lock
Command: LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
URL: https://www.postgresql.org/docs/14/sql-lock.html
注意：
1. name 表示要锁定的现有表的锁名称（可选模式限定），如果在表名之前指定 only，则仅该表被锁定，如果未指定only，则表及所有后代表（如果有）被锁定；
2. lock_mode 表示指定此锁与之冲突的锁，如果未指定锁模式，则使用最严格的access exclusive。
3. nowait 表示lock table不等待任何锁冲突被释放。如果被指定的锁不能立即获得，那么事务就会终止。
04 锁的查看
postgres=# \d pg_locks;
View "pg_catalog.pg_locks"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
locktype | text | | |
database | oid | | |
relation | oid | | |
page | integer | | |
tuple | smallint | | |
virtualxid | text | | |
transactionid | xid | | |
classid | oid | | |
objid | oid | | |
objsubid | smallint | | |
virtualtransaction | text | | |
pid | integer | | |
mode | text | | |
granted | boolean | | |
fastpath | boolean | | |
waitstart | timestamp with time zone | | |
## 查询所有正在等待锁的会话以及持有这些锁的会话
SELECT
pg_stat_activity.pid,
pg_stat_activity.query,
pg_locks.locktype,
pg_locks.mode,
pg_locks.relation::regclass,
pg_locks.transactionid,
pg_locks.virtualxid,
pg_locks.virtualtransaction,
pg_locks.granted
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_locks.granted = false;
## 查看数据库锁源
WITH sos AS (
SELECT array_cat(array_agg(pid),
array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
FROM pg_locks
WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
a.wait_event_type || ': ' || a.wait_event AS wait_event,
current_timestamp-a.state_change time_in_state,
current_timestamp-a.xact_start time_in_xact,
l.relation::regclass relname,
l.locktype, l.mode, l.page, l.tuple,
pg_blocking_pids(l.pid) blocking_pids,
(pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
a.query
FROM pg_stat_activity a
JOIN sos s on (a.pid = any(s.pids))
LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;
05 关于死锁
死锁是两个或两个以上的事务在执行过程中相互持有对象期待的锁、导致事务都将无法进行。
PostgreSQL能够自动检测死锁，会退出其中一个事务，从而允许其他事务完成。
死锁发生的必要条件：
- 互斥条件；
- 请求保持条件；
- 不剥夺条件
- 环路等待条件。
# 1. 创建表a和表b
postgres=# create table a (id int);
CREATE TABLE
postgres=# create table b (id int);
CREATE TABLE
# 2. 开启会话1：
postgres=# begin;
BEGIN
postgres=*# lock table a in exclusive mode;
LOCK TABLE
postgres=*# select pg_sleep(2);
pg_sleep
----------
(1 row)
# 3. 开启会话2：
postgres=# begin;
BEGIN
postgres=*# lock table b in exclusive mode;
LOCK TABLE
postgres=*# select pg_sleep(2);
pg_sleep
----------
(1 row)
# 4. 会话1申请表b的独占排他锁：
postgres=*# lock table b in exclusive mode;
# 此时发现语句执行失败，事务hung住。
# 5. 我们可以通过如下语句查看数据库中的锁，我们可以看到此时会话1被会话2阻塞了
postgres=# WITH sos AS (
SELECT array_cat(array_agg(pid),
array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
FROM pg_locks
WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
a.wait_event_type || ': ' || a.wait_event AS wait_event,
current_timestamp-a.state_change time_in_state,
current_timestamp-a.xact_start time_in_xact,
l.relation::regclass relname,
l.locktype, l.mode, l.page, l.tuple,
pg_blocking_pids(l.pid) blocking_pids,
(pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
a.query
FROM pg_stat_activity a
JOIN sos s on (a.pid = any(s.pids))
LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;
pid | usename | datname | state | wait_event | time_in_state | time_in_xact | relname | locktype | mode | page | tuple | blocking_pids | last_session | lock_depth | query
-----+----------+----------+---------------------+--------------------+-----------------+-----------------+---------+----------+---------------+------+-------+---------------+--------------+------------+---------------------------------
179 | postgres | postgres | idle in transaction | Client: ClientRead | 00:18:36.49402 | 00:18:52.443567 | | | | | | | | 179.0 | select pg_sleep(2);
177 | postgres | postgres | active | Lock: relation | 00:18:23.443331 | 00:19:05.027391 | b | relation | ExclusiveLock | | | {179} | 179 | 179.2 | lock table b in exclusive mode;
(2 rows)
# 6. 这里我们制造死锁条件，让会话2申请表a的独占排他锁，
postgres=*# lock table a in exclusive mode;
ERROR: deadlock detected
DETAIL: Process 179 waits for ExclusiveLock on relation 57643 of database 13757; blocked by process 177.
Process 177 waits for ExclusiveLock on relation 57646 of database 13757; blocked by process 179.
HINT: See server log for query details.
# 结论：PostgreSQL会自动进行死锁检测，出现死锁后，会话2的事务被abort掉，会话1成功获得表b的独占排他锁。
针对死锁问题，我们有什么好的方式去预防呢？
- 设置限制条件，去破坏死锁的四个必要条件中的一个或多个，来预防发生死锁；
- 数据库可以自动检测死锁，根据超时参数设定选择在何时回滚一个事务，解除死锁；
- 开发测试环境中，需要关闭死锁超时，发现死锁，优化程序逻辑，从而避免死锁。
