Search code examples
databasepostgresqltransactionslocking

How to acquire lock on a table and not let other transaction get read access?

I am having a nodejs program which uses sequelize to create tables and insert data based on it.

Now, in future we are going to have multiple instances of the program and so we don't want multiple instances to read from the table during program startup so that only one instance can do the setup thing if required and other instance shouldn't get 'any access' to the table until the first instance has completed it's work.

I have checked 'transaction locking' - shared and exclusive but both of them seems to be giving reading access to the tables which I don't want.

My requirement is specifically that once a transaction acquires lock on a table, other transaction shouldn't be able to read from that table unless first one has completed it's work. How can I do this?

Solution

  • As documented in the manual the statement to lock a table is, LOCK TABLE ...

    If you lock a table in exclusive mode, then no other access is allowed - not even a SELECT. Exclusive mode is the default:

    If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used.

    The manual explains the different lock modes:

    ACCESS EXCLUSIVE

    This mode guarantees that the holder is the only transaction accessing the table in any way.