How to check database locks

Hold a write transaction in one process, then try to write from another. SQLite answers database is locked, error code 5, in under a millisecond. Set PRAGMA busy_timeout on the second connection and the same attempt waits instead: 2379 ms here, then succeeds when the first process commits.

Why check this

Lock errors are the defect that only appears with two users. A test suite that runs one request at a time never sees them, and the first burst on staging produces a page of them. Run this when a service gets a second worker, when a nightly job starts overlapping with traffic, and whenever a log shows an error that a retry makes disappear.

The failure it catches is the write that is dropped without a trace. A handler catches the lock error, logs a warning, returns 200, and the row is never written. The user sees a saved form and the table has nothing in it.

Prerequisites

• A sqlite3 command line client. The shell here is the Android platform-tools build, 3.50.6, 32-bit.
• Node 22, which bundles node:sqlite over SQLite 3.51.3. The two sides of every test below are one shell process and one Node process, so the lock is a real file lock between two clients rather than two handles inside one program.
• The locking documentation and the busy_timeout pragma.
• A row to contend over. Save as schema.sql, and recreate the database before each run.

CREATE TABLE orders (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE, total INTEGER NOT NULL);
INSERT INTO orders (ref, total) VALUES ('A-1000', 500);

• The process that holds the lock. Save as hold.sh and make it executable.

#!/bin/sh
# Hold a write lock on shop.db from the sqlite3 shell for $1 seconds, then commit.
{ echo ".bail on"
  echo "BEGIN IMMEDIATE;"
  echo "UPDATE orders SET total = 900 WHERE ref = 'A-1000';"
  echo "SELECT 'shell: uncommitted UPDATE in place';"
  sleep "$1"
  echo "COMMIT;"
  echo "SELECT 'shell: COMMIT returned without error';"
} | sqlite3 shop.db

• The process that wants the lock. Save as contender.mjs. It takes the busy timeout in milliseconds as its argument.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('shop.db');
const timeout = Number(process.argv[2]);
db.exec(`PRAGMA busy_timeout = ${timeout}`);
const t0 = Date.now();
try {
  db.exec('BEGIN IMMEDIATE');
  db.exec("UPDATE orders SET total = total + 100 WHERE ref = 'A-1000'");
  db.exec('COMMIT');
  console.log('contender  busy_timeout=%d  acquired after %d ms', timeout, Date.now() - t0);
} catch (err) {
  console.log('contender  busy_timeout=%d  failed after %d ms: %s [%s / errcode %d]',
    timeout, Date.now() - t0, err.message, err.code, err.errcode);
}
db.close();

Steps

1. 1

   Step 1.

   Take the lock in one process and ask for it from the other with no patience at all.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning contender.mjs 0; wait )
   

   shell: uncommitted UPDATE in place
   contender  busy_timeout=0  failed after 0 ms: database is locked [ERR_SQLITE_ERROR / errcode 5]
   shell: COMMIT returned without error

   Error code 5 is SQLITE_BUSY, and it arrived in 0 ms. A probe with a zero timeout is the cheapest way to answer whether a database is locked right now.

2. 2

   Step 2.

   Read the lock off the filesystem while it is held.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && ( ./hold.sh 3 & sleep 1; ls shop.db*; wait; ls shop.db* )
   

   shell: uncommitted UPDATE in place
   shop.db
   shop.db-journal
   shell: COMMIT returned without error
   shop.db

   The -journal file exists only while a write transaction is open in rollback journal mode. It disappears at COMMIT. A -wal and -shm pair instead means the database is in write-ahead logging mode, and those two stay between transactions.

3. 3

   Step 3.

   Give the second process a timeout longer than the first one holds.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning contender.mjs 5000; wait )
   

   shell: uncommitted UPDATE in place
   shell: COMMIT returned without error
   contender  busy_timeout=5000  acquired after 2379 ms

   The write went through, 2379 ms late. That is the shell's 3 second hold minus the 0.7 second head start the contender was given. A busy timeout turns an instant failure into latency, and the latency is the holder's transaction length.

4. 4

   Step 4.

   Give it a timeout shorter than the hold.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && ( ./hold.sh 3 & sleep 0.7; node --disable-warning=ExperimentalWarning contender.mjs 1000; wait )
   

   shell: uncommitted UPDATE in place
   contender  busy_timeout=1000  failed after 1156 ms: database is locked [ERR_SQLITE_ERROR / errcode 5]
   shell: COMMIT returned without error

   Same message and same code as step 1, arriving 1156 ms later. The timeout is a floor, not a deadline: SQLite sleeps in increasing steps and checks the clock between them, so a 1000 ms setting overran by 156 ms.

5. 5

   Step 5.

   Read first, write later, and watch the timeout do nothing. Save as upgrader.mjs.

   import { DatabaseSync } from 'node:sqlite';
   const db = new DatabaseSync('shop.db');
   db.exec('PRAGMA busy_timeout = 10000');      // ten seconds of patience
   db.exec('BEGIN');                            // deferred: read first, write later
   const before = db.prepare("SELECT total FROM orders WHERE ref='A-1000'").get().total;
   console.log('upgrader  read %d, snapshot open', before);
   const t0 = Date.now();
   setTimeout(() => {
     try {
       db.exec(`UPDATE orders SET total = ${before + 100} WHERE ref = 'A-1000'`);
       db.exec('COMMIT');
       console.log('upgrader  UPDATE ok after %d ms', Date.now() - t0);
     } catch (err) {
       console.log('upgrader  UPDATE failed after %d ms: %s [errcode %d]',
         Date.now() - t0, err.message, err.errcode);
     }
     db.close();
   }, 4000);
   

   Start the reader first, let the shell commit during its snapshot, and let the write land afterwards.

   rm -f shop.db* && sqlite3 shop.db < schema.sql && sqlite3 shop.db "PRAGMA journal_mode=WAL;" > /dev/null && ( node --disable-warning=ExperimentalWarning upgrader.mjs & sleep 0.7; ./hold.sh 1; wait )
   

   upgrader  read 500, snapshot open
   shell: uncommitted UPDATE in place
   shell: COMMIT returned without error
   upgrader  UPDATE failed after 4010 ms: database is locked [errcode 517]

   Nothing held a lock when that UPDATE ran. The shell committed two seconds earlier. The extended code 517 is SQLITE_BUSY_SNAPSHOT: the snapshot this transaction has been reading is older than the committed state, so the write can never be applied and waiting would not help. The 10 second timeout was never used.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | database is locked, errcode 5, in 0 ms | Another writer holds the lock and this connection has no timeout | Set PRAGMA busy_timeout on every connection that writes. | | The same error after roughly your timeout | The holder outlived the timeout | Shorten the holding transaction before raising the timeout. | | database is locked, errcode 517 | A read-then-write transaction whose snapshot went stale | Open with BEGIN IMMEDIATE and retry the whole transaction, not the statement. | | A -journal file that never disappears | A process died mid-write and left a hot journal | The next connection replays it. Check that nothing is still holding the file open. | | database table is locked | Two cursors inside one connection, not two processes | A different fault. Close the open statement before writing to the same table. |

Common mistakes

What to check next

• How to check transaction isolation level: why the journal mode changes which side is blocked.
• How to check transaction rollback: what the failed writer is left holding after the error.
• How to test connection pool exhaustion: how one long lock becomes a timeout on every request.
• How to find slow queries: the transaction that holds the lock for too long.
• How to check database integrity: what to run after a process died holding a write.

FAQ

How do I check if a database is locked without waiting?

Open a connection, set PRAGMA busy_timeout = 0, and issue BEGIN IMMEDIATE. It returns error code 5 at once when a writer holds the lock, and it commits nothing when it succeeds. Step 1 is that probe.

Which process is holding the lock?

SQLite keeps no lock table, so the database cannot tell you. Find it from outside: the -journal file in step 2 says a write transaction is open, and the operating system can name the processes with the database file open.

Does WAL mode remove lock errors?

It removes the reader against writer collisions. Two writers still take turns, and WAL adds code 517 for a transaction that read before it wrote. Step 5 ran in WAL mode and still failed.

Is a busy timeout enough for production?

It converts a burst of short overlaps into waits, which is worth having. It cannot rescue a transaction that stays open across a network call or a user prompt, and it never helps with 517. Keep write transactions short and open them with BEGIN IMMEDIATE.

Verified