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.

Answer 2

That's not possible at all if you're using MySQL, since knex doesn't allow you to choose which connection to use. The only way to make sure two particular queries are executed at the same connection of pool is to nest them inside a knex transaction. But MySQL documentation says:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

Putting the LOCK TABLE query inside a transaction does work if your DB driver is Postgres, but not MySQL.

Answer 3

You can use db locks for this, like GET_LOCK in mysql.

Specifically you can use a transaction in knex just to make sure you have a specific connection to work with, or you could use acquireConnection and set the connection to the GET_LOCK query.

http://knexjs.org/#Builder-connection

Example:

let conn;
try {
  conn = await knex.client.acquireConnection();

  try {
    let lockRes = await knex.select(knex.raw('GET_LOCK(?, ?) as res', ['lock_name', 15])).connection(conn);

    if (!lockRes.length || lockRes[0]['res'] !== 1) {
      // Did not acquire lock
      return;
    }

    // Your code here
  }
  finally {
    await knex.select(knex.raw('RELEASE_LOCK(?)', ['lock_name'])).connection(conn);
  }
}
finally {
  conn && await knex.client.releaseConnection(conn);
}