PostgreSQL|PostgreSQL 咨询锁advisory lock使用详解数据库|postgresql|sql

PostgreSQL从8.2版本就开始支持advisory lock这一功能。那么什么是advisory lock呢？官方文档的解释是：
PostgreSQL提供了一种方法创建由应用定义其含义的锁。这种锁被称为咨询锁，因为系统并不强迫其使用——而是由应用来保证其正确的使用。
advisory lock分为会话级别和事务级别两种(9.1版本加入事务级别)。事务级别的advisory lock不能显性的释放。advisory lock和系统的MVCC不是一个概念，基本上完全不相干。锁的数量由 max_locks_per_transaction and max_connections 决定。
advisory lock相关的函数：

文章图片

advisory lock 不受事务的影响，在同一个会话中 advisory lock 可以多次lock，但是必须与unlock成对使用。
可能看了上面的解释你还是会有些云里雾里的感觉，那么我们就看看advisory lock究竟能用在哪些场景下吧！
–场景1 两个会话，同时对同一个ID值的记录执行删除后插入，有一个会话会出现删除0，插入失败的现象。
建表：

bill@bill=>CREATE TABLE test (id INT PRIMARY KEY); CREATE TABLE bill@bill=>INSERT INTO TEST VALUES (1); INSERT 0 1 bill@bill=>INSERT INTO TEST VALUES (2); INSERT 0 1

分别在两个会话中执行下列语句：

begin; 1:DELETE FROM test WHERE id=1; 2:INSERT INTO test VALUES (1); 3:COMMIT;

有点经验的人可能立刻就会发现：在会话二中会因为会话一产生锁等待，直到会话一结束就可以执行自己会话中的语句了。事实真的如此吗？
会话一：

bill@bill=>begin; BEGIN bill@bill=>DELETE FROM test WHERE id=1; DELETE 1 bill@bill=>INSERT INTO test VALUES (1); INSERT 0 1 bill@bill=>commit; COMMIT

会话二：

bill@bill=>begin; BEGIN bill@bill=>DELETE FROM test WHERE id=1; DELETE 0 bill@bill=>INSERT INTO test VALUES (1); ERROR:duplicate key value violates unique constraint "test_pkey" DETAIL:Key (id)=(1) already exists.

可以看到，会话二在会话一提交前确实会一直等待，但是等会话一提交完，会话二中delete语句删除记录数为0，insert插入会报错！那么我们该怎么解决这一问题呢？这种情况下我们就可以使用advisory lock。
会话一：

bill@bill=>begin; BEGIN bill@bill=>select pg_try_advisory_xact_lock(1); pg_try_advisory_xact_lock --------------------------- t (1 row)bill@bill=>delete from test where id =1; DELETE 1 bill@bill=>insert into test values(1); INSERT 0 1 bill@bill=>end; COMMIT

会话二：

bill@bill=>begin; BEGIN bill@bill=>select pg_try_advisory_xact_lock(1); --会话一不提交则返回f pg_try_advisory_xact_lock --------------------------- f (1 row)bill@bill=>select pg_try_advisory_xact_lock(1); --会话一提交后返回t pg_try_advisory_xact_lock --------------------------- t (1 row)bill@bill=>DELETE FROM test WHERE id=1; DELETE 1 bill@bill=>INSERT INTO test VALUES (1); INSERT 0 1 bill@bill=>end; COMMIT

–场景2: 使用advisory lock消除行锁冲突。
通常在数据库中最小粒度的锁是行锁，当一个事务正在更新某条记录时，另一个事务如果要更新同一条记录（或者申请这一条记录的锁），则必须等待锁释放。
通常持锁的时间需要保持到事务结束，也就是说，如果一个长事务持有了某条记录的锁，其他会话要持有这条记录的锁，可能要等很久。
那么对大表进行全表或者大部分数据的直接更新必然会很慢。
例如：

bill@bill=>create unlogged table t1(id int primary key, info int[]); CREATE TABLE bill@bill=>insert into t1 select generate_series(1,10000), (select array_agg(id) from generate_series(1,100000) t(id)); INSERT 0 10000 bill@bill=>\dt+ t1 List of relations Schema | Name | Type| Owner |Size| Description --------+------+-------+-------+---------+------------- bill| t1| table | bill| 3961 MB | (1 row)

更新：
需要87秒。

bill@bill=>begin; BEGIN Time: 0.146 ms bill@bill=>update t1 set info=array_append(info,1); UPDATE 10000 Time: 87225.118 ms (01:27.225) bill@bill=>rollback ; ROLLBACK Time: 0.298 ms

使用advisory lock来更新:
使用扫描式的获取advisory lock，保证不会重复获取即可。

create or replace function update() returns void as $$ declare v_id int; begin for v_id in select id from t1-- 扫描式 loop if pg_try_advisory_xact_lock(v_id) then -- 获取到ID的LOCK才会实施更新，否则继续扫描 update t1 set info=array_append(info,1) where id=v_id; end if; end loop; end; $$ language plpgsql strict;

使用32个并行度测试：

vi test.sql select update(); pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -t 1

耗时5s。

pg12@oracle-> pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -t 1 transaction type: ./test.sql scaling factor: 1 query mode: prepared number of clients: 32 number of threads: 32 number of transactions per client: 1 number of transactions actually processed: 32/32 latency average = 5231.399 ms tps = 6.116910 (including connections establishing) tps = 6.135842 (excluding connections establishing) statement latencies in milliseconds: 5205.401select update();

总结： 【PostgreSQL|PostgreSQL 咨询锁advisory lock使用详解】使用advisory lock需要注意：adlock的id是库级冲突，例如lock(1)，在同一个数据库再lock(1)就会冲突。因此，如果在同一个库里面，期望对不同的表里面的数据使用同样的adlock手段，建议不同的表错开ID段，或者使用全局ID。