Explain ACID with a bank transfer example

ACID are four guarantees of a database transaction. A transfer of 100 from account 1 to account 2 shows each one.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

• Atomicity: both updates happen or neither does. If the second fails, the first is rolled back.
• Consistency: the transaction moves the data from one valid state to another. Constraints such as CHECK (balance >= 0) hold, and the total money is unchanged.
• Isolation: concurrent transfers do not see each other's half-finished work. How strictly depends on the isolation level.
• Durability: once COMMIT returns, the result survives a crash, because the change is already in the write-ahead log on disk.

Atomicity and durability come from the transaction log. Isolation comes from locking or multi-version concurrency control. Consistency is a shared job of the constraints you define and the application logic.