Read more

How to coordinate distributed work with MySQL's GET_LOCK

Henning Koch
February 23, 2015Software engineer at makandra GmbH

The linked article Show archive.org snapshot explains how to get a database-wide lock without creating table rows:

This article explains how I replaced file-based methods to ensure only one running instance of a program with MySQL’s GET_LOCK function. The result is mutual exclusivity that works in a distributed environment, and it’s dead simple to implement.

Ruby implementation

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

An implementation as a Rubygem seems to be with_advisory_lock Show archive.org snapshot . It has support for MySQL, PostgreSQL and SQLite.

Unfortunately, it has a horrible caveat in MySQL Show archive.org snapshot :

With MySQL (at least <= v5.5), if you ask for a different advisory lock within a with_advisory_lock block, you will be releasing the parent lock (!!!). A NestedAdvisoryLockError will be raised in this case. If you ask for the same lock name, with_advisory_lock won't ask for the lock again, and the block given will be yielded to.

If you're using MySQL, maybe you'd like to use our table-backed lock instead.

Posted by Henning Koch to makandra dev (2015-02-23 15:08)