PostgreSQL Advisory Lock 咨询锁

PostgreSQL About 1,766 words

概述

Advisory Lock咨询锁分为 Session 级别事务级别两种级别的锁,以及共享排他两种模式。

应用

Session级别的咨询锁应用场景有限,需要手动释放锁(显示调用unlock方法解锁)。

事务级别的咨询锁必须在事务中使用,随着事务的提交或回滚,自动释放锁。

场景

事务级别的咨询锁:防止短时间多次执行该事务。

事务级别咨询锁

排他锁

尝试获取,立即返回。

成功为true,失败为false

begin;
select pg_try_advisory_xact_lock(100);
commit;

等待获取,直到其他事务释放锁,获取到锁。

begin;
select pg_advisory_xact_lock(100);
commit;

共享锁

其他事务中调用了该方法,也能获得锁。

(其他事务提交或回滚,释放该事务对应的共享锁)

begin;
select pg_try_advisory_xact_lock_shared(100);
commit;

查看锁

查看pg_locks表。

排他锁

locktypeadvisorymodeExclusiveLock

lite_note=# select locktype, objid, pid, mode, pg_backend_pid() from pg_locks;
  locktype  | objid |  pid  |      mode       | pg_backend_pid
------------+-------+-------+-----------------+----------------
 relation   |       | 20043 | AccessShareLock |          20043
 virtualxid |       | 20043 | ExclusiveLock   |          20043
 advisory   |   100 | 20043 | ExclusiveLock   |          20043
(5 rows)

共享锁

locktypeadvisorymodeShareLock

lite_note=# select locktype, objid, pid, mode, pg_backend_pid() from pg_locks;
  locktype  | objid |  pid  |      mode       | pg_backend_pid
------------+-------+-------+-----------------+----------------
 relation   |       | 20043 | AccessShareLock |          20043
 virtualxid |       | 20043 | ExclusiveLock   |          20043
 virtualxid |       | 20027 | ExclusiveLock   |          20043
 advisory   |   100 | 20043 | ShareLock       |          20043
 advisory   |   100 | 20027 | ShareLock       |          20043
(5 rows)

架构

咨询锁存储在共享内存池中,它的大小由max_locks_per_transactionmax_connections决定。

文档

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

https://www.postgresql.org/docs/current/view-pg-locks.html

Views: 842 · Posted: 2023-10-31

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh