CODEWITHSUNDEEP
node.jspostgresqllocking
Kukula Mula
Kukula Mula

Reputation: 1869

Postgresql lock

I've read the documentation and couldn't figure out which lock state I need to use for blocking all update/insert/delete commands on the table while allowing select command

Please help me figure out what should replace ???

BEGIN WORK;
LOCK TABLE t1 IN ??? MODE;
INSERT INTO t1(a,b,c) VALUES
    (1,2,3);
SELECT id FROM t1 ORDER BY id DESC LIMIT 1;
COMMIT WORK;

Can I execute this command from Node.js using the pool.query command?

Upvotes: 0

Views: 355

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1620

The documentation states:

ROW EXCLUSIVE

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

So in order to change data you need that lock. To prevent anyone from changing data in some table you have to lock it with conflicting mode (see table 13.2 in the same documentation page). You can see there are 4 such modes:

  • SHARE
  • SHARE ROW EXCL.
  • EXCL.
  • ACCESS EXCL.

To lock the table do:

LOCK TABLE "my_table" IN SHARE MODE;

Upvotes: 2

Related Questions