Race condition in advisory_lock_exists?

[dzirtusss]

In multithreaded environment, e.g. 80 sidekiq parallel jobs (in our case), advisory_lock_exists? actually is mostly guaranteed to create many false positive locks in postgres. Most probably because 1st thread acquires lock and execution is passed to other thread while lock is not released.

I guess there is no easy way to overcome this with existing gem logic, as threads might be terminated any time and wrapping in syncronize is not a good way either.

So... created alternative nonblocking exist check.

class ApplicationRecord
  def self.advisory_lock_nonblocking_exists?(key)
    lock_keys = WithAdvisoryLock::PostgreSQL.new(connection, key, {}).lock_keys

    connection.select_value(<<~SQL.squish).present?
      SELECT 1 FROM pg_locks
      WHERE locktype='advisory'
        AND database=(SELECT oid FROM pg_database WHERE datname=CURRENT_DATABASE())
        AND classid=#{lock_keys.first.to_i}
        AND objid=#{lock_keys.last.to_i}
    SQL
  end
end

Not sure if such a way may have other problems (e.g. there might be cases when those pg tables are not accessible in restricted cloud hostings), but at least seems to work for our situation on AWS RDS - 1 big job opens lock, and many other small jobs just need to wait.