Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems

by Martin Kleppmann

snapshot isolation does not prevent another user from concurrently inserting a conflicting meeting.

Phantoms causing write skew

However, the other four examples are different: they check for the absence of rows matching some search condition,

can’t attach locks to anything.

where a write in one transaction changes the result of a search query in another transaction, is called a phantom

Materializing conflicts

can artificially introduce a lock object into the database?

You create rows for all possible combinations of rooms and time periods ahead of time, e.g. for the next six months.

This approach is called materializing conflicts, because it takes a phantom and turns it into a lock conflict on a concrete set of rows that exist in the database

it can be hard and error-prone to figure out how to materialize conflicts, and it’s ugly to let a concurrency control mechanism leak into the application data model.

materializing conflicts should be considered a last resort if no alt...

Serializable isolation is usually regarded as the strongest isolation level.

But if serializable isolation is so much better than the mess of weak isolation levels, then why isn’t everyone using

will discuss these techniques primarily in the context of single-node databases;

The simplest way of avoiding concurrency problems is to remove the concurrency entirely:

that a single-threaded loop for executing transactions was feasible

RAM became cheap enough that for many use cases it is now feasible to keep the entire active dataset in memory

transactions can execute much faster

However, its throughput is limited to that of a single CPU core.

a database transaction needs to wait for input from a user, the database needs to support a potentially huge number of concurrent transactions,

and so almost all OLTP applications keep transactions short by avoiding interactively waiting for a user within a transaction.

systems with single-threaded serial transaction processing don’t allow interactive multi-statement transactions.

the application must submit the entire transaction code to the database ahead of time, as a stored procedure.

Provided that all data required by a transaction is in memory, the stored procedure can execute very fast, without waiting for any network or disk I/O.

badly written stored procedure (e.g., using a lot of memory or CPU time) in a database can cause much more trouble than equivalent badly written code in an application server.

transactions on a single thread

they don’t need to wait for I/O and they avoid the overhead of other concurrency control mechanisms, they can achieve quite good throughput on a single thread.

also uses stored procedures for ...

order to scale to multiple CPU cores, and multiple nodes, you can potentially partition your data

In this case, you can give each CPU core its own partition,

for any transaction that needs to access multiple partitions, the database must coordinate the transaction across all the partitions that it touches.

Since cross-partition transactions have additional coordination overhead, they are vastly slower than single-partition transactions.

two-phase locking (2PL).

But as soon as anyone wants to write (modify or delete) an object, exclusive access is required:

If transaction A has read an object and transaction B wants to write to that object, B must wait until A commits or aborts before it can continue. (This ensures that B can’t

If transaction A has written an object and transaction B wants to read that object, B must wait until A commits o...

(Reading an old version of the object, like in Figure 7-4, is ...

In 2PL, writers don’t just block other writers; they also block re...

because 2PL provides serializability,

The lock can either be in shared mode or in exclusive mode.

Several transactions are allowed to hold the lock in shared mode simultaneously, but if another transaction already has an exclusive lock on the object, these transactions must wait.

acquire the lock in exclusive mode.

No other transaction may hold the lock at the same time (either in shared...

object, it may upgrade its shared lock to an exclusive lock.

hold the lock

What if DB crashes and lock is never, released?

deadlock.

The database automatically detects deadlocks between transactions and aborts one of them so that the others can make progress.

The big downside of two-phase locking,

is performance:

This is partly due to the overhead of acquiring and releasing all those locks, but more importantly due to reduced concurrency.