CODEWITHSUNDEEP
databasepostgresqltransactionslocking
Ashutosh Tiwari
Ashutosh Tiwari

Reputation: 13

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?

Upvotes: 0

Views: 3964

Answers (2)

O. Jones
O. Jones

Reputation: 108651

In MySQL use LOCK TABLES to lock an entire table.

In postgresql LOCK TABLE whatever IN EXCLUSIVE MODE; does the trick.

For best results have your app, when starting, look for a particular table. Do something simple and fast such as SELECT id FROM whatever LIMIT 1; to probe whether the table exists. If your app gets an exception because the table isn't there, then do

CREATE TABLE whatever ....;
LOCK TABLES whatever WRITE;

from the app creating the table. It blocks access to the table from all instances of your app except the one that gets the LOCK.

Once your table is locked, the initial SELECT I suggested will block from other clients. There's a possible race condition if two clients try to create the table more-or-less concurrently. But the extra CREATE TABLE will throw an exception.

Note: if you LOCK more than one table, and it's possible to run the code from more than one instance of the app, always always lock the tables in the same order, or you have the potential for a deadlock.

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 0

Related Questions