ConceptDBISAI-written · not yet proofread

ACID

Also ACID Properties

ACID (atomicity, consistency, isolation, durability) are the four properties a database guarantees for transactions so that changing data stays correct under failures and concurrent access.

Properties#

Why transactions#

So far tables were read once and then only queried (e.g. a new Wikidata dump each month, for which one builds a new database). In many applications the data changes all the time (bank balances, product prices, grades in HISinOne) via

INSERT INTO movies VALUES('Forrest Gump', 1994, 8.8);
UPDATE movies SET rating = 8.9 WHERE title = 'Inception';
DELETE FROM movies WHERE year < 2000;

With changing data, errors become dangerous: a failed read-only query can simply be asked again, but a money transfer that subtracted the amount from account $X$ without adding it to account $Y$ leaves the data wrong.

Property	Guarantee	Threat addressed
Atomicity	all-or-nothing, no partial updates (rollback to the state before the transaction)	failure in the middle of a transaction
Consistency	valid state → valid state	violation of integrity constraints
Isolation	concurrent execution ≡ some sequential execution	interference of concurrent transactions
Durability	committed changes survive crashes	power loss, hardware failure

Example#

ExampleMoney transfer

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'X';
UPDATE accounts SET balance = balance + 100 WHERE id = 'Y';
COMMIT;

Atomicity: if the system fails after the first UPDATE, neither change is applied.
Consistency: if a constraint balance >= 0 would be violated for $X$ , the transaction fails.
Isolation: a concurrent transfer from $X$ sees either both updates or none, as if the transfers ran one after the other.
Durability: after COMMIT returns, the transfer survives a crash.

(The SQL syntax for transactions and constraints is not on the slides.)

Sources#

Slides 06, p. 22–24 (changing tables), p. 25 (transactions), p. 26 (ACID)
DBIS_VL06 > ACID