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. Lost Updates
Write Skew and Phantoms
Serializability
Stored Procedures
Two-Phase Locking
Predicate Locks
Index Range Locks
Serializable Snapshot Isolation
Concurrency Control: Pessimistic vs Optimistic
Optimistic Concurrency Control
Pessimistic Concurrency Control
Fin!
Chapter 7: Transactions
By Jowanza Joseph
