ACID Properties Explained in Detail

1. What ACID means and why transactions need it

The ACID properties are not an academic concept but the concrete promise a relational database makes for every transaction. The acronym stands for Atomicity, Consistency, Isolation, and Durability, describing four independent guarantees that together ensure data stays correct even under concurrency, failures, and power outages. Without these guarantees, every application would have to track on its own which change was already written, which is still pending, and which needs to be undone.

In practice, you encounter ACID properties wherever several related changes must count as one logical unit: a transfer that changes two accounts at once, an order that reduces stock and creates an order record, or a registration that creates a user and a profile in separate tables. The SQL standard defines the transaction as the building block through which these four properties are guaranteed, and every serious relational database implements them, even if with different technical means.

The following sections work through each of the four ACID properties separately, each with a concrete example of what actually happens without that particular guarantee. Only then does it become clear why ACID should be thought of as a complete package rather than four separate features.

2. Atomicity: all or nothing

Atomicity guarantees that a transaction is either executed completely or none of its changes become visible at all. There is no intermediate state where only half the statements of a transaction take effect. The classic example is a bank transfer: an amount is debited from account A and credited to account B. Both operations must succeed together or fail together, otherwise money vanishes or gets duplicated.

Without atomicity, the following could happen: the debit from account A is successfully written, but before the credit to account B, the process crashes or a network connection drops. Without a rollback mechanism, account A stays permanently debited while account B never receives the money. Across thousands of daily transactions, such gaps add up to tangible financial damage that can only be repaired afterward through tedious manual reconciliation.

-- Atomicity in action: both changes succeed or neither does
BEGIN;

UPDATE accounts SET balance = balance - 250.00 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 250.00 WHERE account_id = 2002;

-- Guard: balance must not go negative
-- If the condition fails, the whole transaction is discarded
DO $$
BEGIN
  IF (SELECT balance FROM accounts WHERE account_id = 1001) < 0 THEN
    RAISE EXCEPTION 'Insufficient funds on account 1001';
  END IF;
END $$;

COMMIT;
-- On RAISE EXCEPTION an implicit ROLLBACK is triggered,
-- both UPDATE statements are then discarded, neither one is left dangling

Technically, databases implement atomicity using undo logs or multi-version mechanisms: every change is first recorded in a log or a new row version, and becomes visible only at COMMIT time. If an error occurs before that, the database uses this log to undo all changes already written by the transaction. For the application, this means: an explicit ROLLBACK in the error path is good practice, but even an abruptly terminated process without a clean ROLLBACK leaves no half-written transaction behind, because the database automatically rolls back incomplete transactions on the next startup.

3. Consistency: integrity always holds

Consistency means that a transaction may only move the database from one valid state to another valid state. Valid here means: all defined constraints, foreign keys, unique indexes, and CHECK conditions remain satisfied after the COMMIT. Unlike atomicity, isolation, and durability, which the database engine guarantees on its own, consistency is a shared responsibility between the database and the application design, the database only enforces the rules that were actually defined as constraints.

A concrete example: a table orders references a table customers via a foreign key. Without the consistency guarantee, a transaction could create an order with a customer id that does not exist, because the corresponding customer was deleted concurrently. The result would be orphaned records that produce NULL values or missing rows in reports, exports, or joins, without any obvious error occurring. Such inconsistencies often only surface weeks later, when a report shows incorrect totals.

-- Enforcing consistency through constraints
CREATE TABLE customers (
  customer_id  INT PRIMARY KEY,
  email        VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id     INT PRIMARY KEY,
  customer_id  INT NOT NULL REFERENCES customers(customer_id),
  total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0)
);

-- This transaction fails due to the foreign key rule,
-- because customer_id 9999 does not exist
BEGIN;
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (5001, 9999, 149.90);
-- ERROR: insert or update on table "orders" violates
-- foreign key constraint "orders_customer_id_fkey"
ROLLBACK;

4. Isolation: keeping concurrent transactions cleanly separated

Isolation governs how much concurrently running transactions are allowed to see of each other before they commit. In theory, every transaction should behave as if it were the only one on the system, but this full isolation costs performance because it can require locking or serialization in the worst case. The SQL standard therefore defines four isolation levels as graduated trade-offs between correctness and concurrency, from READ UNCOMMITTED to SERIALIZABLE.

Without sufficient isolation, several anomalies can occur: a transaction reads data that another, not-yet-committed transaction wrote (dirty read), or a transaction reads the same row twice and gets different values because another transaction committed in between (non-repeatable read). A concrete practical example: two parallel order processes both read the current stock level of ten units, both reduce it independently by five, and write it back. Without sufficient isolation, the final stock level ends up at five instead of zero, even though fifteen units were reserved. Details on the four isolation levels and the specific anomalies are covered in the deep-dive article on isolation levels and the article on dirty reads, phantom reads, and non-repeatable reads.

5. Durability: once committed, stored forever

Durability guarantees that a successfully committed transaction survives an immediate server crash, a power outage, or a kernel panic. As soon as the client receives confirmation for a COMMIT, the database must ensure that this data resides permanently on persistent storage, not just in volatile memory. Technically this is implemented via a write-ahead log: before actually writing the data pages, every change is first written to a sequential log, and that log is physically forced to disk with an fsync call.

Without durability, the following could happen: an e-commerce application shows the customer a confirmation page for their order after checkout, because the database confirmed the COMMIT. The server crashes three seconds later due to an operating system fault. On restart, the order is missing because it only lived in the operating system's page cache and was never written to disk. The customer has a confirmation, the system has no order, trust in the platform is damaged.

-- Durability-relevant settings, vendor specific

-- PostgreSQL: forces a physical fsync on every COMMIT
-- (default value "on", never disable for production data)
SET synchronous_commit = on;

-- MySQL/InnoDB: writes and flushes the redo log on every COMMIT
-- Value 1 is the ACID-compliant setting
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- Value 2 writes the log but leaves the fsync to the operating system
-- Faster, but on an OS crash (not a DB crash) the last one or two
-- seconds of committed transactions can be lost
-- SET GLOBAL innodb_flush_log_at_trx_commit = 2;  -- not ACID-compliant

These settings show a typical trade-off: full durability costs latency, because every COMMIT waits for a physical disk access. Some systems deliberately loosen this guarantee for performance, for example for session data or analytics events, where losing a few seconds is acceptable. For financial transactions, orders, and anything that must be legally provable, the full durability setting is not negotiable.

6. How the four properties interact in practice

The four ACID properties do not act independently of each other, they interlock within the lifecycle of a single transaction. Atomicity ensures that all statements between BEGIN and COMMIT are treated as a unit. Consistency ensures this unit only commits when all constraints are satisfied. Isolation governs what concurrent transactions see of this unit before it completes. Durability guarantees the result stays permanent after a successful COMMIT.

-- All four ACID properties demonstrated in one transaction
BEGIN;                                    -- Start of the atomic unit

-- Isolation: other transactions only see these changes
-- after COMMIT (depending on the isolation level)
UPDATE inventory SET stock = stock - 3
WHERE product_id = 42 AND stock >= 3;     -- Consistency: prevents negative stock

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (7781, 42, 3);

-- Atomicity guard: was a row actually updated?
-- If stock < 3, the UPDATE affects 0 rows, no error raised
-- Application code must explicitly check ROW_COUNT / affected rows

COMMIT;                                   -- Durability: fsync-secured from here
-- Only after this point is the result guaranteed to survive a crash

This interplay also explains why one rarely talks about "one" ACID property without the other three. A database offering atomicity without isolation would never commit half a transaction, but concurrent transactions could still show each other inconsistent intermediate states. Only all four together deliver the promise developers usually take for granted from a relational database without ever questioning it explicitly.

7. ACID vs. BASE: when consistency matters more than availability

Many NoSQL systems advertise the counter-model BASE, Basically Available, Soft State, Eventually Consistent. The difference to ACID properties is not a quality judgment but a deliberate trade-off following the CAP theorem: during a network partition, a distributed system must give up either consistency or availability. Relational databases with ACID choose consistency when in doubt, many distributed NoSQL systems choose availability.

For use cases like financial transactions, inventory management, contract data, or anything with legal proof requirements, strict ACID compliance is practically non-negotiable, because a briefly inconsistent account balance causes real damage. For use cases like social media feeds, like counters, or product recommendations, eventual consistency is often sufficient, because a delay of a few seconds in visibility causes no measurable harm but increases system availability during network issues. The choice between ACID and BASE should therefore always be derived from the consequences of an inconsistency, not from a general trend.

8. ACID in practice: implementation across database engines

Not every database engine implements the ACID properties fully or with the same means. The classic difference in MySQL illustrates this well: the InnoDB storage engine is fully ACID-compliant including transactions, foreign keys, and crash recovery, while the older MyISAM engine has no transaction support and can leave inconsistent tables after a crash. Anyone still running MyISAM tables today is, knowingly or not, giving up atomicity and durability.

Even among fully ACID-compliant systems there are differences in default configuration and implementation details, for example in the default isolation level or the behavior of DDL statements within a transaction. The table below summarizes the most important differences.

9. Common mistakes when working with transactions

The most common mistake in working with ACID properties is autocommit, which is enabled by default in many drivers and ORMs. Every single SQL statement is then treated as its own transaction, so related changes lose their atomicity even though the database itself is ACID-compliant. A second common mistake is the lack of an explicit ROLLBACK in the exception handler: if an application simply closes the connection after an error without explicitly rolling back, the transaction stays open and, in the worst case, blocks locks for other sessions.

-- WRONG: autocommit turns two related UPDATEs
-- into two independent mini-transactions
UPDATE accounts SET balance = balance - 250 WHERE account_id = 1001;
-- Crash right here: account 1001 stays debited, account 2002
-- never receives the credit, because each statement committed immediately
UPDATE accounts SET balance = balance + 250 WHERE account_id = 2002;

-- RIGHT: an explicit transaction bracket enforces atomicity
BEGIN;
UPDATE accounts SET balance = balance - 250 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 250 WHERE account_id = 2002;
COMMIT;

-- RIGHT: exception handling with an explicit ROLLBACK
-- (pseudocode for application logic, modeled after common driver APIs)
-- try {
--   connection.execute("BEGIN");
--   connection.execute(updateStatement1);
--   connection.execute(updateStatement2);
--   connection.execute("COMMIT");
-- } catch (error) {
--   connection.execute("ROLLBACK");
--   throw error;
-- }

A third mistake concerns long-running transactions: a transaction that stays open for minutes because it waits on an external API or user input holds locks in many isolation levels or prevents cleanup of old row versions. The result is growing undo segments, bloated tables, and in extreme cases blocked transactions for other users. Transactions should be kept as short as possible, with all slow, non-database operations happening outside of BEGIN and COMMIT.

10. Summary

The ACID properties Atomicity, Consistency, Isolation, and Durability are four separate but interacting guarantees that relational databases give for every transaction. Atomicity prevents half-written changes, consistency enforces integrity rules, isolation protects concurrent transactions from interfering with each other, and durability secures committed data against crashes. Each of these properties solves a concrete, real-world problem, not a theoretical one.

In practice, the value of ACID shows up most where it is forgotten: autocommit, missing constraints, isolation levels that are too weak, or disabled durability guarantees silently lead to data inconsistencies that often only become visible weeks later as broken reports or wrong account balances. Anyone who brackets transactions explicitly, defines constraints consistently, and understands the trade-offs of isolation levels uses their database's full ACID properties instead of unknowingly undermining them.

11. FAQ: ACID Properties Explained in Detail