SQLITE_BUSY with concurrent transactions is expected behavior?

[Funecio-Agrante]

I'm running 2-3 concurrent processes reading and writing to the same database. With individual INSERT or UPDATE I wasn't getting any issues, I guess the internal queue was handling the concurrency? But now I'm trying to wrap some of the database interactions in transaction functions db.transaction((data) => {..}, but the transactions fail sometimes with SQLITE_BUSY error. Is this expected behavior and I have to wrap some retry logic around the transaction function, like this #155 (comment)?

Does the issue #1067 apply to what I'm describing?

[Prinzhorn]

Edit: before you read the below: are you using WAL? (db.pragma('journal_mode = WAL');). See https://www.sqlite.org/wal.html ("9. Sometimes Queries Return SQLITE_BUSY In WAL Mode"). You definitely want WAL.

I guess the internal queue was handling the concurrency?

There is no such thing as an "internal queue", you were probably just lucky. Your transactions now do multiple reads/writes whereas before you only had transactions with a single read/write (every statement in SQLite is in an implicit transaction). It's just a matter of chance that this now comes up more frequently, because you are locking the db for longer.

Is this expected behavior

Yes, see https://www.sqlite.org/rescode.html#busy (emphasis mine)

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.
[...] process B will get back an SQLITE_BUSY result because SQLite only supports one writer at a time

Edit: proof that a single write can trigger SQLITE_BUSY:

Run this index.mjs twice in two terminals

import Database from 'better-sqlite3'

const db = new Database('./foo.db');

// Won't SQLITE_BUSY with WAL
// db.pragma('journal_mode = WAL');

db.exec('CREATE TABLE IF NOT EXISTS foo (bar)');

const stmt = db.prepare('INSERT INTO foo (bar) VALUES (?)');

while(1) {
  console.log('inserting...');
  stmt.run(Math.random());
}