Locking Behavior

Isolation levels say what will be visible; they do not say what that costs. The cost is that transactions touching the same data have to wait for each other. The mechanism that governs the waiting is the lock: a transaction acquires a right on a resource before touching it, holds that right until its work is done, and then releases it.

This lesson defines lock types, produces a real conflict with two connections, and reads the error the engine gives.

Two Kinds of Locks

The basic model rests on two kinds of locks.

A shared lock is for reading. Multiple shared locks can sit on the same resource at once; readers do not block each other.

An exclusive lock is for writing. If a resource carries an exclusive lock, neither another shared lock nor another exclusive lock can be placed on it.

The compatibility rule fits in one sentence: reading does not block reading, writing blocks everything. Engines using multi-version concurrency control relax this rule on the read side — readers see an older version, so they do not wait on the writer. That is an implementation choice covered in the Relational Database Administration course.

What a lock is placed on is a separate axis: a row, a page, a table, or the whole database. Finer granularity increases concurrency but grows the number of locks and the bookkeeping behind them. The engine in the observation below locks the entire database for writes; on an engine using row-level locking, two transactions writing to different rows in the same scenario would not block each other. This is engine-dependent behavior, and it needs to be known when writing portable code.

A Real Conflict

The script below opens two connections to the same file. The first connection opens a write transaction and keeps it open; the second tries to write:

rm -f lock.db lock.db-wal lock.db-shm
cat > lock.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const a = new DatabaseSync('lock.db');
const b = new DatabaseSync('lock.db');
a.exec('PRAGMA journal_mode = WAL');
a.exec(`CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT);
        INSERT INTO book VALUES (1,'Lost Time',1),(2,'Silent House',1);`);

a.exec('BEGIN IMMEDIATE');
a.exec('UPDATE book SET on_shelf = 0 WHERE id = 1');
console.log('A: write transaction open, book 1 updated');

try {
  b.exec('BEGIN IMMEDIATE');
  b.exec('UPDATE book SET on_shelf = 0 WHERE id = 2');
  console.log('B: updated');
} catch (error) {
  console.log('B got an error:', error.code, '-', error.message);
}

console.log('can B still read:',
            b.prepare('SELECT on_shelf FROM book WHERE id = 1').get().on_shelf);
a.exec('COMMIT');
console.log('A committed; B can write now');
b.exec('BEGIN IMMEDIATE');
b.exec('UPDATE book SET on_shelf = 0 WHERE id = 2');
b.exec('COMMIT');
console.log('final state:', JSON.stringify(a.prepare(
  'SELECT id, on_shelf FROM book ORDER BY id').all()));
JS
node lock.mjs
rm -f lock.db lock.db-wal lock.db-shm

A: write transaction open, book 1 updated
B got an error: ERR_SQLITE_ERROR - database is locked
can B still read: 1
A committed; B can write now
final state: [{"id":1,"on_shelf":0},{"id":2,"on_shelf":0}]

Three things are worth noting.

First, B wanted to write to the second book while A had written to the first. It was still blocked: on this engine, the write lock is database-wide. On an engine using row-level locking, this attempt would have succeeded.

Second, the error message reports rejection, not waiting. Because the lock timeout was zero, the engine returned immediately instead of joining a queue. The message’s shape is engine-specific; what is common is that the conflict reaches the application as an error.

Third, B could not write but could read, and it saw the old value, not the one A had not yet committed. The reader making progress independently of the writer is the lock-side counterpart of the snapshot behavior from the previous lesson.

Once A committed, B’s same write went through without issue. A conflict is not a permanent block; it is a timing-dependent race.

Lock Upgrade Conflict

A more subtle case is two transactions that both read first and write second. Both start with a read right, then both want to upgrade to a write right. If one upgrades, the other’s request cannot be satisfied:

rm -f lock2.db lock2.db-wal lock2.db-shm
cat > upgrade-conflict.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const a = new DatabaseSync('lock2.db');
const b = new DatabaseSync('lock2.db');
a.exec('PRAGMA journal_mode = WAL');
a.exec(`CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT);
        INSERT INTO book VALUES (1,'Lost Time',1),(2,'Silent House',1);`);

a.exec('BEGIN');
b.exec('BEGIN');
console.log('A read:', a.prepare('SELECT on_shelf FROM book WHERE id = 1').get().on_shelf);
console.log('B read:', b.prepare('SELECT on_shelf FROM book WHERE id = 1').get().on_shelf);

a.exec('UPDATE book SET on_shelf = 0 WHERE id = 1');
console.log('A wrote');
try {
  b.exec('UPDATE book SET on_shelf = 0 WHERE id = 1');
  console.log('B wrote');
} catch (error) {
  console.log('B got an error:', error.code, '-', error.message);
}
b.exec('ROLLBACK');
a.exec('COMMIT');
console.log('final state:', JSON.stringify(a.prepare(
  'SELECT id, on_shelf FROM book ORDER BY id').all()));
JS
node upgrade-conflict.mjs
rm -f lock2.db lock2.db-wal lock2.db-shm

A read: 1
B read: 1
A wrote
B got an error: ERR_SQLITE_ERROR - database is locked
final state: [{"id":1,"on_shelf":0},{"id":2,"on_shelf":1}]

Both transactions saw the book on the shelf; both wanted to lend it. Without isolation, both would have succeeded, and the book would have appeared to be lent to two members at once — the lost update from the previous lesson. The lock prevented that: one won, the other got an error.

The correct behavior for the side that got the error is to roll back and retry. On retry, the read fetches the current value and shows that the book is no longer on the shelf. Resolving the conflict is completed by the application writing this retry loop; all the engine does on its own is not leave the conflict silent.

The standard way to build this pattern without the error is to do the read with write intent from the start. Standard SQL defines a form for this that locks the rows it reads:

SELECT on_shelf FROM book WHERE id = 1 FOR UPDATE;

This form takes an exclusive right on the row while reading it; the second transaction enters a wait during the read step, and the upgrade conflict never occurs. The engine in the observation above does not recognize this clause; its counterpart is opening the transaction with write intent through BEGIN IMMEDIATE. The statement itself was not run here; only its form is shown.

Deadlock

If two transactions wait on resources held by each other, neither can proceed. This is called a deadlock, and its simplest form arises with two resources:

Step	Transaction A	Transaction B
1	locks the book row	—
2	—	locks the member row
3	requests and waits on the member row	—
4	—	requests and waits on the book row

After the fourth step there is a cycle in the wait-for graph, and no wait ends on its own. Engines resolve this in two ways. Engines that detect deadlocks scan the wait-for graph, pick a victim transaction when a cycle is found, and roll it back; the victim gets a deadlock error. Engines without detection set a lock timeout; when the time runs out, the waiting transaction gets an error. In the second approach, a genuine deadlock cannot be told apart from a merely slow transaction.

No cycle formed in the observation above, because that engine allows only one writer at a time; the second writer is rejected before it even starts waiting. Deadlock is a problem for engines that hold locks at a finer granularity — more the cost of more concurrency.

Prevention works by keeping the cycle from forming:

• Touching resources in the same order every time. If both transactions lock book before member, the third and fourth steps go in the same direction and the cycle never closes.
• Keeping transactions short. The shorter the lock is held, the lower the chance of conflict.
• Acquiring all locks up front where possible, instead of escalating rights as work proceeds.

These three do not eliminate the conflict, they lower its odds. Because the chance of an error remains, every transaction that might conflict needs to be written so it can be retried: running the same transaction a second time must not produce an additional side effect, meaning it has to be idempotent.

Lock Timeout

The choice between rejecting and waiting is a setting. If a lock timeout is given, a conflicting transaction waits for the lock to free up for the stated duration instead of getting an error immediately:

rm -f lock3.db lock3.db-wal lock3.db-shm
cat > wait-timeout.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const a = new DatabaseSync('lock3.db');
const b = new DatabaseSync('lock3.db', { timeout: 250 });
a.exec('PRAGMA journal_mode = WAL');
a.exec(`CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT);
        INSERT INTO book VALUES (1,'Lost Time',1);`);

a.exec('BEGIN IMMEDIATE');
a.exec('UPDATE book SET on_shelf = 0 WHERE id = 1');

const start = process.hrtime.bigint();
try {
  b.exec('BEGIN IMMEDIATE');
} catch (error) {
  const elapsed = Number(process.hrtime.bigint() - start) / 1e6;
  console.log('B got an error:', error.code);
  console.log('is the wait past the 250 ms limit:', elapsed >= 250);
}
a.exec('COMMIT');
JS
node wait-timeout.mjs
rm -f lock3.db lock3.db-wal lock3.db-shm

B got an error: ERR_SQLITE_ERROR
is the wait past the 250 ms limit: true

The measured duration itself depends on the machine and its load, so it was not printed; what was printed is whether the limit was exceeded. The result is the same error as in the first observation — the difference is that the error arrives not immediately, but after the stated duration has been waited out.

Choosing the limit is a trade-off. A short limit reports conflict quickly and lets the application retry; a long limit hides transient conflicts from the application entirely but extends resource holding in a genuine deadlock. Both rest on the assumption that transactions are kept short.

Summary

• A shared lock is for reading, an exclusive lock is for writing; reading does not block reading, writing blocks everything.
• Lock granularity ranges from row to database; finer granularity increases concurrency and grows the bookkeeping.
• In the observation, the second writer got a database is locked error but could still read; a conflict is not a permanent block, it is a timing-dependent race.
• Two transactions that read then write produce a lock upgrade conflict; the standard fix is locking the read with write intent, the application fix is rolling back and retrying.
• A deadlock is a cycle forming in the wait-for graph; touching resources in the same order, keeping transactions short, and writing retry-safe code are preventive habits.

Next Step

Up to this lesson, the transaction’s boundary, what to do on error, and retrying were all decided by application code; the database only took the statements it was given. Some of this logic can also be defined inside the database itself: the lending rule can be put into a procedure that the application triggers with a single call. The next lesson covers the standard form of procedures and functions that run on the server side, what they provide, and the trade-offs they bring.