Lock table row from data modifying

Refresh

December 2018

Views

35 time

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

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

now, I need lock this table from data modifying, while below plsql block is running

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

So while this block is running, I run other query:

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

This query works immediately and updates row when running plsql block is not completed.

I need contrary, I need that update not worked and waited, while plsql block running.

I suppose that pg_advisory_lock() and pg_advisory_unlock() would help, but how to use it, I don't understood, what is key parameter for these functions, I don't get it.

And also not sure, if these functions will work at all.

any help would be appreciated.

2 answers

1

Вы хотите , select col from t1 where id = 1 FOR UPDATE into res1;чтобы получить блокировку, так что весь блок должен выглядеть следующим образом :

DO $$ declare res1 TEXT; res2 TEXT; BEGIN --PERFORM pg_advisory_lock( )
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( )
END; $$ LANGUAGE PLPGSQL

См http://www.postgresql.org/docs/9.4/static/explicit-locking.html

1

Вы можете явно блокировать строки в операции в SELECTпункте:

DO $$
  DECLARE
     res1 TEXT;
     res2 TEXT;
  BEGIN 
    SELECT col INTO res1 FROM t1 WHERE id = 1 FOR SHARE;
    pg_sleep(5); -- Sleep 5 seconds
    SELECT col INTO res2 FROM t1 WHERE id = 1;
    RAISE NOTICE '% - %', res1, res2;
  END
$$ LANGUAGE plpgsql;

Используйте FOR SHAREпункт , а не FOR UPDATEразрешить другой сеанс , чтобы прочитать данные, но не обновлять.