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

by Martin Kleppmann

index simply point to all versions of an object and require an index query to filter out any

For example, PostgreSQL has optimizations for avoiding index updates if different versions of the same object can fit on the same page

??

they use an append-only/copy-on-write variant that does not overwrite pages of the tree when they are updated, but instead creates a new copy of each modified page.

append-only B-trees, every write transaction (or batch of transactions) creates a new B-tree root, and a particular root is a consistent snapshot of the database at the point in time when it was created.

However, this approach also requires a background process for compaction and garbage collection.

Snapshot isolation

In Oracle it is called serializable, and in PostgreSQL and MySQL it is called repeatable read

Instead, it defines repeatable read, which looks superficially similar to snapshot isolation.

PostgreSQL and MySQL call their snapshot isolation level repeatable read because it meets the requirements of the standard,

the SQL standard’s definition of isolation levels is flawed — it is ambiguous, imprecise, and not as implementation-independent as a standard should be

ostensibly

nobody really knows what repeatable read means.

Preventing Lost Updates

(a read-modify-write cycle).

Atomic write operations

atomic update operations, which remove the need to implement read-modify-write cycles in application code.

UPDATE counters SET value = value + 1 WHERE key = 'foo';

MongoDB provide atomic operations for making local modifications to a part of a JSON document,

Redis provides atomic operations for modifying data structures such as priority queues.

Not all writes can easily be expressed in terms of atomic operations — for example, updates to a wik...

exclusive lock on the object when it is read so that no other transaction can read it until the update has been applied.

cursor stability

simply force all atomic operations to be executed on a single thread.

object-relational mapping frameworks make it easy to accidentally write code that performs unsafe read-modify-write cycles instead of using atomic operations provided by the database

for the application to explicitly lock objects that are going to be updated.

FOR UPDATE clause indicates that the database should take a lock on all rows returned by this query.

An alternative is to allow them to execute in parallel and, if the transaction manager detects a lost update, abort the transaction and force it to retry its read-modify-write cycle.

this approach is that databases can perform this check efficiently in conjunction with snapshot isolation.

Some authors [28, 30] argue that a

database must prevent lost updates in order to qualify as providing snapshot isolation, so MySQL does not provide snapshot isolation under this definition.

?

databases that don’t provide transactions, you sometimes find an atomic compare-and-set operation

this operation is to avoid lost updates by allowing an update to happen only if the value has not changed since you last read it.

However, if the database allows the WHERE clause to read from an old snapshot, this statement may not prevent lost updates,

Check whether your database’s compare-and-set operation is safe before relying on it.

Conflict resolution and replication

Locks and compare-and-set operations assume that there is a single up-to-date copy of the data.

databases with multi-leader or leaderless replication usually allow several writes to happen concurrently and replicate them asynchronously,

techniques based on locks or compare-and-set do not appl...

resolve and merge these versions after the fact.

Atomic operations

For example, incrementing a counter or adding an element to a set are commutative operations.

Unfortunately, LWW is the default in many replicated databases.

Write Skew and Phantoms

Your requirement of having at least one doctor on call has been violated.

This anomaly is called write skew

This is like vinyaas like application?

write skew as a generalization of the lost update problem. Write skew can occur if two transactions read the same objects, and then update some of those objects (different transactions may update different objects). In the special case where different transactions update the same object, you get a dirty write or lost update anomaly

Automatically preventing write skew requires true serializable isolation

Some databases allow you to configure constraints, which are then enforced by the database (e.g., uniqueness, foreign key constraints, or restrictions on a particular value).

you would need a constraint that involves multiple objects. Most databases do not have built-in support for such constraints, but you may be able to implement them with triggers or materialized views, depending on the database

the second-best option in this case is probably to explicitly lock the rows that the transaction depends on.