PostgreSQL锁机制，后悔没有早点遇见你

呆呆的私房菜 2024-08-04

225

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

阅读本文可以了解到什么是数据库锁、锁的类型数据库表锁、死锁、行锁等内容，读者亦可以通过相关的案例来辅助理解PostgreSQL锁机制。

数据库锁概述

锁是为了在高并发环境下保证数据库数据的一致性，在多个用户同时访问数据库的时候，如果不对并发操作加以控制，就有可能读取和存储不正确的数据，破坏数据库的一致性。
PostgreSQL实现并发控制的基本方法是使用锁来控制临界区互斥访问。后台进程访问磁盘文件时，需要获得锁，获得成功后才能进入临界区执行磁盘读写访问，访问完成后退出临界区并释放锁，否则进程睡眠之道别的后台进程唤醒。

锁类型

PostgreSQL数据库定义了3种锁，分别是spinlock、lwlock和regularLock。

1. spinlock是最底层的锁，使用互斥信号量实现。特点是封锁时间短，没有等待队列和死锁检测机制，事务结束时不能自动释放。
2. lwlock主要提供共享存储器的互斥访问。lwlock有两种模式，一种排他模式，一种共享模式，它是利用spinlock实现的。特点是有等待队列、没有死锁检测和能自动释放锁。
3. 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)

显式加锁命令

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不等待任何锁冲突被释放。如果被指定的锁不能立即获得，那么事务就会终止。

锁的查看

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;

关于死锁

死锁是两个或两个以上的事务在执行过程中相互持有对象期待的锁、导致事务都将无法进行。
PostgreSQL能够自动检测死锁，会退出其中一个事务，从而允许其他事务完成。
死锁发生的必要条件：

1. 互斥条件；
2. 请求保持条件；
3. 不剥夺条件
4. 环路等待条件。

# 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的独占排他锁。

针对死锁问题，我们有什么好的方式去预防呢？

1. 设置限制条件，去破坏死锁的四个必要条件中的一个或多个，来预防发生死锁；
2. 数据库可以自动检测死锁，根据超时参数设定选择在何时回滚一个事务，解除死锁；
3. 开发测试环境中，需要关闭死锁超时，发现死锁，优化程序逻辑，从而避免死锁。

本文内容就到这啦，阅读完本篇，相信你也对PostgreSQL的锁机制有了更深的理解了吧！我们下篇再见！

点击上方公众号，关注我吧！

postgresql 数据库事务 mysql事务数据库死锁 oracle死锁

文章转载自呆呆的私房菜，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL锁机制，后悔没有早点遇见你

评论