Posted almost 3 years ago. Visible to the public. External content.

How to coordinate distributed work with MySQL's GET_LOCK

The linked article 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

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

Unfortunately, it has a horrible caveat in MySQL:

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.

Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Author of this card:

Henning Koch
Last edit:
almost 3 years ago
by Henning Koch
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev