Do PostgreSQL advisory locks not work within transactions?

1

I'm using C# with the Npgsql driver to connect to postgresql 9.6.9. I have two servers connecting to the same database, running the following initialization code in a loop, to be sure they conflict with each other:

using (var txScope = new TransactionScope())
using (var connection = Connection())
{
    connection.EnlistTransaction(Transaction.Current);
    using (var cmd = new NpgsqlCommand("SELECT pg_advisory_lock(1024)", connection))
    using (cmd.ExecuteReader()) { }

    // The following line fails:
    using (var cmd = new NpgsqlCommand(@"CREATE OR REPLACE FUNCTION update_column()   
        RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END; $$ language 'plpgsql'; ", connection))
    {
        cmd.ExecuteNonQuery();
    }

    using (var cmd = new NpgsqlCommand("SELECT pg_advisory_unlock(1024)", connection))
    using (cmd.ExecuteReader()) { }

    txScope.Complete();
}

The advisory lock should prevent the CREATE query from running more than once currently, but it is consistently failing with "Npgsql.PostgresException (0x80004005): XX000: tuple concurrently updated" where the function is created. If I use LOCK TABLE instead of the advisory lock, it works. If I do it without transactions, it also works. However, it seems like a fragile design to remove transactions every time I want to lock.

Note: I've tried this with the Npgsql Transactions API, and with BEGIN/COMMIT with the same result.

I think the lock is correctly working as a mutex, so what's going wrong with the transaction?

postgresql
locking
concurrency
npgsql
asked on Stack Overflow Sep 19, 2018 by piojo

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0