Optimistic and Pessimistic Locking

Isolation ensures that the data read is internally consistent. It does not guarantee the correctness of a write made on the basis of a read value.

If two members want to borrow the last remaining copy at the same time, both see “book on shelf.” What both of them see is true; the book really was on the shelf at the moment they read it. The problem arises in the gap between the decision and the write. This lesson builds two ways of closing that gap, actually produces the conflict, and measures the cost of each.

Lost Update

The schema holds a single book and a version column. The version column will not be used in the first section; it comes into play in the second.

# setup.sh — single-book race setup
rm -f library.db library.db-wal library.db-shm
sqlite3 library.db >/dev/null <<'SQL'
PRAGMA journal_mode = WAL;
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
                    status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan')),
                    version INTEGER NOT NULL DEFAULT 1);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, return_date TEXT);
INSERT INTO book VALUES (1,'Blindness','on_shelf',1);
SQL

// count.mjs — prints the open loan count and version for one book
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
const k = db.prepare("SELECT status, version FROM book WHERE book_id = 1").get();
const n = db.prepare("SELECT count(*) AS n FROM loan WHERE book_id = 1 AND return_date IS NULL").get().n;
console.log(`book status=${k.status} version=${k.version}  open loans=${n}`);

The first version reads, decides, writes. The write is inside a boundary; it follows the previous lesson’s rule. It still is not enough.

// unprotected.mjs — read, decide, write; nothing protects the gap
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 5000");
const memberId = Number(process.argv[2]);
const delayMs = Number(process.argv[3] ?? 120);

const book = db.prepare("SELECT status FROM book WHERE book_id = 1").get();
const end = Date.now() + delayMs;
while (Date.now() < end) { /* model of decision time */ }

if (book.status === "on_shelf") {
  db.exec("BEGIN IMMEDIATE");
  db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run();
  db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId);
  db.exec("COMMIT");
  console.log(`member ${memberId}: got the book`);
} else {
  console.log(`member ${memberId}: book on loan, rejected`);
}

sh setup.sh
node unprotected.mjs 4 & node unprotected.mjs 5 & wait
node count.mjs

member 4: got the book
member 5: got the book
book status=on_loan version=1  open loans=2

The single-copy book was issued to two members at once. This is the application-side form of the lost update defined in the Data Modeling and Relational Theory course: the second write went through even though it invalidated the fact the first write relied on. It is not enough for the read and the write to each be correct on their own; the assumption between them has to be tested for whether it still holds.

The delayMs loop is a model; it represents the fact that the decision is not instantaneous, and that time passes for rule checks and external validations. As the gap narrows, the conflict becomes rarer, but it does not disappear.

Optimistic Locking

The first solution does not try to prevent the conflict; it detects it when it happens. The record is taken together with the version it had at the moment it was read, and that version is placed in the write condition. If the version has changed, the write affects no row.

// optimistic.mjs — the read version goes into the write condition; the affected row count tells whether there was a conflict
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 5000");
const memberId = Number(process.argv[2]);
const delayMs = Number(process.argv[3] ?? 120);

const book = db.prepare("SELECT status, version FROM book WHERE book_id = 1").get();
const end = Date.now() + delayMs;
while (Date.now() < end) { /* model of decision time */ }

if (book.status !== "on_shelf") {
  console.log(`member ${memberId}: book on loan, rejected`);
} else {
  db.exec("BEGIN IMMEDIATE");
  const result = db.prepare(
    "UPDATE book SET status = 'on_loan', version = version + 1 WHERE book_id = 1 AND version = ?")
    .run(book.version);
  if (result.changes === 0) {
    db.exec("ROLLBACK");
    console.log(`member ${memberId}: conflict (read version ${book.version}, affected rows 0)`);
  } else {
    db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId);
    db.exec("COMMIT");
    console.log(`member ${memberId}: got the book (version ${book.version} -> ${book.version + 1})`);
  }
}

sh setup.sh
node optimistic.mjs 4 & node optimistic.mjs 5 & wait
node count.mjs

member 5: got the book (version 1 -> 2)
member 4: conflict (read version 1, affected rows 0)
book status=on_loan version=2  open loans=1

Which member wins changes from run to run; what does not change is that one wins and the other reports a conflict. The open loan count dropped to one.

The method’s operating principle rests on a single number: the affected row count. A value of one says the record was found in the state it was read in; a value of zero says someone else got in between. Because this number can be read from the driver, no additional infrastructure is required.

A last-modified timestamp can be used in place of the version column, but if the timestamp’s resolution is not fine enough, two changes carry the same value and the conflict slips through. An integer that only ever increases does not carry this risk.

What to do once a conflict is detected is a separate decision. The options are telling the user “the record has changed,” rereading and retrying the operation, or merging the two changes. In the loan example, the correct behavior is to retry; on the second attempt, the book will show as on loan, so the request is rejected properly.

Pessimistic Locking

The second solution never lets the conflict form at all. The record is locked before the decision is made; the second client either waits or is rejected.

// pessimistic.mjs — the write lock is held for the duration of the decision
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");
db.exec(`PRAGMA busy_timeout = ${process.env.WAIT_MS ?? 0}`);
const memberId = Number(process.argv[2]);
const delayMs = Number(process.argv[3] ?? 120);

try {
  db.exec("BEGIN IMMEDIATE");
} catch (h) {
  console.log(`member ${memberId}: could not get lock -> ${h.message}`);
  process.exit(0);
}
const book = db.prepare("SELECT status FROM book WHERE book_id = 1").get();
const end = Date.now() + delayMs;
while (Date.now() < end) { /* model of decision time */ }

if (book.status === "on_shelf") {
  db.prepare("UPDATE book SET status = 'on_loan', version = version + 1 WHERE book_id = 1").run();
  db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId);
  db.exec("COMMIT");
  console.log(`member ${memberId}: got the book`);
} else {
  db.exec("COMMIT");
  console.log(`member ${memberId}: book on loan, rejected`);
}

With the wait time at zero, the second client does not wait for the lock at all.

sh setup.sh
node pessimistic.mjs 4 & sleep 0.05; node pessimistic.mjs 5 & wait
node count.mjs

member 5: could not get lock -> database is locked
member 4: got the book
book status=on_loan version=2  open loans=1

The error text comes from the driver and varies by engine; what is common is that there is a distinct error class saying the lock could not be acquired. The application has to distinguish this error from others: a lock error can be retried, a constraint violation cannot.

When a wait time is given, the second client waits for the lock and, once its turn comes, reads the current data.

sh setup.sh
WAIT_MS=5000 node pessimistic.mjs 4 & sleep 0.05; WAIT_MS=5000 node pessimistic.mjs 5 & wait
node count.mjs

member 4: got the book
member 5: book on loan, rejected
book status=on_loan version=2  open loans=1

The second member got a “book on loan” response, not a “conflict” response. The difference matters: in the optimistic approach, the application had to handle a conflict; in the pessimistic one, the business rule’s normal rejection took over. The cost is that the second member waits for the duration of the first member’s decision time.

Upgrade Conflict

Setting up pessimistic locking correctly depends on the lock being acquired before the read. Two transactions that read first and then try to write produce a conflict that cannot be resolved by waiting.

// upgrade.mjs — two transactions that read first and then try to write
import { DatabaseSync } from "node:sqlite";
import { writeFileSync, existsSync } from "node:fs";
const role = process.argv[2];
const db = new DatabaseSync("library.db");
db.exec("PRAGMA busy_timeout = 3000");

db.exec("BEGIN");                                  // deferred: the snapshot is taken at the read
db.prepare("SELECT status FROM book WHERE book_id = 1").get();
if (role === "a") { writeFileSync("a-read", ""); while (!existsSync("b-read")) { /* wait */ } }
else { writeFileSync("b-read", ""); while (!existsSync("a-read")) { /* wait */ } }

if (role === "b") { while (!existsSync("a-wrote")) { /* let A write first */ } }
try {
  db.prepare("UPDATE book SET version = version + 1 WHERE book_id = 1").run();
  db.exec("COMMIT");
  if (role === "a") writeFileSync("a-wrote", "");
  console.log(`${role}: wrote and committed`);
} catch (h) {
  db.exec("ROLLBACK");
  console.log(`${role}: upgrade failed -> ${h.message}`);
}

sh setup.sh
rm -f a-read b-read a-wrote
node upgrade.mjs a & node upgrade.mjs b & wait
node count.mjs

b: upgrade failed -> database is locked
a: wrote and committed
book status=on_shelf version=2  open loans=0

B’s wait time was three seconds; even so, it got the error without waiting. The reason is this: B’s transaction opened on a snapshot, and that snapshot is now stale. Waiting does not refresh the snapshot, because it has to stay fixed for the duration of the transaction. The only solution is to roll the transaction back and start over.

This is the situation covered in the Advanced SQL course as an upgrade conflict. It gives the rule on the application side: if there is a write intent, the lock is acquired before the read (with a write-intent declaration such as BEGIN IMMEDIATE); if not, the conflict is handled with the optimistic method. There is no third option sitting between the two.

Measuring the Cost

The difference between the two approaches shows up under contention. The setup below increments a single counter with four workers; each increment is a read, a three-millisecond decision time, and a write.

# counter-setup.sh — single-row counter
rm -f counter.db counter.db-wal counter.db-shm
sqlite3 counter.db >/dev/null <<'SQL'
PRAGMA journal_mode = WAL;
CREATE TABLE counter (name TEXT PRIMARY KEY, value INTEGER NOT NULL, version INTEGER NOT NULL);
INSERT INTO counter VALUES ('loan_count', 0, 1);
SQL

// counter-optimistic.mjs — read, decide, write; retry on conflict
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("counter.db");
db.exec("PRAGMA busy_timeout = 10000");
const count = Number(process.argv[2]);
const thinkMs = Number(process.argv[3]);
const think = () => { const end = Date.now() + thinkMs; while (Date.now() < end) { /* decision time */ } };

let attempts = 0;
for (let i = 0; i < count; i++) {
  for (;;) {
    attempts += 1;
    const s = db.prepare("SELECT value, version FROM counter WHERE name = 'loan_count'").get();
    think();
    db.exec("BEGIN IMMEDIATE");
    const result = db.prepare(
      "UPDATE counter SET value = ?, version = version + 1 WHERE name = 'loan_count' AND version = ?")
      .run(s.value + 1, s.version);
    db.exec(result.changes === 0 ? "ROLLBACK" : "COMMIT");
    if (result.changes === 1) break;
  }
}
console.log(`optimistic worker: ${count} increments, ${attempts} attempts`);

// counter-pessimistic.mjs — the lock is taken first, the decision is made under lock
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("counter.db");
db.exec("PRAGMA busy_timeout = 10000");
const count = Number(process.argv[2]);
const thinkMs = Number(process.argv[3]);
const think = () => { const end = Date.now() + thinkMs; while (Date.now() < end) { /* decision time */ } };

for (let i = 0; i < count; i++) {
  db.exec("BEGIN IMMEDIATE");
  const s = db.prepare("SELECT value FROM counter WHERE name = 'loan_count'").get();
  think();
  db.prepare("UPDATE counter SET value = ?, version = version + 1 WHERE name = 'loan_count'")
    .run(s.value + 1);
  db.exec("COMMIT");
}
console.log(`pessimistic worker: ${count} increments, ${count} attempts`);

# counter-measure.sh — four workers, 40 increments per worker, 3 ms decision time
for approach in optimistic pessimistic; do
  sh counter-setup.sh
  start=$(date +%s%N)
  workers=""
  for i in 1 2 3 4; do node counter-$approach.mjs 40 3 & workers="$workers $!"; done
  wait $workers
  end=$(date +%s%N)
  printf '%s: total %s ms, final value = %s\n\n' "$approach" $(( (end-start)/1000000 )) \
    "$(sqlite3 counter.db 'SELECT value FROM counter;')"
done

sh counter-measure.sh

optimistic worker: 40 increments, 119 attempts
optimistic worker: 40 increments, 126 attempts
optimistic worker: 40 increments, 135 attempts
optimistic worker: 40 increments, 155 attempts
optimistic: total 510 ms, final value = 160

pessimistic worker: 40 increments, 40 attempts
pessimistic worker: 40 increments, 40 attempts
pessimistic worker: 40 increments, 40 attempts
pessimistic worker: 40 increments, 40 attempts
pessimistic: total 713 ms, final value = 160

The final value is 160 in both approaches; both are correct. The durations and attempt counts vary by machine and timing; the relationship does not. The optimistic approach spent well over a hundred attempts for every forty increments — between 119 and 155 per worker — and roughly seven attempts in ten were wasted. Even so, the total duration came out shorter, because the workers spent their decision time in parallel. The pessimistic approach wasted no attempts at all, but the decision times were serialized.

The rule for choosing follows from this. When conflict is rare, the optimistic approach is cheap: the number of wasted attempts stays low, and no one waits. When conflict is frequent, the optimistic approach repeats the same work over and over; the pessimistic approach sets up the order from the start and cuts off that waste. When the decision time is long, the pessimistic approach’s cost grows quickly, because the lock is held for that entire duration.

Summary

• The unprotected read–decide–write flow issued the single-copy book to two members; the lost update occurred even though the write was inside a transaction.
• Optimistic locking places the read version into the write condition; an affected row count of zero is the sign of a conflict, and the losing side retries.
• Pessimistic locking makes the decision under a lock; with the wait time at zero it produced a lock error, and with a wait time given it produced a normal rejection with current data.
• The transaction that read first and then tried to write got an upgrade conflict; this error is not resolved by waiting, the transaction is started over.
• In the measurement, the optimistic approach spent well over a hundred attempts for forty increments but finished faster; the choice depends on how frequent the conflict is and how long the decision takes.

Next Step

Both solutions in this lesson worked inside a single database; the same engine managed both the lock and the version counter. The picture changes if the loan service is not on its own. If the book catalog sits in one service, member records in another, and fine collection in a third, then the “issue a loan” operation touches three separate databases, and none of them can see the others’ transaction. The next lesson shows that atomicity cannot cross a service boundary, measures what that loss means by modeling it, and builds the compensation and outbox patterns.