# 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.
## 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).
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.
#### 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.
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://ai.nuhil.net/data-engineering/sql/acid.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.