Transactions and ACID

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.

A transfer as one transaction

If either update fails, ROLLBACK undoes both.

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.

त्वरित जाँच: Which ACID property guarantees that a committed transaction survives a power failure?

• Durability
• Atomicity
• Consistency
• Isolation