What is ACID Transactions?

How it works

• Atomicity: every statement in the transaction takes effect, or none does. A failure halfway rolls the whole thing back.
• Consistency: a transaction moves the data from one valid state to another; constraints such as primary keys and CHECK rules are never left broken.
• Isolation: concurrent transactions behave as if they ran one after another, to the degree the isolation level promises (read committed, repeatable read, serializable). Most engines use MVCC, so readers see a snapshot instead of blocking writers.
• Durability: once the commit returns, the change survives a crash, because it is in a write-ahead log or committed object storage.

For data pipelines, atomicity and isolation matter most. A load that fails after writing half its rows must not leave half a day in the table, and a dashboard must not read a table while a job is replacing it. Relational databases and cloud warehouses give this out of the box. Plain files on S3 or ADLS do not, which is why Delta Lake, Apache Iceberg and Apache Hudi add a transaction log: a commit is a single atomic log entry that points at the new files.

Example

A transfer of 150 from A to B. The debit breaks the CHECK (balance >= 0) rule, so the transaction is rolled back and the credit to B, which had already run, disappears too (PostgreSQL 16).

CREATE TABLE accounts (id text PRIMARY KEY, balance numeric CHECK (balance >= 0));
INSERT INTO accounts VALUES ('A', 100), ('B', 50);

BEGIN;
UPDATE accounts SET balance = balance + 150 WHERE id = 'B';
UPDATE accounts SET balance = balance - 150 WHERE id = 'A';
COMMIT;

SELECT * FROM accounts ORDER BY id;

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (A, -50).
 id | balance
----+---------
 A  |     100
 B  |      50

Common mistakes

• Running a multi-step load as separate auto-committed statements, so a failure leaves partial results.
• Assuming the default isolation level is serializable; most databases default to read committed.
• Writing Parquet files straight into a table folder and calling it a transaction; without a log, readers can see a half-written load.
• Holding one long transaction open across slow external calls, which blocks other writers or causes conflicts.