Databases coordinate concurrent transactions with locks and, in most modern engines, MVCC (multi-version concurrency control), so readers and writers rarely block each other.

Lock types

Lock	Taken by	Compatible with
Shared (S)	Reads that need stability (`FOR SHARE`, lock-based engines)	Other shared locks
Exclusive (X)	`INSERT`/`UPDATE`/`DELETE`, `SELECT … FOR UPDATE`	Nothing — others wait
Intent (IS/IX)	Table-level marker before row locks	Lets the engine check table locks cheaply

Granularity: row locks (InnoDB, PostgreSQL, SQL Server, Oracle) allow high concurrency; table/page locks are cheaper but block more; SQLite locks the whole database for writing.

MVCC

Instead of overwriting a row in place, an update creates a new version. Each transaction reads the versions that were committed as of its snapshot. Result: readers don't block writers and writers don't block readers; only writer-vs-writer on the same row waits.

PostgreSQL keeps old versions in the table (cleaned up by VACUUM).
MySQL InnoDB and Oracle rebuild old versions from undo logs.
SQL Server uses locking by default; READ_COMMITTED_SNAPSHOT switches reads to row versions.

Explicit row locks

PostgreSQL / MySQL 8 / Oracle

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;       -- lock the row; others' FOR UPDATE waits
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- job queues: grab the next free job without waiting on rows others locked
SELECT id FROM jobs WHERE status = 'new'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;

Deadlocks

T1
Locks account 1 (UPDATE … WHERE id = 1).
T2
Locks account 2 (UPDATE … WHERE id = 2).
T1
Wants account 2 → waits for T2.
T2
Wants account 1 → waits for T1. Neither can proceed.
Database
Detects the cycle and aborts one transaction (the victim) with a deadlock error; the other continues.

SQLite: one writer at a time

Python + SQLite — write lock

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "shop.db")
with sqlite3.connect(path) as db:
    db.execute("PRAGMA journal_mode = WAL")
    db.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER)")
    db.execute("INSERT INTO stock VALUES ('PEN', 10)")

a = sqlite3.connect(path, isolation_level=None, timeout=0)
b = sqlite3.connect(path, isolation_level=None, timeout=0)

a.execute("BEGIN IMMEDIATE")                        # A holds the write lock
a.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'PEN'")
try:
    b.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'PEN'")
except sqlite3.OperationalError as e:
    print("B:", e)                                  # B: database is locked
print("B can still read:", b.execute("SELECT qty FROM stock").fetchone()[0])   # B can still read: 10
a.execute("COMMIT")
b.execute("UPDATE stock SET qty = qty - 1 WHERE sku = 'PEN'")   # now it succeeds
print("final:", b.execute("SELECT qty FROM stock").fetchone()[0])              # final: 8

Real apps set a timeout (busy timeout) so the second writer waits instead of failing immediately.

What is a deadlock and how do databases handle it?

Optimistic vs pessimistic locking?