> For the complete documentation index, see [llms.txt](https://ai.nuhil.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ai.nuhil.net/data-engineering/sql/acid.md).

# ACID

## Atomicity

[Atomicity](https://en.wikipedia.org/wiki/Atomicity_\(database_systems\)) 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. 

```sql
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](https://en.wikipedia.org/wiki/Consistency_\(database_systems\)) ensures that a transaction can only bring the database from one consistent state to another, preserving database [invariants](https://en.wikipedia.org/wiki/Invariant_\(computer_science\)): any data written to the database must be valid according to all defined rules, including [constraints](https://en.wikipedia.org/wiki/Integrity_constraints), [cascades](https://en.wikipedia.org/wiki/Cascading_rollback), [triggers](https://en.wikipedia.org/wiki/Database_trigger), and any combination thereof.

<figure><img src="/files/6bkSPbJ8yGtsoB835x98" alt=""><figcaption></figcaption></figure>

## Isolation

Transactions are often executed [concurrently](https://en.wikipedia.org/wiki/Concurrent_computing) (e.g., multiple transactions reading and writing to a table at the same time). [Isolation](https://en.wikipedia.org/wiki/Isolation_\(database_systems\)) 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](https://en.wikipedia.org/wiki/Durability_\(computer_science\)) 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](https://en.wikipedia.org/wiki/Crash_\(computing\))). This usually means that completed transactions (or their effects) are recorded in [non-volatile memory](https://en.wikipedia.org/wiki/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](https://en.wikipedia.org/wiki/Write-ahead_logging) and [shadow paging](https://en.wikipedia.org/wiki/Shadow_paging).&#x20;

In both cases, [locks](https://en.wikipedia.org/wiki/Lock_\(computer_science\)) 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.&#x20;

#### 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.&#x20;

#### Shadowing

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