database - 从数据修改中锁定表行

Question

CREATE TABLE t1 (
  id serial int,
  col text
);

insert into t1(col) values('old_value');

现在，我需要在下面的 plsql block 运行时锁定此表以防止数据修改

DO $$
  declare 
     res1 TEXT;
     res2 TEXT;
  BEGIN 
        --PERFORM pg_advisory_lock( )  
        select col from t1 where id = 1 into  res1;
        FOR i in 1..2000000000 LOOP
           -- this is just for waiting several second
        END LOOP;
        select col from t1 where id = 1 into  res2;
        RAISE NOTICE '% - %', res1, res2;
        --PERFORM pg_advisory_unlock( )  
  END;
  $$ LANGUAGE PLPGSQL

所以当这个 block 运行时，我运行其他查询:

update t1 SET col = 'new_value' where id = 1;

当运行 plsql block 未完成时，此查询会立即运行并更新行。

我需要相反，我需要更新不工作并等待，而 plsql block 运行。

我想 pg_advisory_lock() 和 pg_advisory_unlock() 会有所帮助，但是如何使用它，我不明白，这些函数的关键参数是什么，我不明白。

也不确定这些功能是否有效。

如有任何帮助，我们将不胜感激。

Answer 1

你想要select col from t1 where id = 1 FOR UPDATE into res1;获得锁，所以整个 block 应该如下所示:

DO $$
declare
res1 TEXT;
res2 TEXT;
BEGIN
--PERFORM pg_advisory_lock( )<br/>
select col from t1 where id = 1 into FOR UPDATE res1;
FOR i in 1..2000000000 LOOP
-- this is just for waiting several second
END LOOP;
select col from t1 where id = 1 into res2;
RAISE NOTICE '% - %', res1, res2;
--PERFORM pg_advisory_unlock( )<br/>
END;
$$ LANGUAGE PLPGSQL

参见 http://www.postgresql.org/docs/9.4/static/explicit-locking.html