ACID

Atomicity

Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

begin tran
update employee
set status = 'active'
insert into employee_salary (12345, 'some_miss_typed_value_here')
commit

The above transaction will be failed altogether, meaning even if the Update part of the statements was correct and supposed to update the value in employee table but it will be rolled back to its previous state because the following Insert statement was not successfully executed.

Consistency

Consistency ensures that a transaction can only bring the database from one consistent state to another, preserving database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

Isolation

Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Implementation

Processing a transaction often requires a sequence of operations that is subject to failure for a number of reasons. For instance, the system may have no room left on its disk drives, or it may have used up its allocated CPU time. There are two popular families of techniques: write-ahead logging and shadow paging.

In both cases, locks must be acquired on all information to be updated, and depending on the level of isolation, possibly on all data that may be read as well.

Write Ahead Log (WAL)

In write ahead logging, durability is guaranteed by copying the original (unchanged) data to a log before changing the database. That allows the database to return to a consistent state in the event of a crash.

Shadowing

In shadowing, updates are applied to a partial copy of the database, and the new copy is activated when the transaction commits.