# Transactions and ACID — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/t-acid

> Define a transaction and the ACID properties, and trace transaction states.

## All or nothing, safely shared

A **transaction** is a sequence of operations that forms one logical unit of work, such as transferring money: debit one account, credit another. DBMSs guarantee the **ACID** properties. **Atomicity**: either all operations take effect or none do; if anything fails, changes are **rolled back**, using the log. **Consistency**: a transaction takes the database from one valid state to another, respecting constraints and business rules (the application must also write correct transactions). **Isolation**: concurrent transactions do not see each other's intermediate states; the result is as if they ran in some serial order, to the degree set by the isolation level. **Durability**: once committed, changes survive crashes, because they are recorded in a log on stable storage before commit is acknowledged. A transaction moves through states: **active**, **partially committed** (last statement executed), **committed**, or **failed** and then **aborted** (rolled back), after which it may be restarted or killed.

## Transaction state diagram

A transaction runs, then either commits or fails and is rolled back.

![Five circles connected by arrows: a start circle leading to a middle circle, which branches to a success path ending in a double circle and a failure path ending in another double circle.](assets/figures/database-fundamentals/section-5-map.svg) — Figure 5.1 — Active, partially committed, committed, failed and aborted states.

## A transfer as one transaction

If either update fails, ROLLBACK undoes both.

```sql
BEGIN;

UPDATE accounts SET balance = balance - 500
WHERE account_no = 'A-101' AND balance >= 500;     -- check: one row updated?

UPDATE accounts SET balance = balance + 500
WHERE account_no = 'B-202';

INSERT INTO transfers (from_acct, to_acct, amount, at)
VALUES ('A-101', 'B-202', 500, now());

COMMIT;          -- durable from here on
-- on any error: ROLLBACK;  -> no partial transfer is ever visible
```

## Consistency is shared work

The DBMS enforces declared constraints, but it cannot know that a transfer must debit and credit equal amounts unless you write the transaction correctly or add constraints. ACID's C depends on both.

**Quiz:** Which ACID property guarantees that a committed transaction survives a power failure?

- [x] Durability
- [ ] Atomicity
- [ ] Consistency
- [ ] Isolation

*Answer:* Durability. Durability is provided by writing changes to stable storage, typically via the log, before commit completes.