Skip to the stories
AI & ML

Symfony 8.2 Adds MySQL and MariaDB Advisory Locks

  • Javier Eguiluz
  • 965 reads

Symfony 8.2 adds advisory lock stores for MySQL and MariaDB, lets existing connections use them, and can remove flock files on release.

Symfony 8.2 Adds MySQL and MariaDB Advisory Locks

For years, PostgreSQL was the only database where Symfony's Lock component could use advisory locks. MySQL and MariaDB users had to store locks in a table and manage expiry themselves. Symfony 8.2 closes that gap with two new stores, and it adds a way to reuse an existing connection for advisory locking. Here's what changes in practice and where the trade-offs sit.

Why Advisory Locks Are Different

The Lock component offers many stores, from files and semaphores to Redis and relational databases. Database-backed stores fall into two camps. Table-based stores write a row per lock, so they need a schema, and they rely on a TTL to free locks left behind by crashed processes. Advisory locks work differently. The database server holds them in memory and ties each one to a connection. No table is required, and the server releases the lock as soon as that connection ends.

That lifecycle is the real benefit. With a table-based store, you pick a TTL, estimate how long your task runs, and refresh the lock during long jobs. Get the TTL wrong and you either hold a lock for too long after a crash or lose it mid-task. An advisory lock sidesteps that decision entirely.

MySQL and MariaDB Advisory Locks

Ryan Pon contributed the MySQL and MariaDB work in #64757 and #64987. The implementation uses MySQL user-level locks, and it ships as two stores. MysqlStore wraps a PDO connection, while DoctrineDbalMysqlStore wraps a Doctrine DBAL connection. You select them through the DSN scheme by adding +advisory:

# config/packages/lock.yaml
framework:
    # a Doctrine DBAL URL creates a DoctrineDbalMysqlStore
    lock: 'mysql+advisory://db_user:[email protected]:3306/app'

A PDO-style DSN such as mysql+advisory:host=127.0.0.1;dbname=app produces a MysqlStore instead. Outside a framework install, you build the store yourself and hand it to a LockFactory:

$store = new MysqlStore('mysql:host=127.0.0.1;dbname=app', [
    'db_username' => 'db_user',
    'db_password' => 'db_password',
]);
$lock = (new LockFactory($store))->createLock('invoice-'.$invoice->getId());

// waits until the lock is free, without polling the database
$lock->acquire(blocking: true);

Both stores support native blocking, so a waiting process sleeps on the database rather than retrying in a loop. Their locks never expire, which removes the TTL bookkeeping. The minimum versions are MySQL 5.7.5 or MariaDB 10.0.2.

One limitation deserves attention before you migrate. MySQL has no shared advisory locks, so acquireRead() takes an exclusive lock. If your code relies on many readers sharing a resource, that behaviour will serialize them. You'll need to restructure those paths or keep them on a store that supports shared locks.

Advisory Locks on an Existing Connection

MD. Saiful Islam Feroz and Nicolas Grekas contributed this change in #65021 and #65098. The problem they address is specific. The +advisory DSNs open their own database connection, but if you passed the service ID of an existing connection, you always received a table-based store, even when the database supported advisory locks.

Symfony 8.2 lets a lock store be defined as an array that points at a PDO or Doctrine DBAL service and sets the new advisory option:

# config/packages/lock.yaml
framework:
    lock:
        service_id: 'doctrine.dbal.lock_connection'
        advisory: true

Symfony inspects the connection, selects the matching store for PostgreSQL, MySQL or MariaDB, and throws an exception for databases that lack advisory locks. That fail-fast behaviour is useful, because a silent fallback to a table would reintroduce the TTL problem without anyone noticing. In standalone code, pass true as the new second argument of the store factory:

use Symfony\Component\Lock\Store\StoreFactory;

// it must be a positional argument: "advisory: true" doesn't work
$store = StoreFactory::createStore($connection, true);

The change comes with a caveat that matters for production. Advisory locks belong to the database session. If the connection closes or reconnects, the database releases every lock it held and says nothing to your application. That can happen through the doctrine_close_connection Messenger middleware, or when the server's idle timeout drops a quiet connection. Your code would keep believing it holds a lock it no longer has. The Symfony example sidesteps this by using a dedicated Doctrine connection named lock, not the default one. Treat that as a minimum. Check every place where connections get recycled before you point a lock store at a shared connection.

Removing Flock Files on Release

Jérôme Tamarelle contributed this one in #66526. The FlockStore creates one file per locked resource and keeps it after release, so the next acquisition can reuse it. That suits a small set of names locked repeatedly. It works less well when each order or user gets its own lock, because the lock directory slowly fills with thousands of files.

The new flock+remove DSN deletes the file when an exclusive lock is released:

# config/packages/lock.yaml
framework:
    # uses the same project-scoped directory as 'flock'
    lock: 'flock+remove'
    # or use your own directory
    # lock: 'flock+remove:///path/to/locks'

Standalone code uses the new removeOnRelease argument:

use Symfony\Component\Lock\Store\FlockStore;

$store = new FlockStore('/path/to/locks', removeOnRelease: true);

The trade-off is speed. Each acquisition has to create the file again, so repeatedly locking the same resource gets slower. That's why the behaviour is opt-in. There's also a consistency rule: every process sharing a lock directory must use the same setting. If one process deletes files while another keeps using them, two processes could end up holding the same lock at once.

What to Watch Next

My read is that the MySQL stores are the most significant addition here, because they make advisory locking available to a large group of applications that previously had no good option short of a lock table. The shared-read gap is the piece to watch. Until MySQL offers shared advisory locks, read-heavy workloads will need a different design or a different store.

The connection-based option needs the most careful rollout. Session-bound locks are efficient, but a silent release on reconnect is the kind of failure that only shows up under load. If you're on MySQL or MariaDB and currently use PdoStore or DoctrineDbalStore with a TTL, a practical first step is to run the advisory store in staging, then deliberately trigger reconnects and idle timeouts and confirm your code notices when a lock disappears.

Source: Javier Eguiluz · symfony.com

From the same set