ext/sqlite3: two SQLite3 connections in one PHP process cannot overlap BEGIN CONCURRENT writes with WAL2 mode

[Ali-007]

Description

The following code:

<?php

declare(strict_types=1);

/*
 * Reproducer for ext/sqlite3 with a custom SQLite Bedrock/WAL2 build.
 *
 * Environment used:
 *   PHP: 8.5.2
 *   OS: macOS
 *   SQLite runtime: 3.52.0 custom Bedrock/WAL2 build
 *
 * SQLite source id:
 *   2026-01-12 17:18:58 d577b2a2b2b04ff7b7b07c0fce6c9e3a910ae0a2a46d3524e688c1530155e3c2
 *
 * Relevant SQLite compile options:
 *   DEFAULT_LOCKING_MODE=1
 *   DEFAULT_WAL_AUTOCHECKPOINT=2500
 *   DEFAULT_WAL_SYNCHRONOUS=1
 *   DQS=0
 *   ENABLE_UNLOCK_NOTIFY
 *   THREADSAFE=1
 *   WAL2
 *
 * Note:
 *   I know BEGIN CONCURRENT and WAL2 are SQLite branch/custom-build features,
 *   not stock SQLite features. I am reporting this because the same SQLite
 *   library behaves differently when used from C/pthreads or PHP multiprocess,
 *   but fails when two SQLite3 connections are used inside one PHP process.
 */

if (!extension_loaded('sqlite3')) {
    fwrite(STDERR, "sqlite3 extension is not loaded\n");
    exit(1);
}

$dbFile = '/tmp/php-sqlite3-begin-concurrent-repro.sqlite3';

foreach ([$dbFile, "$dbFile-journal", "$dbFile-wal", "$dbFile-wal2", "$dbFile-shm"] as $file) {
    if (is_file($file)) {
        unlink($file);
    }
}

function open_db(string $path): SQLite3
{
    $db = new SQLite3($path, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
    $db->enableExceptions(true);

    /*
     * This is needed for my current SQLite build because it reports
     * DEFAULT_LOCKING_MODE=1. It avoids a separate prepare-time locking issue.
     * The issue reported here still happens with locking_mode=NORMAL.
     */
    $db->exec('PRAGMA locking_mode = NORMAL');

    $db->busyTimeout(250);
    $db->exec('PRAGMA busy_timeout = 250');
    $db->exec('PRAGMA wal_autocheckpoint = 0');

    return $db;
}

function step(string $label, callable $fn, ?SQLite3 $db = null): bool
{
    try {
        $fn();
        echo "[OK]   {$label}\n";
        return true;
    } catch (Throwable $e) {
        $code = $db ? $db->lastErrorCode() : $e->getCode();
        $xcode = ($db && method_exists($db, 'lastExtendedErrorCode'))
            ? $db->lastExtendedErrorCode()
            : $code;

        echo "[FAIL] {$label}: code={$code} xcode={$xcode} error="
            . get_class($e) . ': ' . $e->getMessage() . "\n";

        return false;
    }
}

function exec_insert(SQLite3Stmt $stmt, int $id, string $payload): void
{
    $stmt->reset();
    $stmt->clear();
    $stmt->bindValue(':id', $id, SQLITE3_INTEGER);
    $stmt->bindValue(':payload', $payload, SQLITE3_TEXT);

    $res = $stmt->execute();

    if ($res instanceof SQLite3Result) {
        $res->finalize();
    }
}

$version = SQLite3::version();

echo "PHP: " . PHP_VERSION . "\n";
echo "SQLite runtime: " . ($version['versionString'] ?? 'unknown') . "\n";

$setup = open_db($dbFile);

$setup->exec('PRAGMA journal_mode = DELETE');

$journal = strtolower((string)$setup->querySingle('PRAGMA journal_mode = wal2'));
echo "journal_mode: {$journal}\n";

if ($journal !== 'wal2') {
    throw new RuntimeException("Expected wal2, got {$journal}");
}

$setup->exec('PRAGMA synchronous = NORMAL');

/* Smoke test: make sure BEGIN CONCURRENT is accepted by the linked SQLite. */
$setup->exec('BEGIN CONCURRENT');
$setup->exec('ROLLBACK');

$setup->exec('CREATE TABLE t1(id INTEGER PRIMARY KEY, payload TEXT NOT NULL)');
$setup->exec('CREATE TABLE t2(id INTEGER PRIMARY KEY, payload TEXT NOT NULL)');
$setup->exec("INSERT INTO t1(id, payload) VALUES(1, 'seed')");
$setup->exec("INSERT INTO t2(id, payload) VALUES(1, 'seed')");

echo "setup locking_mode: " . strtolower((string)$setup->querySingle('PRAGMA locking_mode')) . "\n";

$setup->close();

$db1 = open_db($dbFile);
$db2 = open_db($dbFile);

echo "db1 locking_mode: " . strtolower((string)$db1->querySingle('PRAGMA locking_mode')) . "\n";
echo "db2 locking_mode: " . strtolower((string)$db2->querySingle('PRAGMA locking_mode')) . "\n";

$stmt1 = $db1->prepare('INSERT INTO t1(id, payload) VALUES(:id, :payload)');
$stmt2 = $db2->prepare('INSERT INTO t2(id, payload) VALUES(:id, :payload)');

if (!$stmt1 instanceof SQLite3Stmt || !$stmt2 instanceof SQLite3Stmt) {
    throw new RuntimeException('prepare failed');
}

$tx1 = false;
$tx2 = false;

step('db1 BEGIN CONCURRENT', function () use ($db1, &$tx1): void {
    $db1->exec('BEGIN CONCURRENT');
    $tx1 = true;
}, $db1);

step('db2 BEGIN CONCURRENT', function () use ($db2, &$tx2): void {
    $db2->exec('BEGIN CONCURRENT');
    $tx2 = true;
}, $db2);

step('db1 INSERT before any COMMIT', function () use ($stmt1): void {
    exec_insert($stmt1, 1000002, 'from db1');
}, $db1);

step('db2 INSERT while db1 transaction is still open', function () use ($stmt2): void {
    exec_insert($stmt2, 2000002, 'from db2');
}, $db2);

step('db1 COMMIT', function () use ($db1, &$tx1): void {
    $db1->exec('COMMIT');
    $tx1 = false;
}, $db1);

step('db2 COMMIT', function () use ($db2, &$tx2): void {
    $db2->exec('COMMIT');
    $tx2 = false;
}, $db2);

if ($tx1) {
    step('db1 ROLLBACK cleanup', function () use ($db1, &$tx1): void {
        $db1->exec('ROLLBACK');
        $tx1 = false;
    }, $db1);
}

if ($tx2) {
    step('db2 ROLLBACK cleanup', function () use ($db2, &$tx2): void {
        $db2->exec('ROLLBACK');
        $tx2 = false;
    }, $db2);
}

$stmt1->close();
$stmt2->close();
$db1->close();
$db2->close();

$check = open_db($dbFile);

echo "t1 rows: " . (int)$check->querySingle('SELECT count(*) FROM t1') . "\n";
echo "t2 rows: " . (int)$check->querySingle('SELECT count(*) FROM t2') . "\n";

$check->close();

Resulted in this output:

PHP: 8.5.2
SQLite runtime: 3.52.0
journal_mode: wal2
setup locking_mode: normal
db1 locking_mode: normal
db2 locking_mode: normal
[OK]   db1 BEGIN CONCURRENT
[OK]   db2 BEGIN CONCURRENT
[OK]   db1 INSERT before any COMMIT
[FAIL] db2 INSERT while db1 transaction is still open: code=5 xcode=5 error=SQLite3Exception: Unable to execute statement: database is locked
[OK]   db1 COMMIT
[FAIL] db2 COMMIT: code=5 xcode=5 error=SQLite3Exception: cannot commit transaction - SQL statements in progress
[OK]   db2 ROLLBACK cleanup
t1 rows: 2
t2 rows: 1

But I expected this output instead:

PHP: 8.5.2
SQLite runtime: 3.52.0
journal_mode: wal2
setup locking_mode: normal
db1 locking_mode: normal
db2 locking_mode: normal
[OK]   db1 BEGIN CONCURRENT
[OK]   db2 BEGIN CONCURRENT
[OK]   db1 INSERT before any COMMIT
[OK]   db2 INSERT while db1 transaction is still open
[OK]   db1 COMMIT
[OK]   db2 COMMIT
t1 rows: 2
t2 rows: 2

Additional notes:

I compiled SQLite (Bedrock branch) from source, then compiled PHP from source to use that custom built SQLite, also compiled Swoole from source.

This test uses one PHP process and two independent SQLite3 connections to the same database.

Both connections successfully execute BEGIN CONCURRENT. Each connection writes to a separate table, so there is no application-level row/table conflict. However, whichever connection writes first succeeds, and the second connection fails at INSERT with SQLITE_BUSY / "database is locked" while the first transaction is still open.

Reversing the write order produces the same behavior: db2 can write first, then db1 fails.

The same SQLite library/build behaves better when tested from C/pthreads and from PHP using multiple OS processes. The failure appears specific to using multiple ext/sqlite3 connections inside one PHP process.

This appears to be a bug or limitation in ext/sqlite3's handling of multiple SQLite3 connections/statements in one process.

The same issue happens when I want to make multiple connections to the same database in a Swoole's Coroutine context, so, even though the compiled SQLite allows write concurrency, but swoole's coroutines fail, because underlying ext/sqlite3 has this bug.

PHP Version

PHP 8.5.2 (cli) (built: Feb 10 2026 20:47:58) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.5.2, Copyright (c) Zend Technologies
    with Zend OPcache v8.5.2, Copyright (c), by Zend Technologies

Operating System

macOS