README

Advisory Locking Features of Postgres/MySQL/MariaDB on Laravel

Requirements

Note

Older versions have outdated dependency requirements. If you cannot prepare the latest environment, please refer to past releases.

Installing

composer require mpyw/laravel-database-advisory-lock:^4.4

Basic usage

Important

The default implementation is provided by ConnectionServiceProvider, however, package discovery is not available. Be careful that you MUST register it in config/app.php by yourself.

<?php

return [

    /* ... */

    'providers' => [
        /* ... */

        Mpyw\LaravelDatabaseAdvisoryLock\ConnectionServiceProvider::class,

        /* ... */
    ],

];

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Database\ConnectionInterface;

// Session-Level Locking
$result = DB::advisoryLocker()
    ->forSession()
    ->withLocking('<key>', function (ConnectionInterface $conn) {
        // critical section here
        return ...;
    }); // no wait
$result = DB::advisoryLocker()
    ->forSession()
    ->withLocking('<key>', function (ConnectionInterface $conn) {
        // critical section here
        return ...;
    }, timeout: 5); // wait for 5 seconds or fail
$result = DB::advisoryLocker()
    ->forSession()
    ->withLocking('<key>', function (ConnectionInterface $conn) {
        // critical section here
        return ...;
    }, timeout: -1); // infinite wait (except MariaDB)

// Postgres only feature: Transaction-Level Locking (no wait)
$result = DB::transaction(function (ConnectionInterface $conn) {
    $conn->advisoryLocker()->forTransaction()->lockOrFail('<key>');
    // critical section here
    return ...;
});

Advanced Usage

Tip

You can extend Connection classes with AdvisoryLocks trait by yourself.

<?php

namespace App\Providers;

use App\Database\PostgresConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        Connection::resolverFor('pgsql', function (...$parameters) {
            return new PostgresConnection(...$parameters);
        });
    }
}

<?php

namespace App\Database;

use Illuminate\Database\PostgresConnection as BasePostgresConnection;
use Mpyw\LaravelDatabaseAdvisoryLock\AdvisoryLocks;

class PostgresConnection extends BasePostgresConnection
{
    use AdvisoryLocks;
}

Implementation Details

Key Hashing Algorithm

-- Postgres: int8
hashtext('<key>')

-- MySQL/MariaDB: varchar(64)
CASE WHEN CHAR_LENGTH('<key>') > 64
THEN CONCAT(SUBSTR('<key>', 1, 24), SHA1('<key>'))
ELSE '<key>'
END

• Postgres advisory locking functions only accept integer keys. So the driver converts key strings into 64-bit integers through hashtext() function.
  • An empty string can also be used as a key.
• MySQL advisory locking function accepts string keys but their length are limited within 64 chars. When key strings exceed 64 chars limit, the driver takes first 24 chars from them and appends 40 chars sha1() hashes.
  • MariaDB's limit is actually 192 bytes, unlike MySQL's 64 chars. However, the key hashing algorithm is equivalent.
  • MariaDB accepts an empty string as a key, but does not actually lock anything. MySQL, on the other hand, raises an error for empty string keys.
• With either hashing algorithm, collisions can theoretically occur with very low probability.

Locking Methods

• Session-Level locks can be acquired anywhere.
  • They can be released manually or automatically through a destructor.
  • For Postgres, there was a problem where the automatic lock release algorithm did not work properly, but this has been fixed in version 4.0.0. See #2 for details.
• Transaction-Level locks can be acquired within a transaction.
  • You do not need to and cannot manually release locks that have been acquired.

Timeout Values

• Postgres does not natively support waiting for a finite specific amount of time, but this is emulated by looping through a temporary function.
• MariaDB does not accept infinite timeouts. very large numbers can be used instead.
• Float precision is not supported on MySQL/MariaDB.

Caveats about Transaction Levels

Recommended Approach

When transactions and advisory locks are related, either locking approach can be applied.

Tip

For Postgres, always prefer Transaction-Level Locking.

Note

Transaction-Level Locks:
Ensure the current context is inside the transaction, then rely on automatic release mechanisms.

if (DB::transactionLevel() < 1) {
    throw new LogicException("Unexpectedly transaction is not active.");
}

DB::advisoryLocker()
    ->forTransaction()
    ->lockOrFail('<key>');
// critical section with transaction here

Note

Session-Level Locks:
Ensure the current context is outside the transaction, then proceed to call DB::transaction() call.

if (DB::transactionLevel() > 0) {
    throw new LogicException("Unexpectedly transaction is already active.");
}

$result = DB::advisoryLocker()
    ->forSession()
    ->withLocking('<key>', fn (ConnectionInterface $conn) => $conn->transaction(function () {
        // critical section with transaction here
    }));

Warning

When writing logic like this, DatabaseTruncation must be used instead of RefreshDatabase.

Considerations

Caution

Session-Level Locks:
Don't take session-level locks in the transactions when the content to be committed by the transaction is related to the advisory locks.

What would happen if we released a session-level lock within a transaction? Let's verify this with a timeline chart, assuming a READ COMMITTED isolation level on Postgres. The bank account X is operated from two sessions A and B concurrently.

Caution

Transaction-Level Locks: A transaction-level advisory lock is bound to the top-level transaction, not to a nested one. Postgres releases it only when the enclosing top-level transaction ends — transaction-level locks "behave more like regular lock requests" and "are held until the current transaction ends; there is no provision for manual release".

Laravel implements nested DB::transaction() calls with SAVEPOINTs, and this interacts with the rule above in a way that is easy to overlook. When a nested transaction commits (i.e. its savepoint is released), a lock acquired inside it is not released: it is promoted to the enclosing (sub)transaction and stays held until the outermost transaction finishes. The lock is released early only when the nested transaction is rolled back (ROLLBACK TO SAVEPOINT), because "if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to".

So you cannot scope a transaction-level lock to just an inner transaction; its lifetime always extends to the outermost one. This is still more reliable than a session-level lock — it can never outlive its transaction and it sidesteps the race condition shown in the session-level example above — but the trade-off is that the lock may be held longer than you intend when acquired inside nested transactions. If you specifically need a lock to be released at the boundary of an inner transaction, a transaction-level lock is not the right tool.