What is ACID? How do Atomicity and Isolation work in PostgreSQL?

ACID is a set of properties of transactions in databases that ensure reliability and consistency of data:

• Atomicity — a transaction is completed in full or not at all. In PostgreSQL, this is achieved through a rollback mechanism if any operation within the transaction fails.

• Consistency — a transaction transforms the database from one correct state to another, adhering to all constraints and rules.

• Isolation — concurrent transactions do not affect each other, as if they were executed sequentially. PostgreSQL implements transaction isolation levels (Read Committed, Repeatable Read, Serializable) that control the visibility of changes between transactions.

• Durability — after a transaction is committed, changes are saved even in case of failures.

Example of Atomicity and Isolation in PostgreSQL:

BEGIN;
-- several insert/update operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- if something goes wrong, rollback changes
COMMIT;

If an error occurs during one of the operations, PostgreSQL will rollback all changes, ensuring atomicity. Isolation guarantees that other transactions will not see intermediate states before commit.