Home

Awesome

Laravel Database Advisory Lock Build Status Coverage Status

Advisory Locking Features of Postgres/MySQL/MariaDB on Laravel

Requirements

PackageVersionMandatory
PHP<code>^8.0.2</code>
Laravel<code>^9.0 || ^10.0</code>
PHPStan<code>>=1.1</code>
RDBMSVersion
Postgres<code>>=9.1.14</code>
MySQL<code>>=5.7.5</code>
MariaDB<code>>=10.0.15</code>

Installing

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

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

Locking Methods

PostgresMySQL/MariaDB
Session-Level Locking
Transaction-Level Locking

Timeout Values

PostgresMySQLMariaDB
Timeout: 0 (default; immediate, no wait)
Timeout: positive-int
Timeout: negative-int (infinite wait)
Timeout: float

Caveats about Transaction Levels

Key Principle

Always avoid nested transactions when using advisory locks to ensure adherence to the S2PL (Strict 2-Phase Locking) principle.

Recommended Approach

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

[!NOTE] Transaction-Level Locks:
<ins>Acquire the lock at the transaction nesting level 1</ins>, then rely on automatic release mechanisms.

if (DB::transactionLevel() > 1) {
    throw new LogicException("Don't use nested transactions outside of this logic.");
}

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

[!NOTE] Session-Level Locks:
<ins>Acquire the lock at the transaction nesting level 0</ins>, then proceed to call DB::transaction() call.

if (DB::transactionLevel() > 0) {
    throw new LogicException("Don't use transactions outside of this logic.");
}

$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] Transaction-Level Locks:
Don't take transaction-level locks in nested transactions. They are unaware of Laravel's nested transaction emulation.

[!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.

Session ASession B
BEGIN
BEGIN
pg_advisory_lock(X)
pg_advisory_lock(X)
Fetch balance of User X<br>(Balance: 1000 USD)
Deduct 800 USD if balance permits<br>(Balance: 1000 USD → 200 USD)
pg_advisory_unlock(X)
Fetch balance of User X<br>(Balance: 1000 USD :heavy_exclamation_mark:)
Deduct 800 USD if balance permits<br>(Balance: 1000 USD → 200 USD :bangbang:)
COMMIT
pg_advisory_unlock(X)
Fetch balance of User X<br>(Balance: 200 USD)
COMMIT
Fetch balance of User X<br>(Balance: <ins>-600 USD</ins> :interrobang::interrobang::interrobang:)