Why can't advisory locks be unlocked while in a transaction with pg-promise?

user_1827734 0
Viewed 1305

I ran across the following code and was trying to debug it, but I don't understand why the lock couldn't be unlocked in this scenario:

const db = pgp(opts)

await db.one('SELECT pg_try_advisory_lock(1) AS lock') // returns true, and I can see the lock in the DB

// no await 
db.tx(async t => {
      const fingerprintIds = fingerprints.map(item => item.id)
      sql = `UPDATE fingerprint SET blah=1 WHERE id IN ($(fingerprintIds:list)) RETURNING id`
      const updatedFingerprintIds = await db.query(sql, { fingerprintIds }) // yes, it is not using the transaction object 

      // some other database calls
    })

result = await db.one('SELECT pg_advisory_unlock(1) AS lock')
// result.lock === false, database logs the error: 'WARNING:  you don't own a lock of type ExclusiveLock'
// lock still stays in the database (until the session ends)

Of course, when I add the await in front of the db.tx call, and use t in the transaction callback, then it works as expected.

Does this happen because the pg-promise library is in a transactional state when the unlock is called? Because when I call this succession of queries, the lock will unlock as expected:

SELECT pg_try_advisory_lock(1) AS lock;
BEGIN;
SELECT * FROM table;
SELECT pg_advisory_unlock(1) AS lock;
pg-promise postgresql-9.5 postgresql
1 Answers

Those lock operations are exclusive to the current connection session, and so they must be executed within the same transaction block:

await db.tx(async t => {

    await t.func('pg_try_advisory_lock', [1]);

    // execute all queries here, against 't' context;
    // ...

    await t.func('pg_advisory_unlock', [1]);

});
  • We use method func here just to shorten the syntax.

That however won't execute the unlock, if the transaction fails, so if that's a concern, then you will need to manually catch errors inside the transaction:

await db.tx(async t => {

    await t.func('pg_try_advisory_lock', [1]);

    let result, err;

    try {
        // execute all queries here, against 't' context;
        // and set the 'result'...

    } catch(e) {
        err = e;
    }

    await t.func('pg_advisory_unlock', [1]);

    if(e) {
        throw e;
    }
    return result;
});

UPDATE

I’m using an advisory lock because there are a lot of other things (non-idempotent, non-database) that happen outside of this one database transaction, and since there are multiple instances of the app running, the lock is to prevent other instances from doing the same work at the same time

I'm not sure how this type of lock can help across multiple databases or even multiple apps, since those locks can only work within the same session.

But if you really need to execute those locks outside of the transaction, and yet against the same connection, then you would need to manage the connection manually:

// Somewhere in the beginning of your chain logic,
// you allocate a manually-managed connection:
const dbm = await db.connect();

// Then your lock happens:
await dbm.func('pg_try_advisory_lock', [1]);


// Then you transaction happens...
await dbm.tx(async t => {
    // transaction queries
});

// Then your unlock query:
await dbm.func('pg_advisory_unlock', [1]);

// Then you release the connection:
dbm.done();

You just have to be careful with this, not to leak connections ;)

user_11020510
Related