Locking is the oldest answer to the isolation problem and still the one most widely deployed, and it is worth understanding as a mechanism before any protocol is layered on top of it.

What a lock actually is

An entry in a table saying that a particular transaction holds a particular kind of claim on a particular item, and that other transactions wanting an incompatible claim must wait.

Starting from the data structure rather than from the protocol keeps the subject concrete. A lock is not a flag on the data and not a property of a page in memory, it is a record in a separate structure that outlives any individual page access and is consulted before one.

What a lock protects

A logical item, usually a row or a key, rather than a physical location. The lock survives the page being evicted, the row moving within its page, and the index being reorganised.

This distinction is the reason locks and latches are different things. A latch protects a physical structure for the duration of one operation, and a lock protects a logical item for the duration of a transaction, which can be many seconds and many thousands of page accesses.

Why the two are not interchangeable

A latch is held for microseconds, is not tracked for deadlock, and is released by the code that took it. A lock is held for the length of a transaction, is tracked, and may be waited on by many transactions at once.

Keeping them separate prevents a common confusion when reading engine documentation. The same engine will report latch contention and lock contention as unrelated metrics, and they have different causes and different remedies, with latch contention being a throughput problem and lock contention a concurrency design problem.

The two basic modes

Shared, taken to read, compatible with other shared claims. Exclusive, taken to write, compatible with nothing. Any number of readers may hold the item together, and a writer holds it alone.

These two modes and their compatibility are the whole foundation. Every refinement that follows is either a finer mode or a claim on a different kind of item, and the compatibility question is always the same one: may these two claims coexist.

Why the compatibility table is the entire interface

Every grant decision is a lookup in it. The lock manager has no understanding of what the data means, only of whether an incoming request conflicts with what is already held.

Recognising this makes the manager's behaviour predictable. It cannot be clever about intent, it cannot notice that two writes would have been harmless, and it cannot know that a reader was only going to display the value, which is precisely why it is cheap enough to consult millions of times a second.

Why an update is not a third mode

Reading a row and then writing it means taking a shared claim and later converting it to exclusive. Some engines offer an intermediate mode for that case, and the underlying operation is still a conversion.

Naming the conversion matters because it is a distinct source of trouble. Two transactions each holding a shared claim and each wanting to convert it will wait for each other forever, which is a deadlock with a different shape from the usual one and a different remedy.

Why reading can require a lock at all

Without one, a reader can see a value that a concurrent writer is about to change, and any decision based on it can be invalidated before the transaction ends.

Explaining the purpose rather than asserting the rule is what makes the next section comprehensible. The read lock does not protect the read, which has already happened, it protects the fact that the value is still what was read, which is the expensive write protection in a different guise.

Why version-based engines mostly avoid read locks

A reader given a consistent snapshot of committed data does not need to hold anybody off, because nothing another transaction does can change what the snapshot shows.

Mentioning this here prevents the lesson being read as describing all engines. Pure locking for reads survives mainly in engines without versioning and in the specific cases where an application asks for it deliberately, which is the explicit read lock covered at the end.

What granularity means

The size of the item a lock covers: a row, a page, an index key range, a table, or the whole database. Finer granularity allows more concurrency and costs more memory and more bookkeeping.

Framing it as a trade rather than a setting is accurate. A million row locks permit a million independent writers and require a million entries in the manager, and a single table lock requires one entry and permits one writer.

Why the choice cannot be made once

A transaction touching three rows wants row locks, and one touching every row in a table wants a table lock, so the right granularity depends on the statement rather than on the schema.

This is what motivates the hierarchy described later. An engine that must choose in advance will be wrong for half its workload, so the mechanism has to let a transaction hold claims at more than one size at once.

Why lock memory is a real constraint

Each entry costs bytes and each waiting request costs more, so a transaction modifying millions of rows can exhaust the manager's memory before it exhausts anything else.

Knowing this explains a class of otherwise baffling failures. A bulk update that fails with an out-of-locks error has not hit a data limit or a disk limit, it has filled a fixed structure whose size is usually configurable and usually left at its default.

Why locks are not written to the log

They exist only in memory and only for the duration of a transaction, because after a crash no transaction is running and there is nothing left to protect.

Stating this closes a question the durability lessons raise naturally. Recovery re-establishes locks only for transactions it intends to leave open, which happens in distributed commit, and otherwise the whole structure is simply rebuilt empty.

Why the mechanism is still dominant

It is simple, it has no bookkeeping cost for transactions that do not conflict beyond the lock entries themselves, and it degrades by making work wait rather than by making it fail.

Being fair about its merits is worthwhile given the criticism that follows. Waiting is often preferable to aborting, because a waiting transaction eventually succeeds without the application doing anything, while an aborted one requires correct retry logic that many applications lack.

Why it nonetheless has a bad reputation

Lock contention is invisible until it is severe, deadlocks arrive as unexplained errors, and the mechanism's costs appear as latency rather than as anything named after locking.

Acknowledging the reputation sets up the operating section. The mechanism is not subtle, and the difficulty is almost entirely in observing it, which is why the practical material concentrates on what to measure rather than on what to configure.

What to take from locking

Three things. A lock is a record in a separate structure claiming a logical item for the duration of a transaction, which is why it is a different thing from a latch protecting a physical structure for one operation, and the two appear as unrelated metrics with unrelated remedies. The compatibility table is the entire interface, so the manager cannot be clever about intent, which is exactly what makes it cheap. And granularity is a trade that cannot be settled once, because the right size depends on the statement rather than on the schema.