SQL Locking and Concurrency

Whenever more than one connection tries to read or write the same data at the same time, the database engine needs a way to keep those operations from stepping on each other. That mechanism is locking. Locks are what stop one transaction from overwriting another transaction’s uncommitted changes, reading data mid-update, or corrupting a table because two writers touched it simultaneously. Understanding how locking works — and how your specific database engine implements it — is essential for building applications that stay correct and fast under real concurrent load.

Overview: How Database Locking Works

Every relational database has to answer the same question: when transaction A is reading or writing a piece of data, what is transaction B allowed to do to that same piece of data right now? The answer is enforced through locks — markers the engine attaches to some unit of data (a row, a page, a table, or even the whole database file) that either permit or block other transactions from touching that unit.

There are two broad strategies for handling this:

• Pessimistic concurrency control — assume conflicts will happen, so acquire a lock before touching data and make other transactions wait. This is what most people mean by “locking.”
• Optimistic concurrency control — assume conflicts are rare. Let every transaction proceed without locking, then check at write time (usually with a version number or timestamp column) whether anything changed underneath you. If it did, reject the write and let the application retry.

Locks also differ in mode and granularity. By mode, the two fundamental types are:

Lock type	Also called	Behavior
Shared lock	Read lock	Multiple transactions can hold a shared lock on the same data at once (many readers), but no one can write while any shared lock is held.
Exclusive lock	Write lock	Only one transaction can hold it, and no other transaction may read or write that data until it’s released.

By granularity, engines like MySQL’s InnoDB and PostgreSQL support row-level locking — only the specific rows a statement touches are locked, so unrelated rows in the same table remain fully accessible to other transactions. Other engines lock at a coarser level: a whole page, a whole table, or in SQLite’s case, the entire database file. Coarser locking is simpler and cheaper to manage, but it sacrifices concurrency — more transactions end up waiting for each other even when they don’t logically conflict.

SQLite’s Locking Model

Because the examples in this lesson run against SQLite, it’s worth understanding its model specifically, since it differs from the row-level locking you may have read about for other engines. SQLite locks the database file as a whole, not individual rows, and a connection moves through a sequence of lock states as a transaction progresses:

• UNLOCKED — no lock held; the connection isn’t accessing the database.
• SHARED — acquired to read; any number of connections can hold a shared lock simultaneously.
• RESERVED — acquired by a connection that intends to write; it signals “I plan to modify this database,” but existing readers may continue until the writer actually commits.
• PENDING — a brief state while the writer waits for existing readers to finish before upgrading further.
• EXCLUSIVE — required to actually write changes to the file; no other connection may read or write while it’s held.

By default, BEGIN starts a DEFERRED transaction — no lock is taken until the first statement that needs one. That’s convenient, but it means two transactions can both start, both read successfully, and only discover a write conflict later, when one of them tries to escalate to a write lock and finds another connection already holds one. This produces an SQLITE_BUSY error rather than a clean queue. You can avoid that surprise by explicitly requesting a write lock up front with BEGIN IMMEDIATE (grabs RESERVED immediately) or BEGIN EXCLUSIVE (grabs a full exclusive lock immediately).

SQLite also supports WAL (Write-Ahead Logging) mode, which changes this significantly: readers work from a stable snapshot of the database and no longer block a writer, and a single writer can commit while readers keep reading. WAL mode is the standard recommendation for any SQLite database with concurrent access.

Syntax

BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] TRANSACTION;
  -- ... statements ...
COMMIT;
-- or
ROLLBACK;

PRAGMA busy_timeout = milliseconds;
PRAGMA journal_mode = WAL;
PRAGMA locking_mode = NORMAL | EXCLUSIVE;

Clause / Pragma	Purpose
BEGIN DEFERRED	Default. No lock taken until a statement needs one.
BEGIN IMMEDIATE	Takes a write (RESERVED) lock immediately, avoiding late write-lock failures.
BEGIN EXCLUSIVE	Takes a full exclusive lock immediately, blocking all other readers and writers.
PRAGMA busy_timeout	How long (ms) a connection waits for a lock before failing with SQLITE_BUSY, instead of failing instantly.
PRAGMA journal_mode = WAL	Switches to write-ahead logging so readers and a writer can operate concurrently.

Examples

Example 1: An explicit write lock with BEGIN IMMEDIATE

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  balance REAL NOT NULL
);
INSERT INTO accounts (id, owner, balance) VALUES
  (1, 'Alice', 500.00),
  (2, 'Bob', 150.00);

BEGIN IMMEDIATE TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT id, owner, balance FROM accounts ORDER BY id;

Result:

id	owner	balance
1	Alice	400.0
2	Bob	250.0

BEGIN IMMEDIATE grabs the write lock the moment the transaction starts, instead of waiting until the first UPDATE runs. For a money transfer like this, that matters: it guarantees no other writer can sneak in between the two UPDATE statements and leave the transfer half-applied.

Example 2: Waiting instead of failing with busy_timeout

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

Result: a single row reporting the current busy timeout, now set to 5000 milliseconds.

Without a busy timeout, a connection that can’t immediately get the lock it needs fails right away with SQLITE_BUSY. Setting busy_timeout tells SQLite to instead retry quietly for up to that many milliseconds before giving up — turning a hard failure under light contention into a short, invisible wait.

Example 3: Optimistic concurrency with a version column

CREATE TABLE inventory (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  version INTEGER NOT NULL DEFAULT 1
);
INSERT INTO inventory (id, product_name, quantity, version)
VALUES (1, 'USB Cable', 50, 1);

UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE id = 1 AND version = 1;

SELECT id, product_name, quantity, version FROM inventory WHERE id = 1;

Result:

id	product_name	quantity	version
1	USB Cable	49	2

No explicit lock is taken here at all. Instead, the WHERE id = 1 AND version = 1 clause makes the update conditional on nothing else having changed the row first. If a second process had already updated this row (bumping version to 2), this statement would match zero rows and update nothing — the application checks the affected-row count and knows to reload and retry. This is optimistic concurrency: cheap when conflicts are rare, and it needs no lock-wait logic at all.

How It Works Step by Step (Under the Hood)

For a typical write transaction in a lock-based engine, the sequence looks like this:

• 1. Statement issued. The engine parses and plans the statement as usual (table access, index use, join order).
• 2. Lock requested. Before touching data, the engine requests a lock appropriate to the operation — shared for reads, exclusive (or an intent/reserved lock) for writes — at whatever granularity it supports (row, page, table, or file).
• 3. Lock granted or queued. If the requested lock is compatible with locks already held by other transactions, it’s granted immediately. If not, the requesting transaction waits — up to a timeout, if one is configured.
• 4. Statement executes. With the lock held, the read or write proceeds safely against a consistent view of the data.
• 5. Lock held for the transaction’s duration. Locks acquired mid-transaction are generally held until COMMIT or ROLLBACK, not released after each statement — this is what keeps the whole transaction atomic and isolated from others.
• 6. Release. On COMMIT or ROLLBACK, all locks the transaction held are released, and any transactions that were queued behind them can proceed.

Two transactions can end up each holding a lock the other one needs — a deadlock. For example, transaction A locks row 1 and then wants row 2, while transaction B locks row 2 and then wants row 1; neither can proceed. Engines with row-level locking typically run a deadlock detector that spots the cycle and aborts one transaction (rolling it back) so the other can continue. SQLite, with its single database-level lock, cannot deadlock in quite the same way between writers, but a connection can still be starved waiting for a lock indefinitely without a busy_timeout.

Common Mistakes

Mistake 1: Using SELECT … FOR UPDATE in SQLite

SELECT ... FOR UPDATE is standard in PostgreSQL, MySQL, and Oracle for explicitly locking the rows a query returns. SQLite has no such clause — it doesn’t do row-level locking at all, so this raises a syntax error:

SELECT * FROM employees WHERE department = 'Engineering' FOR UPDATE;
-- Error: near "FOR": syntax error (SQLite has no row-level FOR UPDATE)

The SQLite equivalent is to take a database-level write lock for the whole transaction up front:

BEGIN IMMEDIATE TRANSACTION;
SELECT * FROM employees WHERE department = 'Engineering';
COMMIT;

BEGIN IMMEDIATE acquires the write lock immediately, so no other connection can start writing until this transaction finishes — a coarser but workable substitute for row-level FOR UPDATE.

Mistake 2: Assuming SET TRANSACTION ISOLATION LEVEL works everywhere

Many engines let you choose an isolation level per session or transaction with a statement like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM orders;
COMMIT;
-- Error: SQLite does not support the SET TRANSACTION statement

SQLite doesn’t expose isolation levels this way at all — its default behavior is already serializable-like for a single writer, and concurrency behavior is instead controlled through the transaction mode (DEFERRED/IMMEDIATE/EXCLUSIVE) and the journal mode:

PRAGMA journal_mode = WAL;

Switching to WAL mode is the practical SQLite equivalent of tuning for concurrency: it lets readers keep working off a snapshot while a single writer commits, rather than blocking everyone during a write.

Best Practices

• Keep transactions as short as possible — every millisecond a lock is held is a millisecond other connections might be waiting.
• In SQLite, use BEGIN IMMEDIATE for any transaction you know will write, so lock conflicts surface immediately instead of mid-transaction.
• Set a reasonable busy_timeout so transient contention causes a short wait instead of an outright failure.
• Prefer WAL mode for SQLite databases with concurrent readers and writers.
• Use optimistic concurrency (a version or updated-at column) for workloads where conflicts are rare — it avoids lock contention entirely.
• Don’t assume locking syntax is portable — FOR UPDATE, isolation-level statements, and lock hints vary a lot between engines; check your specific database’s documentation.
• In row-level locking engines, always access tables in a consistent order across your transactions to reduce the chance of deadlocks.
• Index the columns used in your UPDATE/DELETE WHERE clauses — on engines with row-level locking, a missing index can force a full table scan and lock far more rows than necessary.

Practice Exercises

• Exercise 1: Using the accounts table from Example 1, write a transaction that starts with BEGIN IMMEDIATE, transfers 50 from Bob to Alice, and commits. What are the final balances?
• Exercise 2: Explain in your own words what would happen if two SQLite connections both ran BEGIN IMMEDIATE at nearly the same time without a busy_timeout set, versus with PRAGMA busy_timeout = 3000; set on both.
• Exercise 3: Using the inventory table from Example 3, write an UPDATE that decrements quantity by 1 guarded by version = 2. What should your application do if this statement reports zero rows affected?

Summary

• Locking is how databases let multiple transactions run at once without corrupting or misreading each other’s data.
• Shared (read) locks can be held by many transactions at once; exclusive (write) locks can be held by only one.
• Lock granularity ranges from row-level (Postgres, MySQL/InnoDB) to whole-database-level (SQLite) — coarser granularity means simpler locking but more waiting.
• SQLite moves through UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE states; BEGIN IMMEDIATE/EXCLUSIVE let you request a write lock up front.
• PRAGMA busy_timeout turns instant lock failures into short retries; PRAGMA journal_mode = WAL lets readers and a writer work concurrently.
• Optimistic concurrency (version columns) avoids locking altogether by detecting conflicts at write time.
• Deadlocks arise from circular lock waits; engines resolve them via detection (abort one transaction) or timeouts.
• Locking syntax and behavior are not portable across databases — always verify against your specific engine’s documentation.