How to use "Try" Postgres Advisory Locks

Question

I am experiencing some unexpected (to me) behavior using pg_try_advisory_lock. I believe this may be connection pooling / timeout related.

pg_advisory_lock is working as expected. When I call the function and the desired lock is already in use, my application waits until the specified command timeout on the function call.

however, when i replace with pg_try_advisory_lock and instead check the result of this function (true / false) to determine if the lock was acquired some scenario is allowing multiple processes (single threaded .net core deployed to ECS) to acquire "true" on the same lock key at the same time.

in C# code, I have implemented within an IDisposable and make my call to release the lock and dispose of the underlying connection on disposal. This is the case both for my calls to pg_advisory_lock and pg_try_advisory_lock. all of the work that needs to be synchronized happens inside a using block.

my operating theory is that the settings around connection pooling / timeouts are at play here. since the try call doesnt block, the session context for the lock "disposes" at the postgres - perhaps as a result of the connection being idle(?).

if that is the cause, the simplest solution seems to be to disable any kind of pooling for the connections used in try locking. but since pooling is just a theory at this point, it seems a bit early to start targeting a specific solution.

any ideas what may be the cause?

Example of the C#

using (Api.Instance.Locking.TryAcquire(someKey1, someKey2, out var acquired))
{
    if (acquired)
    {
        // do some locked work
    }
}

Under the hood. TryAcquire is calling

select pg_try_advisory_lock as acquired from pg_try_advisory_lock(@key1,@key2)

score 1 · Answer 1 · answered Apr 22 '19 at 23:53

This turned out to be kind of dumb. No changes to pooling where required.

I am using Dapper and NpgSql libraries. The NpgsqlConnection returns to a closed state after being used to .Query() unless the connection is explicitly opened.

This was impacting both my calls to try / blocking versions of the advisory lock calls albeit in a less adverse way in the blocking capacity.

How to use "Try" Postgres Advisory Locks

1 Answers1