busy_timeout is seemingly ignored (interleaving transactions will always fail)

[andymitchell]

Description

The default behaviour in Sqlite is that if a transaction is running, and another transactions starts, the second one returns an immediate error of "database is locked".

But you can make it auto retry if busy_timeout is set. In BetterSqlite3 the engine will keep retrying until the database becomes writeable again. It's more elegant than bespoke handling in app code.

This doesn't appear to be supported by @libsql. Is there an intention to add it? Could that decision be added to the docs so a search on 'busy_timeout' would help people find an answer online?

Environment

@libsql/client: 0.14.0
vitest: 2.1.8
typescript: 5.7.2
node: 20.15.1

Steps to reproduce

Run this code in Vitest/Jest/etc. to see it fail

import { createClient } from "@libsql/client"; // "^0.14.0"



test(`LibSql fails at concurrent transactions even with busy_timeout`, async () => {

    const url = `file:local.db`

    // Turn on WAL mode. Speeding things up, making a transaction collision less likely. 
    /*
    // Disabled, because it makes no difference to the test
    const preClient = createClient({
        url
    });
    await preClient.execute('PRAGMA journal_mode = WAL;'); // Speeds things up, so collisions less likely. Makes no difference to the transactions failing. 
    preClient.close();
    */

    const client = createClient({
        url
    });

    // This should make Sqlite retry when it encounters a lock; but it's not having an impact. https://www.sqlite.org/c3ref/busy_timeout.html 
    // In contrast, this logic works with BetterSqlite3. 
    await client.execute('PRAGMA busy_timeout = 5000;'); 

    await client.execute(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL
        )
    `);

    // Wrap a transaction in a promise, so it can run concurrently
    const txPath1 = new Promise<void>(async (accept, reject) => {
        try {
            const tx = await client.transaction('write');
            await tx.execute({ sql: 'INSERT INTO users (name) VALUES (?)', args: ['Alice'] });
            await tx.execute({ sql: 'INSERT INTO users (name) VALUES (?)', args: ['Bob'] });
            await tx.commit()
            accept();
        } catch (e) {
            reject(e);
        }

    });

    // await txPath1; // If uncommented, this succeeds as it makes it run linear

    // Wrap a transaction in a promise, so it can run concurrently
    const txPath2 = new Promise<void>(async (accept, reject) => {
        try {
            const tx2 = await client.transaction('write'); // Throws error here: "SqliteError: database is locked" / { code: 'SQLITE_BUSY', rawCode: 5 }
            await tx2.execute({ sql: 'INSERT INTO users (name) VALUES (?)', args: ['Charleen'] });
            await tx2.execute({ sql: 'INSERT INTO users (name) VALUES (?)', args: ['David'] });
            await tx2.commit()
            accept();
        } catch (e) {
            reject(e);
        }

    });

    // Wait for the two transactions paths to complete
    await Promise.all([txPath1, txPath2]);

    // Verify the data
    const resultFinal = await client.execute('SELECT * FROM users');
    expect(resultFinal.rows.length).toBe(4);

})

Expected Behaviour

The second transaction automatically retries because "busy_timeout" is set at await client.execute('PRAGMA busy_timeout = 5000;')

It inserts 4 rows, and returns them.

Actual Behaviour

It immediately crashes on const tx2 = await client.transaction('write'); with the error "database is locked".

Related

#104