Postgresql skip locked跳过行锁消除行锁冲突等待

场景如下：
如果某人申请一张卡，那么这张卡信息所在的行，都会加锁，以避免别人来申请同一张卡，如果别人同时也要申请一张卡，那么需要跳过加锁的卡，申请下一张无锁的卡，类似此场景都可以使用skip locked。

skip locked使用

hank=> insert into tb1 values (1,100);
INSERT 0 1
hank=> insert into tb1 values (2,101);
INSERT 0 1
hank=> select * from tb1;
 a |  b  
---+-----
 1 | 100
 2 | 101
(2 rows)

hank=> begin;
BEGIN
hank=> select * from tb1 order by 1 limit 1 for update skip locked;
 a |  b  
---+-----
 1 | 100
(1 row)

另外开启一个session
hank=> select * from tb1;
 a |  b  
---+-----
 1 | 100
 2 | 101
(2 rows)

下面可见取到的是a为2的记录，跳过了a=1的记录，而且不需要等待
hank=> begin;
BEGIN
hank=> select * from tb1 order by 1 limit 1 for update skip locked;
 a |  b  
---+-----
 2 | 101
(1 row)

另外还有一种方法，可以使用advisory lock来做判断

hank=> begin;
hank=> select pg_try_advisory_xact_lock(1),* from tb1 where a=1;
 pg_try_advisory_xact_lock | a |  b  
---------------------------+---+-----
 t                         | 1 | 100
 
打开另一个session,开启一个事物，然后查询advisory lock持有的情况，可见a=1的记录是false，无法获取advisory lock。
hank=> begin;
BEGIN
hank=> select pg_try_advisory_xact_lock(1),* from tb1 where a=1;   
 pg_try_advisory_xact_lock | a |  b  
---------------------------+---+-----
 f                         | 1 | 100
(1 row)

以下可见a=2的记录可获得advisory lock。

hank=> select pg_try_advisory_xact_lock(2),* from tb1 where a=2; 
 pg_try_advisory_xact_lock | a |  b  
---------------------------+---+-----
 t                         | 2 | 101

由上面例子可写出以下更新的函数

create or replace function advisory_lock_update() returns void as $$
declare
  v_a int;
begin
  for v_a in select a from tb1  -- 扫描tb1
  loop  
    if pg_try_advisory_xact_lock(v_a) then -- 获取到a的LOCK才更新，否则继续扫描
      update tb1 set b=b+1 where a=v_a;
    end if;
  end loop;
end;
$$ language plpgsql strict;

另外需要注意的是，使用advisory lock的时候，pg_try_advisory_xact_lock的使用的数值如果在整个库范围内不是唯一的，那么不同表的相同值，都不能获取咨询锁，所以尽量每个表要判断的值最好是错开，分段使用,所以在使用的时候还是有一定局限性。

参考：
https://www.postgresql.org/docs/9.6/explicit-locking.html
https://www.postgresql.org/docs/9.6/functions-admin.html
https://www.postgresql.org/docs/9.6/sql-select.html
https://github.com/digoal/blog/blob/master/201610/20161018_01.md