Reuse of connections for unlock in multithreaded applications

[brauliobo]

Take the code below that relies on postgresql advisory locks to execute in parallel with multiple workers and threads:

DB.select(Sequel.function :pg_try_advisory_lock, 0, 123).all
# (multi threaded execution)

# unlock depends on luck to use same connection as the lock above,
# otherwise, postgresql issues WARNING:  you don't own a lock of type ExclusiveLock
# and the lock isn't released
DB.select(Sequel.function :pg_advisory_unlock, 0, 123).all

Each worker relies on a database pool. I need to force Sequel to use the same connection to unlock as the connection use to acquire the lock, otherwise the unlock fails

[brauliobo]

Using a transaction and xact_lock solves the issue

DB.transaction do
  DB.select(Sequel.function :pg_try_advisory_xact_lock, 0, 123).all

  # do work
end

[brauliobo]

Alternatively, DB.synchronize{ } can be used. https://www.rubydoc.info/github/jeremyevans/sequel/Sequel%2FDatabase:synchronize