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?
User contributions licensed under CC BY-SA 3.0