Posted about 2 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.

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Author of this card:

Henning Koch
Last edit:
about 2 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 makandropedia