ACID

Atomicity

Definition

A transaction is treated as a single "atom." Either every statement in the transaction succeeds, or the entire thing is rolled back, leaving the database unchanged.

Implementation

• Mysql: Uses the Undo Log to revert changes if a transaction fails. If you use the InnoDB engine (the default), you get this protection.
• Postgres: Uses a combination of WAL and a system called MVCC (Multiversion Concurrency Control). It essentially tracks the state of data "versions" to ensure it can discard uncommitted changes instantly.

Consistency

definition

Consistency ensures that a transaction takes the database from one valid state to another, maintaining all predefined rules (constraints, cascades, triggers).

Implementation

There are two levels of consistency:

1. Data consistency
   • You have multiple views of your data (e.g foreign keys), will change in 1 view propagate to the other views.
   • This is achieved by
     • The user (database designer)
     • Referential integrity (foreign keys, cascade ...)
     • Atomicity and Isolation.
2. Read consistency
   • If TX1 updates a field, TX2 should read the new value.
   • SQL databases can guarantee read consistency in case of 1 Server
   • Horizontal scaling or Caching lead to read inconsistency => Eventual consistency.

Isolation

definition

This ensures that concurrent transactions (multiple people using the database at once) don't interfere with each other. It makes it appear as if transactions are running sequentially.

There are four main isolation levels you should know:

• Read Uncommitted: Can see "dirty" (uncommitted) data. -> Dirty reads
• Read Committed: (Postgres Default) You only see data once it's saved. -> Non-Repeatable Reads
• Repeatable Read: (MySQL Default) If you read a row twice in one transaction, the data won't change even if someone else updated it. -> Phantom reads
• Serializable: The strictest level; transactions behave as if they are the only ones running. -> Solves isolation but is very expensive.

There is a 5th non standard level called Snapshot: Like Repeatable Reads but it takes only TX committed before the current TX start.

Implementation

Both Postgres and MySQL use MVCC and Locking to implement isolation. The difference is that Postgres stores old versions of the rows in the tables and MySQL stores the old versions of the rows in the UNDO logs and includes a hidden DB_ROLL_PTR in each row that links it to its previous version in the undo log, creating a version chain.

MySQL calls Snapshots Read Views. New Read Views are created at each SELECT statement in READ COMMITTED mode and on the first SELECT on REPEATABLE READ.

MySQL then prevents Phantoms in REPEATABLE READ using Gap Locks and Next-Key Locks.

• Record Lock: Locks the actual index record.
• Gap Lock: Locks the "gap" between index records.
• Next-Key Lock: A combination of a record lock and a gap lock on the space before that record.

Durability

definition

Once a transaction is committed, it remains committed—even in the event of a system crash or power failure.

Implementation

Both databases use a Write-Ahead Log (WAL).

• MySQL: Calls this the Redo Log.
• Postgres: Calls this the WAL.

The database writes the intent of the change to a log file on the disk before updating the actual data files. If the system crashes, it simply replays the log to recover the data.