Transactions

Transaction simplifies fault tolerance, especially on distributed systems. A transaction groups several reads and writes together into a logical unit that either succeeds (commit) or fails (abort into rollback) together. Retrying a transaction is safe, because there will not be any partial failures.

Transaction creates a safety gurarantee, with some cost attributed to it, with differing levels of guarantee. Weakening transactional guarantee that is not needed can improve performance and availability.

ACID Guarantee

ACID implementation details can differ from one database to another. The guarantee on each aspects of ACID can differ greatly.

Systems that does not meet ACID criteria are sometimes called BASE (basically available, soft state, eventual consistency). However, this term is even more ambiguous than ACID.

Atomicity

Atomicity deals with what happens when a client is attempting multiple writes, but a fault occurs after some of the writes have been processed. Writes that are grouped together in one atomic operation (transaction) cannot be completed / committed when a fault happens (transaction aborted). The database then has to discard or undo (rollback) any writes that has been made in the operation.

Another word to describe this is abortability. Without atomicity guarantee, it is hard for the application to determine which operations has been completed and requires rollback.

Consistency

Consistency in the context of ACID, refers to the database being in a “good state”. The application expects the data to be in a certain state (invariant). When a transactino starts with a valid state, the completion (commit) or cancellation (rollback) must leave the database in a valid state.

Only the application knows the condition of the invariant, therefore must define its transaction correctly. While the database can validate certain invariant (unique constraints for example), it cannot validate all invariants. Therefore C, doesn’t really belong in ACID. The application can use the database’s isolation and atomicity to ensure its consistency.

Isolation

Race condition can happen when multiple processes reads / writes the same parts of the dataset. Isolation means that concurrently executing transactions are isolated from each other.

This is distinct from serializability, referring to serializable isolation, that guarantees that multiple transactions executes as if it had been run serially (one after another) even though it may have run concurrently.

Durability

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

In a single node database, durability means that data has been stored to non-volatile storage (hard drive / ssd). In a replicated database, durability means that data has been copied to some number of nodes, often (r/2 + 1) aka quorum. Database must wait until the writes or replications complete before reporting a transaction completion.

In practice, there are no perfect durability, only risk reduction techniques:

Writing to non-volatile storage
Replication
Backups

Race Condition Problems

Dirty Reads

Dirty read means this transaction can see writes from other transactions that has not been committed.

Dirty Writes

Dirty write means this transaction can overwrite changes from other transaction that has not been committed.

Phantom Reads

Phantom read means another transaction has changed the set of data that is selected (for update / locking) or updated by the condition specified (WHERE clause). This creates a problem when different rows are supposed to be updated in this transaction if it had been started after the other transaction has completed.

Lost Updates

Lost update problem can occur when an application reads some value from the database, and writes back a modified value (read - modify - write cycle). When multiple transactions are running concurrently, one of the updates may be lost.

Example:

Value in database = 100
T1 reads
T2 reads
T1 adds 200
T2 adds 100
T1 writes
T2 writes
Value in database is now 200 instead of 400 (T1 modification is lost)

This can be prevented by using atomic update clause (update set row + 1) or explicit locking.

Isolation Levels

Concurrency bugs are hard to find by testing, because such bugs are only trigerred when you get unlucky with the timings. Database systems tries to hid concurrency problems by providing transaction isolation.

Read Committed

Prevents:

Dirty reads
Dirty writes

Repeatable Read