Manually Lock Table in Knex

Question

I have one table, when a new row is added to it, all existing rows need to be updated. Essentially, before one "insert" is finished, I don't want to allow another "insert" to take place.

So I guess normal row level lock or read lock is not enough and want to directly lock the tables during the transaction.

Is there a way to manually lock tables (in ACCESS EXCLUSIVE mode) in Knex?

My guess is that this will involve:

1. running raw sqls (lock and unlock) and make sure the same

2. connection is used for the transaction.

Is there a way to do that? or I there are better solution to my problem.

p.s. another approach I'm exploring is to use a job queue and let a single worker to insert records (using a raw sql connection). But I was told this will not work in a multiple node scenario.

Answer 1

With knex you cannot currently choose to which connection queries are sent except when you are using transactions. All queries sent to same transaction are also sent to the same connection.

For sending locking commands one needs to use knex.raw.

1. Start transaction
2. Send raw SQL locking queries to transaction
3. ???
4. profit

How locking is exactly done depends on the database you are using. Usually locks are automatically released when transaction is committed.