CODEWITHSUNDEEP
databasepostgresqlplpgsqlpostgresql-9.4
Oto Shavadze
Oto Shavadze

Reputation: 42853

Lock table row from data modifying

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.

Upvotes: 2

Views: 74

Answers (2)

JiriS
JiriS

Reputation: 7570

You want select col from t1 where id = 1 FOR UPDATE into res1; to get the lock, so the whole block should look as following:

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

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

Upvotes: 1

Patrick
Patrick

Reputation: 32336

You can explicitly lock the rows in a transaction in the SELECT clause:

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;

Use the FOR SHARE clause rather than FOR UPDATE to allow other session to read the data, but not update.

Upvotes: 1

Related Questions