Worth using `pg_try_advisory_xact_lock` for PostgreSQL?

[gaffneyc]

I recently added the gem to a project and we ran into a case where the locks were not getting freed due to the transaction being aborted when it hit a configured statement timeout. When the abort occurred ActiveRecord would throw PG::InFailedSqlTransaction when trying to do the unlock.

We monkey patched in pg_try_advisory_xact_lock which auto releases at the end of the transaction and it has been working well so far. I'm fairly new to advisory locks so there might be a reason not to use them. From what I understand the main issues are that pg_try_advisory_lock will work outside of a transaction and pg_try_advisory_xact_lock is only supported in PostgreSQL 9.1 and up.

[machty]

Is it always/generally the case that you're in a transaction before you grab an advisory lock?

This is my first stab at a monkey patch (which might makes sense for a PR)... is it close to what you landed on?

module WithAdvisoryLock
  class PostgreSQL < Base
    # See http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
    def try_lock
      if connection.open_transactions > 0
        execute_successful?('pg_try_advisory_xact_lock')
      else
        execute_successful?('pg_try_advisory_lock')
      end
    end

    def release_lock
      if connection.open_transactions > 0
        # lock is released automatically at transaction close
      else
        execute_successful?('pg_try_advisory_lock')
      end
    end
  end
end

[amalagaura]

I wanted to second the request for a transaction advisory lock. We immediately ran into the issue of unreleased locks. Transaction advisory locks seem superior because they will always be cleaned up.

[seuros]

could you send a PR please ?

[johnnagro]

+1

[danielnc]

I've opened a pull request

#15