PostgreSQL 变态需求实现 - 堵塞式读, 不堵塞写 - 串行读,并行写 - advisory lock

作者

digoal

日期

2020-05-17

标签

PostgreSQL , 并行写 , 串行读 , advisory lock , 堵塞式读 , 不堵塞写

背景

一个比较变态的需求, 允许并行写, 不允许并行读.

实现方式比较简单, 使用advisory lock.

create table a (id int primary key, info text); insert into a values (1,'test');

insert into a values ....; -- 常规用法就可以, 没什么特别, 只要不是同一行都不冲突

同一行记录的串行查询有两种实现方式,

1、不堵塞读、不等其他查询结束, 但是也不返回未拿到共享锁的读结果.

select * from a where id=? and pg_try_advisory_xact_lock(?);

例子:

-- 会话a begin; select * from a where id=1 and pg_try_advisory_xact_lock(1); ... id | info ----+------ 1 | test (1 row)

-- 会话b select * from a where id=1 and pg_try_advisory_xact_lock(1); -- 没有返回记录 ,因为拿不到这个锁 id | info ----+------ (0 rows)

-- 会话a end; -- 结束事务, 释放锁, 也可以调用pg_advisory_unlock释放锁.

-- 会话b select * from a where id=1 and pg_try_advisory_xact_lock(1); -- 有返回记录 id | info ----+------ 1 | test (1 row)

2、堵塞读, 等其他读结束.

begin; select pg_advisory_xact_lock(?); select * from a where id=? ;

例子:

-- 会话a begin; select pg_advisory_xact_lock(1); select * from a where id=1; ... id | info ----+------ 1 | test (1 row)

-- 会话b postgres=> begin; BEGIN postgres=> select pg_advisory_xact_lock(1); -- 等待中

-- 会话a end; -- 结束事务, 释放锁, 也可以调用pg_advisory_unlock释放锁.

```
-- 会话b
-- 可以拿到锁
pg_advisory_xact_lock

(1 row)
postgres=> select * from a where id=1;
id | info
----+------
1 | test
(1 row)
```

3、如果你觉得麻烦, 方法2可以写cte语法, 一句搞定, 不需要开事务的方式先加锁再查询, 一句搞定..

with locks as (select 1 as locks from pg_advisory_xact_lock(1)) select a.* from a,locks where id=1;

参考

postgres=> \df *.*advisory* List of functions Schema | Name | Result data type | Argument data types | Type ------------+----------------------------------+------------------+---------------------+------ pg_catalog | pg_advisory_lock | void | bigint | func pg_catalog | pg_advisory_lock | void | integer, integer | func pg_catalog | pg_advisory_lock_shared | void | bigint | func pg_catalog | pg_advisory_lock_shared | void | integer, integer | func pg_catalog | pg_advisory_unlock | boolean | bigint | func pg_catalog | pg_advisory_unlock | boolean | integer, integer | func pg_catalog | pg_advisory_unlock_all | void | | func pg_catalog | pg_advisory_unlock_shared | boolean | bigint | func pg_catalog | pg_advisory_unlock_shared | boolean | integer, integer | func pg_catalog | pg_advisory_xact_lock | void | bigint | func pg_catalog | pg_advisory_xact_lock | void | integer, integer | func pg_catalog | pg_advisory_xact_lock_shared | void | bigint | func pg_catalog | pg_advisory_xact_lock_shared | void | integer, integer | func pg_catalog | pg_try_advisory_lock | boolean | bigint | func pg_catalog | pg_try_advisory_lock | boolean | integer, integer | func pg_catalog | pg_try_advisory_lock_shared | boolean | bigint | func pg_catalog | pg_try_advisory_lock_shared | boolean | integer, integer | func pg_catalog | pg_try_advisory_xact_lock | boolean | bigint | func pg_catalog | pg_try_advisory_xact_lock | boolean | integer, integer | func pg_catalog | pg_try_advisory_xact_lock_shared | boolean | bigint | func pg_catalog | pg_try_advisory_xact_lock_shared | boolean | integer, integer | func (21 rows)

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.