Drizzle Team•6mo ago

LibsqlError: SQLITE_BUSY: database is locked

I am getting a LibsqlError: SQLITE_BUSY: database is locked error on my insert function here with LibSQL/Turso as my SQLite driver

export async function putClientGroup(
  clientGroup: ClientGroupRecord,
): Promise<void> {
  const db = await getDB()
  const { id, cvrVersion, clientGroupVersion } = clientGroup
  const insertClientGroupStatementQuery = db
    .insert(replicacheClientGroup)
    .values({
      id,
      cvrVersion,
      clientGroupVersion,
      lastModified: new Date(),
    })
    .onConflictDoUpdate({
      target: replicacheClientGroup.id,
      set: {
        cvrVersion,
        clientGroupVersion,
        lastModified: new Date(),
      },
    })
    .prepare()

  await insertClientGroupStatementQuery.run()
}

export async function putClientGroup(
  clientGroup: ClientGroupRecord,
): Promise<void> {
  const db = await getDB()
  const { id, cvrVersion, clientGroupVersion } = clientGroup
  const insertClientGroupStatementQuery = db
    .insert(replicacheClientGroup)
    .values({
      id,
      cvrVersion,
      clientGroupVersion,
      lastModified: new Date(),
    })
    .onConflictDoUpdate({
      target: replicacheClientGroup.id,
      set: {
        cvrVersion,
        clientGroupVersion,
        lastModified: new Date(),
      },
    })
    .prepare()

  await insertClientGroupStatementQuery.run()
}

This error occurs after refactoring my driver from better-sqlite3 to LibSQL / Turso. My application ran without the error with the relatively same code here. The only difference two differences with the LibSQL / Turso driver is that we await to get the db and .run() the query statement. In both the working better-sqlite3 driver and non-working turso driver, we have this function called from within a db.transaction(). Then pullForChanges calls the function with await putClientGroup(nextClientGroupRecord)

  const {
    nextCVRVersion,
    nextCVR,
    clientChanges,
    lists,
    shares,
    todos,
  } = await db.transaction(async () => pullForChanges(
    clientGroupID,
    baseCVR,
    userID,
    replicacheCookie,
  ))

  const {
    nextCVRVersion,
    nextCVR,
    clientChanges,
    lists,
    shares,
    todos,
  } = await db.transaction(async () => pullForChanges(
    clientGroupID,
    baseCVR,
    userID,
    replicacheCookie,
  ))

4 Replies

christradingOP•6mo ago

I passed down tx and used that instead of db for all my drizzle queries, and that solved my error on my database POST requests 95% of the time. The LibsqlError: SQLITE_BUSY: database is locked still happens once in a while but I can't pinpoint from where and why. But at least now it's failing and erroring 100% of the time

camiaei•6mo ago

i'm seeing this error while using better-sqlite3. in my case it seems that the error appears when i have increased the amount of simultaneous read/write operations.

Mozart's_Ghost•6mo ago

https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions "If the first statement after BEGIN DEFERRED is a SELECT, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY"

glomar•2mo ago

by default sqlite uses a busy timeout of 0. I've used this before I run a transaction to set the wait time.

await db.run(sql`PRAGMA busy_timeout = 10000;`);
await db.transaction(async () => {
... rest of transaction code ...

await db.run(sql`PRAGMA busy_timeout = 10000;`);
await db.transaction(async () => {
... rest of transaction code ...

Gaming

Programming

LibsqlError: SQLITE_BUSY: database is locked