Chapter 7: Transactions

What are transactions?

A transaction is a way for an application to group several reads and writes together into a logical
unit.

Why do we need them?

Atomicity
Consistency
Isolation

Durability

Atomicity

atomicity describes what happens if a client wants to make several writes, but a fault
occurs after some of the writes have been processed

Consistency

ACID consistency is that you have certain statements about your data ( invariants ) that
must always be true

Isolation

Isolation in the sense of ACID means that concurrently executing transactions are isolated from
each other: they cannot step on each other’s toes.

Durability

Durability is the promise that once a transaction has committed successfully, any data it
has written will not be forgotten, even if there is a hardware fault or the database crashes.

Single-Object vs Multi-Object Transactions

Snapshot Isolation

Read Committed

When reading from the database, you will only see data that has been committed (no dirty
reads ).

When writing to the database you will only overwrite data that has been committed (no dirty writes)

Atomic Write Operations

Explicit Locking

BEGIN TRANSACTION ; SELECT * FROM figures WHERE name = 'robot' AND game_id = 222 FOR UPDATE ; Check whether move is valid, then update the position
of the piece that was returned by the previous SELECT.
UPDATE figures SET position = 'c4' WHERE id = 1234 ; COMMIT ; 

Kleppmann, Martin. Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems (Kindle Locations 6192-6201). O'Reilly Media. Kindle Edition.