Imagine a single notebook that an entire family shares to write down chores. If two people try to write in it at the exact same instant, pages get torn, ink smears, and nobody can read what was written. A database is that notebook — except instead of one family, it might be shared by millions of people (or millions of computer programs) all trying to read and write at the same time.

Database locking is the mechanism a database uses to make sure that when two or more users try to touch the same piece of data at the same time, they don’t step on each other’s toes. It is the traffic‑light system of the data world: it tells one car “go” and the other car “wait,” so nobody crashes at the intersection.

1.1 A Short History

In the early days of computing (1960s–1970s), most programs ran one at a time, one after another (this is called “batch processing”). There was no real fight over data because only one program touched the database at any given moment.

As computers became powerful enough to run many programs simultaneously (multiprogramming) and later as networks connected many users to one shared database, engineers ran into a brand‑new problem: what happens when two people try to update the same bank account balance at the same second? This question gave birth to an entire field of computer science called concurrency control, and locking was one of the very first (and still most widely used) solutions.

Jim Gray, a legendary computer scientist at IBM, formalised much of this theory in the 1970s while working on System R, one of the first relational database prototypes. His work on locking, transactions, and the concept of ACID properties (which we’ll explain shortly) still forms the backbone of every serious database today — MySQL, PostgreSQL, Oracle, SQL Server, and even modern cloud databases like Amazon Aurora and Google Spanner.

1.2 A Quick Timeline

• 1960s–70sEarly mainframe computers run programs in strict batch mode; concurrency conflicts are rare and mostly ignored.
• 1970sJim Gray and colleagues at IBM formalise concurrency control theory as part of the System R relational database project.
• 1980s–90sCommercial relational databases (Oracle, DB2, SQL Server, Sybase) ship production‑grade locking and isolation‑level support.
• 1990s–2000sMVCC gains popularity in PostgreSQL, Oracle, and later MySQL InnoDB — readers stop blocking writers for good.
• 2010sDistributed lock services (ZooKeeper, etcd, Redis Redlock) let systems coordinate across many machines and data centres.
• 2012‑presentGlobal databases like Google Spanner and CockroachDB combine locking with consensus algorithms to serialise transactions across continents.

To understand why locking exists, we first need to understand what goes wrong without it. Let’s use the simplest possible example: a bank account.

Suppose your bank balance is $100. You and your sibling both try to withdraw $80 at the exact same second — you at an ATM, your sibling using the banking app. Without any protection, here is what could happen:

• Step 1Both programs read the balance. Your ATM reads balance = $100. At the same instant, the app also reads balance = $100.
• Step 2Both programs calculate a new balance. ATM computes: 100 − 80 = 20. App computes: 100 − 80 = 20.
• Step 3Both programs write the new balance. Both write “20” back to the database. Two withdrawals of $80 happened, but the balance only dropped by $80 instead of $160.
• Step 4The bank loses $80. This is called a lost update — one of the classic concurrency bugs. The bank gave out $160 but only recorded an $80 decrease.

This is exactly the kind of catastrophe that locking prevents. A lock makes sure that once the ATM starts working with your balance, the app must wait until the ATM is completely finished before it’s allowed to read or change that same balance.

Fig 2.1 — Locking forces the second transaction to wait until the first one finishes, preventing the lost‑update bug.

2.1 The Other Classic Problems Locking Prevents

Before going further, let’s build a solid vocabulary. Every term below is something you will see constantly in database documentation, interviews, and production incident reports.

3.1 What Is a “Lock”?

A lock is a flag (a small marker) that the database attaches to a piece of data — a row, a page, or an entire table — to say “this is currently being used, please wait or be careful.” It’s stored in the database engine’s memory, in a structure usually called a lock table.

3.2 Shared Locks vs. Exclusive Locks

Not every lock is the same strictness. There are two fundamental types:

This gives us a simple compatibility rule that every database engine follows:

3.3 Lock Granularity

Granularity just means “how big is the chunk of data being locked?” Databases can lock at different sizes:

3.4 A Tiny Beginner Example

Imagine a spreadsheet shared with your classmates. If your teacher locks cell B2 while grading it, nobody else can edit B2 until the teacher is done — but everyone can still edit A1, C5, or any other cell. That’s exactly how row‑level locking behaves inside a database table.

3.5 Software Example — SELECT…FOR UPDATE

In SQL, you often request an exclusive lock explicitly using SELECT … FOR UPDATE:

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 123 FOR UPDATE;  -- takes an exclusive row lock
UPDATE accounts SET balance = balance - 80 WHERE id = 123;
COMMIT;  -- lock released here

While Transaction A holds this lock, any other transaction trying to SELECT … FOR UPDATE the same row (id = 123) will simply pause and wait until Transaction A commits or rolls back.

3.6 Latches vs. Locks — A Subtle but Important Distinction

Interviewers love this question, so let’s settle it clearly. A lock protects logical data (a row, a table) for the entire duration of a transaction, and is visible to the transaction manager. A latch (sometimes called a “mutex” inside the database engine) protects a physical, low‑level structure — like a page in memory or an internal data structure — for a tiny fraction of a second, just long enough to safely read or modify it. Latches are held for microseconds; locks can be held for the whole length of a transaction, which might be seconds or even minutes.

Think of a lock as reserving a whole meeting room for your one‑hour meeting, while a latch is like briefly holding a door open for half a second so nobody bumps into you while you walk through. Both prevent conflicts, but they operate at completely different time scales and for completely different purposes.

Locking doesn’t exist in a vacuum — it’s the tool databases use to implement something called transaction isolation levels. These levels are a dial you can turn between “fast but risky” and “slow but safe.”

A transaction is simply a group of operations treated as one unit — either all of them succeed, or none of them do. Think of it like a recipe: you don’t want to add “half an egg”; you either finish the whole step or you don’t do it at all.

*Note: PostgreSQL’s Repeatable Read actually prevents phantom reads too, thanks to MVCC snapshots — more on this in Section 8.

MySQL’s default is Repeatable Read. PostgreSQL’s default is Read Committed. Oracle only offers Read Committed and Serializable. Knowing your database’s default is a very common interview and production‑debugging topic.

4.1 Setting Isolation Level in Java (JDBC)

Your application code can request a specific isolation level per connection, overriding the database default when a particular operation needs stronger (or can tolerate weaker) guarantees:

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
try {
    // critical financial operation that must never see anomalies
    transferFunds(conn, fromId, toId, amount);
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);
}

A simple beginner way to remember the four levels: Read Uncommitted is “trust everyone, even mid‑sentence.” Read Committed is “only listen to finished sentences.” Repeatable Read is “once I start listening to your story, don’t change earlier parts on me.” Serializable is “we can only talk one at a time, in turns, no interruptions at all.”

Two‑Phase Locking is the classical algorithm that guarantees the strictest, safest form of concurrency (serialisability). It has a wonderfully simple rule:

“Once a transaction releases even one lock, it may never acquire any new locks again.”

This creates exactly two phases:

Fig 5.1 — The “growing” phase acquires locks, the “shrinking” phase releases them — they never interleave.

Most production databases use a variant called Strict Two‑Phase Locking (Strict 2PL), where all exclusive locks are held until the transaction fully commits or rolls back (rather than releasing gradually). This avoids a subtle problem called “cascading rollbacks,” where undoing one transaction would force you to undo others that read its uncommitted changes.

There are two philosophies for handling conflicts, and picking the right one is one of the most important production decisions a backend engineer makes.

Optimistic locking is usually implemented with a version number or a timestamp column.

// Java example using JDBC – optimistic locking with a version column
public boolean updateProductPrice(Connection conn, int productId, double newPrice, int expectedVersion) throws SQLException {
    String sql = "UPDATE products SET price = ?, version = version + 1 " +
                 "WHERE id = ? AND version = ?";
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        stmt.setDouble(1, newPrice);
        stmt.setInt(2, productId);
        stmt.setInt(3, expectedVersion);
        int rowsAffected = stmt.executeUpdate();
        // If rowsAffected == 0, someone else changed the row first — retry or fail
        return rowsAffected > 0;
    }
}

If another transaction already bumped the version number, rowsAffected will be 0, and your application knows a conflict happened — it can retry the whole operation with fresh data.

This pattern is exactly how popular frameworks like Java’s JPA/Hibernate implement @Version fields, and it’s how most e‑commerce “add to cart” and inventory systems avoid overselling without locking every single row constantly.

A deadlock happens when two (or more) transactions are each waiting for a lock that the other one holds — forever. Neither can proceed. It’s the database equivalent of two polite people in a doorway, each saying “after you,” and both never moving.

Fig 7.1 — A classic deadlock cycle — Transaction 1 waits on Transaction 2, which waits on Transaction 1.

7.1 Beginner Example

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- locks row 1
-- (pauses here for a moment)
UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- wants to lock row 2

-- Transaction 2 (running at the same time)
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;  -- locks row 2
-- (pauses here for a moment)
UPDATE accounts SET balance = balance + 20 WHERE id = 1;  -- wants to lock row 1

Transaction 1 holds row 1 and wants row 2. Transaction 2 holds row 2 and wants row 1. Neither will ever get what it needs. This is a genuine deadlock.

7.2 How Databases Detect Deadlocks

Most databases build a wait‑for graph in the background — a diagram of “who is waiting for whom.” If this graph ever forms a cycle (as in the figure above), the database knows a deadlock exists. It then picks a victim transaction (often the one that has done the least work) and forcibly rolls it back, freeing its locks so the other transaction can continue.

1. Both transactions request locks and one becomes blocked.
2. The database’s deadlock detector periodically scans the wait‑for graph.
3. A cycle is found → deadlock confirmed.
4. The database aborts one transaction (throws an error like Deadlock found; try restarting transaction).
5. The application catches this error and retries.

7.3 How to Prevent Deadlocks in Practice

// Java example — handling a MySQL deadlock with a retry loop
int maxRetries = 3;
for (int attempt = 1; attempt <= maxRetries; attempt++) {
    try {
        transferFunds(conn, fromAccountId, toAccountId, amount);
        break; // success
    } catch (SQLException e) {
        if (e.getErrorCode() == 1213 /* MySQL deadlock code */ && attempt < maxRetries) {
            Thread.sleep(50L * attempt); // small backoff before retrying
            continue;
        }
        throw e;
    }
}

// Option 1: the "synchronized" keyword — simple, built into the language
public class Counter {
    private int count = 0;
    public synchronized void increment() {
        count++;   // only one thread can run this method at a time
    }
}

// Option 2: java.util.concurrent.locks.ReentrantLock — more flexible
public class SafeCounter {
    private int count = 0;
    private final ReentrantLock lock = new ReentrantLock();

    public void increment() {
        lock.lock();
        try {
            count++;
        } finally {
            lock.unlock();  // ALWAYS release in a finally block
        }
    }
}

Locking every single read would be extremely slow at scale, so most modern databases (PostgreSQL, MySQL InnoDB, Oracle, SQL Server’s snapshot isolation) use something smarter for reads: Multi‑Version Concurrency Control (MVCC).

The idea is beautifully simple: instead of making readers wait for writers, the database keeps multiple versions of a row. A reader is given a consistent “snapshot” of the data as it looked at the moment their transaction started, while a writer creates a brand‑new version of the row rather than overwriting the old one in place.

Fig 8.1 — MVCC lets readers keep working with an old but consistent snapshot while writers create new versions.

With MVCC, readers never block writers, and writers never block readers. Only writer‑vs‑writer conflicts still need locks. This is why PostgreSQL and MySQL InnoDB can support thousands of concurrent read‑heavy connections without grinding to a halt.

Old row versions that are no longer needed by any active transaction are eventually cleaned up by a background process — in PostgreSQL this is called VACUUM, and forgetting to run it (or having it fall behind) is a famous cause of production slowdowns and “table bloat.”

Let’s open the hood and look at the actual pieces inside a database engine that make locking possible.

Fig 9.1 — A simplified architecture of how the lock manager fits into a database engine.

9.1 Key Components

The lock table deserves extra attention because it’s frequently asked about in interviews. Internally it’s typically a hash table where the key is something like (table_id, row_id) and the value is a small structure listing: the lock mode (S or X), the list of transactions currently holding it, and a queue of transactions waiting for it.

9.2 How the Lock Manager Decides “Grant” or “Wait”

When a new lock request arrives, the lock manager runs roughly this algorithm:

1. Look up the resource in the lock table. If no entry exists, create one and grant the lock immediately.
2. If an entry exists, compare the requested mode against every currently granted mode using the compatibility matrix from Section 3.2.
3. If compatible with all current holders and the wait queue is empty, grant immediately.
4. If incompatible, or if there are already waiters ahead in line (to keep things fair and prevent starvation), append the request to the wait queue and put the requesting transaction to sleep.
5. Whenever a lock is released, wake up the next compatible request(s) in the queue in order.

That “fairness” rule in step 4 is important: without it, a steady stream of incoming shared (read) locks could keep an exclusive (write) lock waiting forever — a problem called writer starvation. Most production databases use FIFO queuing specifically to prevent this.

Let’s trace exactly what happens, step by step, from the moment a transaction starts to the moment it ends.

Fig 10.1 — The full lifecycle of a lock request, from acquisition to release.

1. Begin: the application starts a transaction.
2. Request: when the transaction touches a row, the engine asks the lock manager for the appropriate lock (S for read, X for write).
3. Check compatibility: the lock manager checks the lock table to see if the requested lock conflicts with existing locks.
4. Grant or Queue: if compatible, the lock is granted immediately. If not, the requester is placed in a FIFO wait queue (most databases are fair and avoid starving waiters).
5. Hold: the transaction keeps working while holding its locks.
6. Release: on COMMIT or ROLLBACK, all locks are released at once (in Strict 2PL).
7. Wake next waiter: the lock manager notifies the next transaction in the queue that the resource is now free.

Locking is a powerful tool, but every tool has costs. Understanding the trade‑offs is what turns knowledge into good production judgement.

There’s no universally “correct” locking strategy — it’s always a trade‑off between consistency, concurrency, and complexity. This trio is sometimes nicknamed the “consistency‑concurrency dial”: turning it toward more safety turns it away from more speed.

Locking directly affects how many transactions per second (TPS) your database can handle. Here are the levers engineers pull in production.

12.1 Lock Contention

Contention happens when many transactions want the same lock at once — like everyone in a store trying to use the single self‑checkout machine. High contention on a “hot row” (e.g. a popular product’s inventory count) is one of the most common real‑world scalability bottlenecks.

12.2 Techniques to Reduce Contention

12.3 Lock Escalation

Some databases (notably SQL Server) automatically convert many small row‑locks into one big table‑lock when a transaction touches too many rows, to save memory. This is called lock escalation, and while it saves overhead, it can accidentally block unrelated transactions — a subtle production gotcha worth knowing.

12.4 Index Locking — The Hidden Cost

Beginners often forget that an UPDATE doesn’t just lock the row itself — it may also need to lock entries in every index built on that table, since indexes are separate physical structures that must stay in sync with the underlying data. A table with five indexes touched by a write may involve five separate internal lock acquisitions, not just one. This is one reason why adding “just one more index” to speed up reads can quietly slow down writes — a classic read‑versus‑write trade‑off every engineer eventually learns the hard way.

12.5 Lock‑Free and Latch‑Free Data Structures

At the very cutting edge of performance engineering, some in‑memory databases and caching layers (like parts of Redis, or specialised in‑memory databases such as VoltDB) use lock‑free algorithms built on hardware‑level atomic operations (like “compare‑and‑swap”). These structures avoid traditional locks entirely for certain operations, squeezing out extra throughput at the cost of significantly higher implementation complexity. This is advanced territory, but it’s worth knowing the term exists — it often comes up when comparing “why is this database so much faster for this one workload.”

So far we’ve discussed locking inside a single database server. But modern systems run across many servers, data centres, and even continents. This raises a much harder question: how do you lock something when the data isn’t even on one machine?

13.1 Locking and Replication

Most production databases use a primary‑replica (leader‑follower) setup: writes go to one primary node, and locks are only managed there. Read replicas serve read‑only traffic and typically don’t need write locks at all — but they may lag slightly behind the primary (this is called replication lag, part of the “eventual consistency” trade‑off).

Fig 13.1 — Only the primary node manages write locks; replicas serve reads independently.

13.2 CAP Theorem in One Paragraph

The CAP theorem says a distributed system can only fully guarantee two out of three: Consistency (everyone sees the same data), Availability (the system always responds), and Partition tolerance (it keeps working even if network links break). Because network partitions are unavoidable in the real world, the real choice is between consistency and availability during a partition. Locking is fundamentally a consistency tool — it can reduce availability if a lock‑holder becomes unreachable.

13.3 Distributed Locks

Sometimes you need to lock something that isn’t even a database row — like making sure only one server in a cluster runs a scheduled job at a time. This is solved with distributed locking systems such as:

• Redis (via the SET NX + expiry pattern, or the Redlock algorithm) — fast, widely used, good for short‑lived locks.
• Apache ZooKeeper / etcd — built on the Raft or Paxos consensus algorithms, offering very strong guarantees, used by Kafka, Kubernetes, and many large‑scale systems.
• Database advisory locks — PostgreSQL offers pg_advisory_lock(), a lock that isn’t tied to any row, useful for app‑level coordination.

// Java example — acquiring a distributed lock in Redis using Jedis
public boolean acquireLock(Jedis jedis, String lockKey, String requestId, int ttlSeconds) {
    // SET key value NX EX ttl -> only succeeds if key does not already exist
    String result = jedis.set(lockKey, requestId, SetParams.setParams().nx().ex(ttlSeconds));
    return "OK".equals(result);
}

public void releaseLock(Jedis jedis, String lockKey, String requestId) {
    // Only delete if we still own it (avoids deleting someone else's lock after expiry)
    String script = "if redis.call('get', KEYS[1]) == ARGV[1] then " +
                     "return redis.call('del', KEYS[1]) else return 0 end";
    jedis.eval(script, 1, lockKey, requestId);
}

13.4 Consensus Algorithms

When multiple nodes must agree on “who holds the lock right now,” they use consensus algorithms like Raft or Paxos. These algorithms let a cluster of machines agree on a single fact even if some machines crash or messages get lost — the same technology that lets etcd (used inside Kubernetes) safely coordinate leader election.

13.5 Failure Recovery

If a database crashes while holding locks, those locks simply vanish along with the crashed process’s in‑memory state. On restart, the database replays its Write‑Ahead Log (WAL) to redo committed work and undo uncommitted work — any transaction that was mid‑flight (and thus holding locks) is automatically rolled back, and its locks never need to be “manually” released because the in‑memory lock table itself was rebuilt from scratch.

Locking isn’t just about correctness — it has real security implications too.

You can’t fix what you can’t see. Every production database team tracks locking‑related metrics closely.

-- PostgreSQL: find what's currently blocking what
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;

In real production environments, teams wire these metrics into dashboards (Grafana, Datadog) and set alerts such as “page the on‑call engineer if deadlocks exceed 10/minute” or “alert if any transaction has held a lock for more than 30 seconds.” Distributed tracing tools (like OpenTelemetry) can also tag spans with lock‑wait time so engineers can see exactly which API call got stuck and why.

Cloud‑managed databases have changed how teams think about locking in production.

When deploying to the cloud, always check your provider’s default lock timeout, isolation level, and deadlock retry behaviour — defaults sometimes differ from a self‑hosted install of the same database engine.

16.1 Backups, Disaster Recovery, and Locking

Taking a backup of a live, constantly‑changing database might sound like it would require locking the entire database for the whole duration — but modern backup tools avoid this. Techniques like consistent snapshots (relying on the same MVCC machinery from Section 8) let a backup process read a stable, point‑in‑time view of the data without blocking ongoing writes at all. Tools such as pg_basebackup for PostgreSQL or Percona XtraBackup for MySQL are specifically designed to take “hot backups” — full backups while the database keeps serving live traffic — precisely because they piggyback on the same versioning ideas that make MVCC reads non‑blocking. This is a major improvement over decades‑old backup strategies that genuinely did require freezing the whole database, which was unacceptable for any system that needs to run 24/7.

Disaster recovery plans should also account for locking behaviour during failover: when a primary node fails and a replica is promoted to take its place, any locks that existed only in the old primary’s memory are gone — in‑flight transactions are lost and must be retried by the application, which is another reason idempotent, retryable application logic (mentioned in Sections 7 and 20) is such a critical production habit, not just a deadlock‑specific fix.

Locking concepts extend beyond the database itself into the rest of your architecture.

17.1 Cache Stampede and Locking

Imagine a popular cache entry (like a homepage’s “trending products” list) expires, and suddenly 10,000 requests hit the database at once to regenerate it. This is called a cache stampede. A common fix is to use a short‑lived distributed lock (like the Redis lock shown in Section 13) so only one request regenerates the cache while the rest wait briefly or serve stale data.

Fig 17.1 — A distributed lock prevents thousands of requests from hammering the database at once when a cache entry expires.

17.2 Load Balancers and “Sticky” Sessions

Load balancers themselves don’t hold database locks, but poor routing can worsen lock contention — e.g. always routing writes for the same customer to different app servers can increase the chance of overlapping transactions competing for the same row. Some systems intentionally route related writes (like all updates for one shopping cart) to the same backend instance to naturally serialise them before they even reach the database.

In a microservices world, a single business transaction (like “place an order”) often spans multiple services and multiple databases — no single database lock can protect the whole flow.

18.1 The Problem with Distributed Transactions

Traditional locking (2PL) assumes one database. Across services, engineers historically used two‑phase commit (2PC), a distributed transaction protocol — but it’s slow and creates tight coupling between services (if one service is down, everyone is blocked).

18.2 The Modern Approach: Sagas

Most microservice architectures today avoid distributed locks entirely and instead use the Saga pattern: break the big transaction into a series of small, local transactions, each with a “compensating action” to undo it if a later step fails.

Fig 18.1 — A Saga chains local transactions with compensating actions instead of holding one giant distributed lock.

Within each individual service, however, ordinary row‑level locking (or optimistic locking) is still used at the database level — Sagas just remove the need for locks to span services.

18.3 Idempotency, Not Locks, for APIs

To prevent duplicate API calls (like a user double‑clicking “Pay Now”) from causing double charges, APIs commonly use idempotency keys rather than distributed locks: the client sends a unique key with the request, and the server checks (using a unique‑constraint or a short‑lived lock on that key) whether it has already processed that exact request.

A small number of named patterns keep showing up around locking because they answer recurring problems well. An equally small number of anti‑patterns keep showing up because they’re the shortcuts everyone is tempted to take.

19.1 Good Patterns

19.2 Anti-patterns to Avoid

The gap between a locking system that scales gracefully and one that collapses under load is usually not a technology gap — it’s a discipline gap. These are the habits successful teams share.

• 01Keep transactions as short as possible. Do all your business logic and computation outside the transaction; only wrap the actual database writes.
• 02Pick the loosest isolation level that’s still correct. Don’t reach for Serializable “just to be safe” — measure the real risk first.
• 03Always set lock and statement timeouts. A hung transaction should never be allowed to block the system forever.
• 04Access tables/rows in a consistent order across your entire codebase to prevent deadlocks.
• 05Prefer optimistic locking for low‑conflict, high‑read scenarios (like editing a user profile) and pessimistic locking for high‑conflict scenarios (like limited‑quantity ticket sales).
• 06Batch large writes into smaller chunks instead of one massive transaction touching millions of rows.
• 07Monitor lock waits and deadlocks continuously — treat rising numbers as an early warning sign, not just an incident trigger.
• 08Write idempotent retry logic in your application for deadlock errors — they will happen eventually at scale, and that’s expected, not a bug.

Almost every recognisable digital experience is quietly powered by locking somewhere in the stack. Here’s a short tour of how large systems apply it in practice.

Notice the pattern: the choice of locking strategy always follows the ratio of reads to writes and the cost of getting it wrong. High‑read systems lean on MVCC and caching; high‑conflict, high‑value writes (money, unique seats) lean on pessimistic locks or careful optimistic retries.

A short set of the questions engineers, students, and interviewers ask most about database locking.

Database locking is one of the oldest and still most essential ideas in computer science — the mechanism that lets many people share the same data without corrupting it. From a single hotel‑room key to globally distributed systems synchronising across continents with atomic clocks, the fundamental problem never changes: coordinate access so conflicting changes don’t collide.

Key Takeaways

• 01Locking prevents lost updates, dirty reads, non‑repeatable reads, and phantom reads by controlling concurrent access to data.
• 02Shared (read) locks can be held by many transactions at once; exclusive (write) locks can only be held by one.
• 03Two‑Phase Locking guarantees serialisability by never acquiring new locks after the first release.
• 04Pessimistic locking waits upfront; optimistic locking checks for conflicts only at write‑time — pick based on contention level.
• 05Deadlocks are detected via wait‑for graphs and resolved by rolling back a victim transaction; always design with consistent lock ordering.
• 06MVCC lets readers avoid blocking writers (and vice versa) by keeping multiple row versions — the backbone of modern high‑concurrency databases.
• 07Distributed systems extend locking with consensus algorithms (Raft, Paxos), distributed locks (Redis, ZooKeeper), and patterns like Sagas that avoid cross‑service locks entirely.
• 08Production success depends on monitoring lock waits/deadlocks, setting timeouts, keeping transactions short, and choosing the right isolation level for each workload.

“A good lock is a small, quiet promise: only one at a time — and you can always trust the answer, even when a million people are trying to change the same thing at once.”