I know that this is documented behaviour but it trips me over every time when i use this gem. I think it would be more sensible to raise an error if it couldn't acquire the lock, instead of simply not executing the block. The caller could handle the exception if executing the block was optional.

On Postgres with_advisory_lock converts non-numeric lock names to integers by converting the lock name to a string and then computing the hash of the string. Unfortunately Ruby MRI 1.9 uses a session local random seed in the String hash method so hash codes will be different in different MRI instances. See this Stackoverflow question for more details. It looks like using Zlib.crc32 instead should do the trick.

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.

Hi,

We have a problem with closure_tree but comes from with_advisory_lock because on log are trying to get SELECT GET_LOCK('closure_tree', 0) until timeout finish the thread of server or in other cases (because runs indefinitely) doesn't end until mysql reset cache status.

Is possible to improve this state by disabling cache on MySQL but some times Query Cache is really necessary or simply doesn't have MySQL configuration access.

Our test case was maded with ab console program (http://httpd.apache.org/docs/2.0/programs/ab.html) and getting a similar command like mentioned we get the exposed result.

ab -n 6 -c 6 -p put.data -T application/json http://0.0.0.0:3000/element

CACHE (0.0ms)  SELECT GET_LOCK('closure_tree', 0)
CACHE (0.1ms)  SELECT GET_LOCK('closure_tree', 0)
CACHE (0.0ms)  SELECT GET_LOCK('closure_tree', 0)
...

If you get this query with a dynamic timestamp on file lib/with_advisory_lock/mysql.rb similar than mentioned, you will change the query and will get the real state of the locked string after timestamp change (1 second).

0 < connection.select_value("SELECT GET_LOCK(#{quoted_lock_name}, 0)*"+Time.now.to_i.to_s)

Thanks !

Has anyone used this gem with RDSProxy? Doing testing locally it seems that the locking works properly but I'm not sure if I'm missing something.

I explained this issue in depth in #47. The short version is that ActiveRecord caching can lead to stale query results within an advisory lock, at a time when it's critical to be working with up-to-date data.

I'm thinking that it may make sense for the with_advisory_lock block to automatically turn off caching via ActiveRecord::QueryCache.uncached for the duration of the block. Or perhaps there should be an argument to with_advisory_lock to control this.

The example use case is using an advisory lock to prevent the double-creation of a record. Take this for example:

def create_or_update_user(external_user)
  transaction do
    with_advisory_lock("create-user-#{external_user.id}", transaction: true) do
      user = User.find_or_initialize_by(external_id: external_user.id)
      user.update_info(external_user)
      user.save!
    end
  end
end

In this example users are created from 3rd party webhooks, and the goal is to create a new user if it doesn't already exist, or update the existing one.

The problem I ran into is that upstream of the lock in my code I had a User.find_by(external_id: external_user.id). The user didn't exist at this point, so the result was being cached as nil. Then inside the lock find_or_initialize_by used the cached result of nil, even though by this time the user record had already been created, thus defeating the purpose of the lock.

I solve this in my code by using uncached to ensure a fresh query:

def create_or_update_user(external_user)
  transaction do
    with_advisory_lock("create-user-#{external_user.id}", transaction: true) do
      self.class.uncached do
        user = User.find_or_initialize_by(external_id: external_user.id)
        user.update_info(external_user)
        user.save!
      end
    end
  end
end

This issue was originally posted on Stack Overflow. [Stack Overflow].
(https://stackoverflow.com/questions/56146171/ruby-with-advisory-lock-test-with-multiple-threads-fails-intermittently)

Summary of the issue is that rails tests which create multiple threads then try to call an operation which uses with_advisory_lock do not seem work properly. Things that were tried:

1. Wrap with_advisory_lock block in a Transaction -> Locking behavior as expected
2. Create a transacion within the with_advisory_lock block -> Locking behavior as expected
3. Use only transaction and NO with_advisory_lock -> Locking behavior as expected
   The only thing that doesn't seem to work as expected is just using with_advisory_lock as intended.

STACK OVERFLOW TICKET
I'm using the with_advisory_lock gem to try and ensure that a record is created only once. Here's the github url to the gem.

I have the following code, which sits in an operation class that I wrote to handle creating user subscriptions:

def create_subscription_for user
  subscription = UserSubscription.with_advisory_lock("lock_%d" % user.id) do
    UserSubscription.where({ user_id: user.id }).first_or_create
  end

  # do more stuff on that subscription
end

and the accompanying test:

threads = []
user = FactoryBot.create(:user)

rand(5..10).times do
  threads << Thread.new do
    subject.create_subscription_for(user)
  end
end

threads.each(&:join)

expect(UserSubscription.count).to eq(1)

What I expect to happen:

• The first thread to get to the block acquires the lock and creates a record.
• Any other thread that gets to the block while it's being held by another thread [waits indefinitely until the lock is released] 1 (as per docs)
• As soon as the lock is released by the first thread that created the record, another thread acquires the lock and now finds the record because it was already created by the first thread.

What actually happens:

• The first thread to get to the block acquires the lock and creates a record.
• Any other thread that gets to the block while it's being held by another thread goes and executes the code in the block anyway and as a result, when running the test, it sometimes fails with a ActiveRecord::RecordNotUnique error (I have a unique index on the table that allows for a single user_subscription with the same user_id)

What is more weird is that if I add a sleep for a few hundred milliseconds in my method just before the find_or_create method, the test never fails:

def create_subscription_for user
  subscription = UserSubscription.with_advisory_lock("lock_%d" % user.id) do
    sleep 0.2
    UserSubscription.where({ user_id: user.id }).first_or_create
  end

  # do more stuff on that subscription
end

My questions are: "Why is adding the sleep 0.2 making the tests always pass?" and "Where do I look to debug this?"

Thanks!

UPDATE: Tweaking the tests a little bit causes them to always fail:

threads = []
user = FactoryBot.create(:user)

rand(5..10).times do
  threads << Thread.new do
    sleep
    subject.create_subscription_for(user)
  end
end

until threads.all? { |t| t.status == 'sleep' }
  sleep 0.1
end

threads.each(&:wakeup)
threads.each(&:join)

expect(UserSubscription.count).to eq(1)

I have also wrapped first_or_create in a transaction, which makes the test pass and everything to work as expected:

def create_subscription_for user
  subscription = UserSubscription.with_advisory_lock("lock_%d" % user.id) do
    UserSubscription.transaction do
      UserSubscription.where({ user_id: user.id }).first_or_create
    end
  end

  # do more stuff on that subscription
end

So why is wrapping first_or_create in a transaction necessary to make things work?

I have code that uses advisory locks to synchronize access across processes. But the lock is ignored when running concurrent fibers.

> fs = 2.times.map{ Fiber.new{ ActiveRecord::Base.with_advisory_lock("test", timeout_seconds: 0){ Fiber.yield(:inside_lock) } } }
=> [#<Fiber:0x000000010cecf380 (pry):15 (created)>, #<Fiber:0x000000010cecefc0 (pry):15 (created)>]
> fs.map(&:resume)
   (0.8ms)  SELECT pg_try_advisory_lock(1484750348,0) AS t92239c629d25968c41bc80391a4aabb4 /* test */
   (0.2ms)  SELECT pg_try_advisory_lock(1484750348,0) AS t54e121e1f436b79ab55825ef77d4125e /* test */
=> [:inside_lock, :inside_lock]

This is because with_advisory_lock ignores the lock if nested inside another lock, determined by thread variable. In case of fibers, they share the same thread, but locks are not actually nested.

It is possible to work-around the issue by adding a local Mutex, but I feel like this behavior is not correct.

The fix is simple:

diff --git a/lib/with_advisory_lock/base.rb b/lib/with_advisory_lock/base.rb
index 19f6791..a4a7e73 100644
--- a/lib/with_advisory_lock/base.rb
+++ b/lib/with_advisory_lock/base.rb
@@ -44,8 +44,8 @@ def lock_stack_item
     end
 
     def self.lock_stack
-      # access doesn't need to be synchronized as it is only accessed by the current thread.
-      Thread.current[:with_advisory_lock_stack] ||= []
+      # access doesn't need to be synchronized as it is only accessed by the current fiber/thread.
+      Fiber.current[:with_advisory_lock_stack] ||= []
     end
     delegate :lock_stack, to: 'self.class'

Is this gem still maintained?
Do you have plans on supporting rails 7?

I've used it successfully in the past and would like to reuse it again, but we have rails 7 migration planned, and want to be sure it will be rails7 friendly before going in

Hello maintainers,

I'm working on a feature that would very much benefit from nested advisory locks. Currently I'm including this gem by specifying the ref of HEAD of master branch (currently 25e415c).

Is there a planned release coming up? Our testing so far is successful - it seems stable and reliable - but it's always preferable to be able to lock into a specific release than directly to a commit.

Thanks!

Hi,

I was wondering why the gem thread_safe is listed as a runtime dependency in the gemspec... Inspecting the code I see no usage of this gem... Could it be removed? This gem was merged into concurrent-ruby and is deprecated.

Hi, and thanks for this gem that looks to be the answer to my problem.

ActiveRecord 6 is not mentioned in the README, are the known issues, or is it not updated yet?

With the last merge: https://travis-ci.org/ClosureTree/with_advisory_lock/jobs/255309283

NoMethodError:         NoMethodError: undefined method `can_be_bound?' for ActiveRecord::PredicateBuilder:Class
...
4.2.9/lib/active_record/relation/query_methods.rb:574:in `where'
            /home/travis/.rvm/gems/ruby-2.2.6/gems/activerecord-4.2.9/lib/active_record/querying.rb:10:in `where'
            /home/travis/build/ClosureTree/with_advisory_lock/test/parallelism_test.rb:28:in `block in work'

(perhaps skip that one test if AR 4.2 and MySQL?)

I wonder if that is

1. normal behaviour
2. can be something like Dir.tmpdir used for lock storage?

During testing I found that (at least in my set up), ActiveRecord was re-using connections in which advisory locks were held, so when trying to lock on one again, even though it is locked, as it is within the same session.

I'm not sure if I'm doing something wrong but I can't guarantee the connection that is used to take the lock, so I can't guarantee this is locking correctly all the time.

Can anyone shed some light on to this issue?

I would expect Foo.with_advisory_lock("foo", 0) { ... } to try to obtain the lock once, and give up immediately if it cannot be obtained.

Instead, the code will never try to obtain the lock. It will simply give up.

I've been using with_advisory_lock for transactional locks on Rails 4.2 for a long time with success. I just upgraded my app to Rails 5.2 and suddenly they're not working anymore. I'm using Postgres.

My use case is to prevent double-creation of a record (very similar to the use described in #42). It looks something like this:

def create_or_update_user(external_user)
  transaction do
    with_advisory_lock("create-user-#{external_user.id}", transaction: true) do
      user = User.find_or_initialize_by(external_id: external_user.id)
      user.update_info(external_user)
      user.save!
    end
  end
end

This code is executed by Sidekiq workers in jobs that are triggered by 3rd party webhooks. Often two jobs will run simultaneously that are trying to run this same method for the same user. On Rails 4.2 the transactional advisory lock would successfully prevent two workers from trying to create the same user record. Now that I'm on Rails 5.2 I'm getting flooded with ActiveRecord::RecordNotUnique errors, meaning that the the locks are not working like they used to. (The table has a unique index in postgres that prevents a duplicate record.)

Does anyone know what might have changed in Rails 5 that could cause this? Did something change with the way that ActiveRecord connects to the database? @mceachen would your comments on #42 apply here? Those were about session-level locks on MySQL so I'm unsure, but the fact that it used to work and now it doesn't is very confusing to me.

PS I've noticed that the block isn't necessary for transaction locks, since the lock is released when the transaction ends, so I've also tried the following code and I get the same result. Both of these approaches worked on Rails 4.2:

def create_or_update_user(external_user)
  transaction do
    with_advisory_lock("create-user-#{external_user.id}", transaction: true)
    user = User.find_or_initialize_by(external_id: external_user.id)
    user.update_info(external_user)
    user.save!
  end
end

I've had a good rummage through the code, and am pretty sure things aren't wired up this way, but I wondered if it's possible to kick off a lock with one method call, then release the lock with another? This would be useful say in a before_save and after_save_commit callback arrangement.

As i seen by usage example, method used as instance method.
It is confusing as one may think it is like SQL row lock.
Please emphasize in docs that this it is class method. It is relying on locks string only without any relation to class at all... Really, like some kind of helper...
So should not be illusion of row locking emulation....

BTW, you may introduce per instance locking by using lock string like "[User 11]" or so. Then lock_name may be some suffix of this.

The URL http://mceachen.github.io/with_advisory_lock goes to 404 – perhaps it should be removed?

https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock

Dunno if you want to complicate the code by adding support for nested locks in that case.. but might be nice.

It's not a bug although it might be a good place to ask, at least the answer might be an interesting section in Readme ;).

We've been using advisory locks extensively in multiple apps via with_advisory_lock gem and now, we are looking into introducing a connection pooler due to the huge number of DB connections. We are considering pgbouncer with transaction pool_mode and based on some research, some things are not really clear, so I was wondering if you have any experience with that.

So advisory locks are session-based features, which won't work with a transaction pool mode. This is also a common source of the issues with running migrations in Rails and one of the reasons why in Rails 6 it's possible to disable advisory locks to solve the issue with migrations. On the other hand, there are transaction-level locks, which are also supported by this gem, which might work just fine with the transaction pool mode. Yet, this doesn't seem to go along with the general recommendations that you shouldn't use advisory locks with pgbouncer or even with the fact that in Rails, it's either a session-based advisory lock or not at all (although this decision could be made due to the fact that it's possible to disable transaction per migration).

So I was wondering if transaction-level locks will just work with pgbouncer or should we find some alternative?

Thanks in advance for the answer.

Microsoft SQL Server has a similar locking mechanism detailed here: https://msdn.microsoft.com/en-us/library/ms189823.aspx
I've sketched in basic support (enough for my needs). Any chance you'd accept a pull request?

Hey there -- thanks for providing this gem. Is there a reason you chose to always provide a timeout of 0 within the MySQL driver rather than pass the thread timeout along? Would you be receptive to a PR that changes this behavior?

Thanks!

It looks like the code was added 7 years ago, so I could easily believe that it is fixed.

I could very well be missing something but is there a reason why the with_advisory_lock method's second, timeout, parameter isn't passed to the mysql GET_LOCK here (it's actually hardcoded to 0):

https://github.com/mceachen/with_advisory_lock/blob/master/lib/with_advisory_lock/mysql.rb#L18

Thanks!

-adam

https://github.blog/2022-08-25-introducing-trilogy-a-new-database-adapter-for-ruby-on-rails/

https://github.com/trilogy-libraries/activerecord-trilogy-adapter

(It's mysql-compatible, I noticed that when testing out trilogy in a project that uses with_advisory_lock, I'm seeing a lot of .lock- files)

I am having difficulty executing with_advisory_lock in my Rails test environment.

Works fine when RAILS_ENV=development:

code/pullreminders » rails c
Loading development environment (Rails 5.1.6)
Nirb(main):001:0> NotificationRule.with_advisory_lock
Traceback (most recent call last):
        1: from (irb):1
ArgumentError (wrong number of arguments (given 0, expected 1..2))

But doesn't when `RAILS_ENV=test`:

code/pullreminders » RAILS_ENV=test rails c
Loading test environment (Rails 5.1.6)
irb(main):001:0> NotificationRule.with_advisory_lock
Traceback (most recent call last):
1: from (irb):1
NoMethodError (undefined method `with_advisory_lock' for #Class:0x00007f9769d8d080)

I am using Postgres and here's my Gemfile:

ruby '2.5.0'
gem 'rails', '~> 5.1.6'
gem 'with_advisory_lock', '~> 4.0.0'

As far as I can tell my development and test databases are the same as far as Postgres configuration and schema.

Hi! first off, thanks for the attention and for for the gem.

Rails: 5.0.0
psql (PostgreSQL) 10.3 (Debian 10.3-1.pgdg90+1)

My rails server is run inside a docker container with

bundle exec puma -t 0:5 -p ${PORT:-3000} -e ${RAILS_ENV:-development}

DB config has a pool of 5 threads as well

I am trying to figure out what I might be doing wrong in using this gem.

This is an excerpt of a service object

  def call
    Rails.logger.debug "Running thread #{Thread.current.object_id}"
    ActiveRecord::Base.with_advisory_lock_result("backlog-lock", timeout_seconds: 5) do
      if servicing.work_order.in_progress?
        servicing.update(status: :open, redone: true, approval_status: nil)
      else
        servicing.rescheduled!
        add_duplicated_servicing
        backlog_work_order
      end
    end
  end

This code gets executed as a result of a controller action, and to test I am executing two curl requests in parallel.

The behaviour I don't understand is that the second thread to reach the with_advisory_lock_result seems to be blocking the first one, instead of just waiting for 5 seconds while the other thread keeps running.

The log output from executing the code above is:

Started POST "/api/servicings/92/reschedule?redo_reason=testing" for 172.18.0.1 at 2018-12-26 20:46:17 +0000
Started POST "/api/servicings/91/reschedule?redo_reason=testing" for 172.18.0.1 at 2018-12-26 20:46:17 +0000
Processing by API::ServicingsController#reschedule as */*
  Parameters: {"redo_reason"=>"testing", "id"=>"91"}
Processing by API::ServicingsController#reschedule as */*
  Parameters: {"redo_reason"=>"testing", "id"=>"92"}
  User Load (1.6ms)  SELECT  "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  User Load (7.7ms)  SELECT  "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Servicing Load (4.7ms)  SELECT  "servicings".* FROM "servicings" WHERE "servicings"."deleted_at" IS NULL AND "servicings"."id" = $1 LIMIT $2  [["id", 92], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  CACHE (0.3ms)  SELECT  "servicings".* FROM "servicings" WHERE "servicings"."deleted_at" IS NULL AND "servicings"."id" = $1 LIMIT $2  [["id", 92], ["LIMIT", 1]]
  Servicing Load (9.5ms)  SELECT  "servicings".* FROM "servicings" WHERE "servicings"."deleted_at" IS NULL AND "servicings"."id" = $1 LIMIT $2  [["id", 91], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  CACHE (0.5ms)  SELECT  "servicings".* FROM "servicings" WHERE "servicings"."deleted_at" IS NULL AND "servicings"."id" = $1 LIMIT $2  [["id", 91], ["LIMIT", 1]]
   (2.0ms)  BEGIN
   (2.8ms)  BEGIN
   (0.3ms)  COMMIT
   (0.4ms)  COMMIT
Running thread 47076409369740
Running thread 69856980992120
   (1.5ms)  SELECT pg_try_advisory_lock(986825911,0) AS t3f11a179e210d135eade537bf1cf4340 /* backlog-lock */
   (0.8ms)  SELECT pg_try_advisory_lock(986825911,0) AS t27331309003857f07af7ba8c5d482a7f /* backlog-lock */
  WorkOrder Load (11.5ms)  SELECT  "work_orders".* FROM "work_orders" WHERE "work_orders"."deleted_at" IS NULL AND "work_orders"."id" = $1 LIMIT $2  [["id", 39], ["LIMIT", 1]]
   (1.3ms)  BEGIN
   (1.3ms)  COMMIT
  Location Load (3.5ms)  SELECT  "locations".* FROM "locations" WHERE "locations"."deleted_at" IS NULL AND "locations"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  WorkOrder Load (2.6ms)  SELECT  "work_orders".* FROM "work_orders" WHERE "work_orders"."deleted_at" IS NULL AND "work_orders"."status" IN ('open', 'in_progress') AND "work_orders"."backlog" = $1 AND "work_orders"."location_id" = 1 LIMIT $2  [["backlog", true], ["LIMIT", 1]]
   (0.4ms)  BEGIN
  SQL (5.4ms)  INSERT INTO "work_orders" ("contact", "location_id", "description", "backlog", "starts_at", "timezone", "ends_at", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["contact", "Donato test"], ["location_id", 1], ["description", "Test"], ["backlog", true], ["starts_at", 2018-12-27 12:00:00 UTC], ["timezone", "America/New_York"], ["ends_at", 2018-12-27 18:00:00 UTC], ["created_at", 2018-12-26 20:46:18 UTC], ["updated_at", 2018-12-26 20:46:18 UTC]]
   (3.9ms)  COMMIT
   (0.7ms)  BEGIN
   (0.5ms)  COMMIT
  Area Load (11.5ms)  SELECT  "areas".* FROM "areas" WHERE "areas"."deleted_at" IS NULL AND "areas"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
   (99.2ms)  SELECT pg_try_advisory_lock(986825911,0) AS t00e16279f8d89a1b21ffe48bd6306f09 /* backlog-lock */
   (1.1ms)  SELECT pg_try_advisory_lock(986825911,0) AS tf3d6a44a3253fb07f24c8015b242f75b /* backlog-lock */
   (1.3ms)  SELECT pg_try_advisory_lock(986825911,0) AS tccae0e9b109ac8a5fa3dacabd26ea120 /* backlog-lock */
   (0.7ms)  SELECT pg_try_advisory_lock(986825911,0) AS t705f95fd6f580325093a5c300580671e /* backlog-lock */
   (0.8ms)  SELECT pg_try_advisory_lock(986825911,0) AS tbfeb8cd03aef66de28ea5193a5772ce2 /* backlog-lock */
   (0.6ms)  SELECT pg_try_advisory_lock(986825911,0) AS t331e00c084d8fd5f7bebfffb5e1ad7f0 /* backlog-lock */
   (0.7ms)  SELECT pg_try_advisory_lock(986825911,0) AS t33c88295291c7d1a319c46a7043d5115 /* backlog-lock */
   (0.6ms)  SELECT pg_try_advisory_lock(986825911,0) AS t1e5a62030123421fa68169c6bc3629c9 /* backlog-lock */
   (0.6ms)  SELECT pg_try_advisory_lock(986825911,0) AS t52bc53f618514af84bc297cdacc9d25a /* backlog-lock */

This last line goes on for the 5 seconds (parameter to with_advisory_lock_result) and then it gives up -- no lock acquired -- and only then does the first thread resume work.

From the lines

Running thread 47076409369740
Running thread 69856980992120
   (1.5ms)  SELECT pg_try_advisory_lock(986825911,0) AS t3f11a179e210d135eade537bf1cf4340 /* backlog-lock */
   (0.8ms)  SELECT pg_try_advisory_lock(986825911,0) AS t27331309003857f07af7ba8c5d482a7f /* backlog-lock */

I presume that there are indeed 2 threads in parallel (different Thread.current.object_id), almost in sync with each other.

I expected that I would see logs of the first thread doing its work intermingled with logs of the second thread querying the pg_try_advisory_lock, which does not seem to be the case.

Perhaps I am missing a parameter, or a db config in rails....

At the moment, the gem generates lock id automatically based on the given string. The resulting lock id is hidden as an implementation detail. However, it would be useful to know for debugging purposes - to see which DB session is holding the current lock, in case it is stuck, and be able to force-release it.

Here is how it could work:

info = User.with_advisory_lock_result("user-#{user.id}"){ ... }
unless info.lock_was_acquired?
  p info.lock_id #=> [2116437524, 0]
end

select * from pg_locks where classid = 2116437524;

There's a MiniTest warning in the test results, which obscure the output quite a lot.

A failed test output is hard to find among all those warnings about "global use of must_equal".

Why do you use pg_try_advisory_lock and not just simple pg_advisory_lock which will avoid polling in SQL?

You can plan with this example:

  def with_advisory_lock(lock_id)
    execute("SELECT pg_advisory_lock(#{lock_id})")
    yield
  ensure
    execute("SELECT pg_advisory_unlock(#{lock_id})")
  end

The private method included in ActiveRecord has a very generic name and can easily conflict. Is it possible to give it a more specific name?

ActiveRecord::Base.send(:impl_class) #=> WithAdvisoryLock::PostgreSQL

Hello everyone,

As part of our ongoing efforts to improve the with_advisory_lock gem, we are planning some significant changes in the upcoming release, which we wanted to share with you in advance to help you prepare for the transition.

Enhanced Multi-Database Support
This update aims to provide cleaner implementation by injecting the need code into the adapters directly.

Dropping Support for SQLite3
We have decided to drop support for SQLite3 in the next major release of with_advisory_lock. PostgreSQL and MySQL provide the capability natively, and this will allow us to simplify the codebase.

Single-node SQLite consumers can substitute this library with a mutex.

Support for ActiveRecord 7+
With the incoming release of a new Rails version and bi g refactoring going on in rails/rails, it will be more beneficial to drop support for legacy version.

Action Required
If you are currently using SQLite3 with with_advisory_lock, we recommend transitioning to a supported database system such as PostgreSQL or MySQL before the next release or lock the version.

We understand that these changes may require adjustments in your applications, and we are here to help.

Best regards,
Seuros

cc @mceachen

Currently the postgres adapter uses connection#select_value which looking at the documentation the call chain is :

select_value -> select_one -> select_all

select_all "... returns an array of hashes ..."
select_one "... selects first hash of array ..."
select_value " ... selects first value of hash ..."

This means that when selecting more than one value in a statement you need to explictly request which column you want.

In some Rubies maybe select_value always returns the output of the lock functions but at least in REE/MRI 1.8 and jRuby in 1.8 mode where I tested this is not true.

The fix is simple - use execute and retrieve the value ( PR coming ) or change the SQL to only select the lock function result.

https://www.percona.com/doc/percona-xtradb-cluster/5.5/limitation.html
https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

It would be a good idea to mention this in the README, we just found out the hard way.

Looks like a new method with_advisory_lock! was added which raises an error if a lock failed to be acquired but it's not yet available on the latest version. Is the latest code stable enough to cut a release for that?

Thanks.

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.

I believe select get_version() gets called on every lock - is that necessary?