PostgreSQL 使用advisory lock实现行级读写堵塞

less than 1 minute read

背景

PostgreSQL的读写是不冲突的,这听起来是件好事对吧,读和写相互不干扰,可以数据库提高读写并发能力。

但是有些时候,用户也许想让读写冲突(需求:数据正在被更新或者删除时,不允许被读取)。

那么有方法能实现读写冲突吗?

PostgreSQL提供了一种锁advisory lock,可以实现读写堵塞的功能。

使用advisory lock实现行级读写堵塞

1. 创建表,注意使用一个唯一ID(用于advisory lock)

postgres=# create table ad_test(id int8 primary key, info text, crt_time timestamp);
CREATE TABLE

2. 插入测试数据

postgres=# insert into ad_test values (1,'test',now());
INSERT 0 1

3. 会话1,更新某一条记录

postgres=# begin;
BEGIN
postgres=# update ad_test set info='abc' where id=1;
UPDATE 1

4. 会话2,读这条记录

postgres=# select * from ad_test ;
 id | info |          crt_time          
----+------+----------------------------
  1 | test | 2017-05-07 15:57:42.201804
(1 row)

使用以上常规的方法,读写是不冲突的。

5. 会话1,更新这条记录的同时,使用advisory lock锁住这个ID

postgres=# begin;
BEGIN
postgres=# update ad_test set info='abc' where id=1 returning pg_try_advisory_xact_lock(id);
 pg_try_advisory_xact_lock 
---------------------------
 t
(1 row)

UPDATE 1

6. 会话2,查询这条记录时,使用advisory lock探测这条记录,如果无法加锁,返回0条记录。从而实现读写堵塞(实际上是隔离)。

postgres=# select * from ad_test where id=1 and pg_try_advisory_xact_lock(1);
 id | info | crt_time 
----+------+----------
(0 rows)

使用advisory lock,实现了读写冲突的需求(实际上是让读的会话读不到被锁的记录)。

adlock使用注意

advisory lock锁住的ID,是库级冲突的,所以使用时也需要注意哟。

advisory lock相关函数API的详细介绍

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

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

advisory lock的其他应用

1. 《PostgreSQL upsert功能(insert on conflict do)的用法》

2. 《PostgreSQL 无缝自增ID的实现 - by advisory lock》

3. 《PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率》

4. 《聊一聊双十一背后的技术 - 不一样的秒杀技术, 裸秒》

Flag Counter

digoal’s 大量PostgreSQL文章入口