What Are ACID Transactions? Principles, Examples & Benefits

Partial completion is often worse than outright failure in payments, inventory, bookings, and identity records. Database transactions address that risk while controlling what concurrent operations can observe and ensuring that committed changes survive a crash.

This post explains each ACID property, follows a transaction from BEGIN to COMMIT, looks under the hood at concurrency control and recovery, and compares how SQL and NoSQL databases define the scope of their guarantees.

What are ACID transactions?

An ACID transaction is a group of database operations governed by four properties: atomicity, consistency, isolation, and durability. The acronym comes from Theo Härder and Andreas Reuter's 1983 paper on transaction-oriented database recovery, which brought these requirements into one framework.

The transaction is the unit to which the guarantees apply. It may contain one statement, such as changing a user's email address, or many statements, such as creating an order, reserving stock, recording a payment authorization, and writing an audit entry. The business event may involve services outside the database, but an ordinary database transaction covers only the operations performed within its defined database scope.

Consider an order for the last unit of a product. The application must add the order and reduce inventory together. If it creates the order but fails to reserve the item, the customer has bought stock that the system does not have. If it reserves the item but loses the order, inventory disappears without a customer record. A transaction makes the two writes one commit decision.

ACID is a guarantee model, not a product category. Relational databases are closely associated with it, but document, key-value, and other NoSQL systems can also provide ACID transactions. The useful questions are which operations participate, how much data a transaction can span, what isolation level applies, and where the durability boundary ends.

Why ACID transactions matter

Transactions give application developers a controlled answer to failure and concurrency. Without them, every multi-step write needs its own compensation logic, intermediate-state tracking, and crash recovery. That logic becomes difficult to reason about once several clients can modify the same records at the same time.

They prevent partial business events. An order, booking, or account transfer often maps to writes in several rows or collections. Atomic commit keeps an error after the first write from leaving half of the event visible.

They preserve invariants under contention. Constraints and transaction logic can enforce rules such as nonnegative inventory, unique usernames, balanced ledger entries, or one reservation per seat. Isolation matters here. Two transactions that are correct alone can still violate a rule if they both read the same old value and then commit conflicting decisions.

They make recovery predictable. A client can learn that its transaction committed, rolled back, or reached an uncertain outcome because the connection failed during commit. Databases maintain logs and recovery procedures so that a process or machine crash does not leave half-written pages as the accepted state.

They create a trustworthy system of record. Downstream search indexes, caches, event consumers, and analytical systems need a coherent committed state to read from. Patterns such as a transactional outbox use the same database transaction to write both an application change and a publishable event record, closing a common gap between database commit and message publication.

ACID does have a cost. Concurrency control can make transactions wait, conflict, abort, or retry. Logging adds I/O. Coordination grows more expensive as a transaction spans shards, replicas, or regions. Good systems therefore keep transactions short and give each one the smallest scope that still protects the business invariant.

ACID also does not mean secure. It does not authenticate users, authorize access, encrypt data, validate untrusted input, or prevent fraud. It can preserve a security-related invariant when the schema and transaction logic express one, but access control and cryptographic protection are separate layers.

How ACID transactions work

A transaction moves through a short lifecycle even when its internals are complex.

1. The application starts a transaction, explicitly with BEGIN or an equivalent API call, or implicitly through the database's autocommit behavior.
2. It reads the state needed to make a decision and issues one or more writes.
3. The database checks syntax, data types, constraints, and conflicts as the statements run. Depending on the engine and isolation level, it may acquire locks, create new row versions, or record read and write dependencies.
4. The application requests COMMIT. The database decides whether the whole unit can commit and makes the decision durable before reporting success.
5. If a statement fails, the application cancels the work, a conflict forces an abort, or the connection closes before completion, the database rolls the transaction back. Some errors leave the commit outcome uncertain to the client, so applications need idempotency or a way to check the result safely.

The order example can be expressed as one SQL transaction:

BEGIN;

UPDATE inventory
SET available = available - 1
WHERE product_id = 42
  AND available > 0;

-- The application verifies that exactly one row changed.
INSERT INTO orders (order_id, product_id, quantity, status)
VALUES (9001, 42, 1, 'confirmed');

COMMIT;

The available > 0 predicate turns the stock check into part of the write instead of relying on an earlier, potentially stale application read. The application must still verify that the update affected one row. If it affected none, it should not insert the order and should roll the transaction back. ACID makes the chosen statements reliable; it does not decide whether the statements implement the right business rule.

The database exposes no intermediate state after the inventory update. Other transactions see a state allowed by their isolation level, and after commit they can see both changes. If the insert violates a constraint or the application rolls back, neither change remains.

The four ACID properties explained

The properties work together, but each addresses a different failure mode.

Atomicity

Atomicity is the all-or-nothing rule. Every write in a transaction becomes effective, or none does. In the order example, the inventory decrement and order insert share one outcome. A rollback may undo changes already made in memory, while a crash-recovery process may use log records to determine which changes should be redone or undone. The visible promise is the same either way: no partial transaction becomes committed state.

Atomicity does not mean that an individual machine instruction happens instantaneously. It means observers cannot accept a partly committed result. It also stops at the transaction boundary. If an application charges a card through an external API and then writes an order locally, a database rollback cannot reverse the card charge. Cross-system workflows usually need idempotent operations, an outbox, or a saga with explicit compensation.

Consistency

Consistency means a successful transaction takes the database from one valid state to another. Validity comes from the schema and application rules: primary keys remain unique, foreign keys point to existing rows, check constraints hold, and business invariants encoded in transaction logic remain true.

The database can enforce only the rules it knows. If no constraint or transaction logic prevents a negative balance, the engine can commit one without violating its declared rules. Consistency therefore depends on both database design and application behavior.

ACID consistency is also different from CAP consistency. In ACID, consistency means that a committed transaction preserves the database's declared validity rules. In CAP, consistency means atomic or linearizable behavior: operations appear to occur in one real-time-respecting order, so a read beginning after a write completes returns that value or one from a later write. During a network partition, a system cannot guarantee both this consistency and availability. A database can offer ACID transactions within one scope while using asynchronous replication, and therefore expose a different consistency model, outside that scope.

Isolation

Isolation controls how concurrent transactions affect one another. At the strongest level, serializable isolation guarantees an outcome equivalent to running committed transactions one at a time in some order. The engine may still execute them concurrently, but it aborts or delays combinations that cannot be reconciled with a serial order.

Weaker isolation levels permit more concurrency but expose more anomalies. A dirty read observes another transaction's uncommitted data. A nonrepeatable read gets a different committed value when it reads the same row twice. A phantom appears when repeating a predicate query returns a different set of matching rows. Snapshot-based implementations can prevent those named anomalies and still allow a broader anomaly such as write skew, where two transactions read the same snapshot, update different rows, and jointly violate an invariant.

The SQL isolation-level names do not describe every engine identically. PostgreSQL, for example, documents that its Repeatable Read level prevents all three SQL-standard phenomena but can still produce serialization anomalies. Applications that depend on a cross-row invariant should reason from the engine's documented behavior, not from the level's name alone. Serializable transactions may fail with a serialization error, and retrying the complete transaction is part of using the guarantee correctly.

Durability

Durability means that once the database reports a successful commit, the change survives the failures covered by its durability contract. Engines commonly implement this with a write-ahead log. The database records enough information to recover a change and flushes the relevant log record to durable storage before acknowledging commit. After a crash, it replays committed records that had not yet reached the main data files.

PostgreSQL's write-ahead logging documentation describes the ordering precisely: changes to data files are written only after the corresponding log records have been flushed to permanent storage. This lets recovery redo a committed change without forcing every modified table page to disk at commit time.

Durability still has a defined boundary. Configuration can trade durability for latency, storage hardware can break its write promises, and a local commit does not necessarily mean a remote replica has received the change. Backups also remain necessary. Durability protects committed work from specified failures; it does not protect against accidental deletion, malicious changes, or the loss of every copy.

How database transactions work

The SQL keywords describe intent. The database engine turns that intent into concurrency control, logging, and recovery.

Concurrency control determines visibility. Lock-based engines prevent conflicting operations by making one transaction wait or fail. Multi-version concurrency control (MVCC) keeps multiple row versions so readers can use a consistent snapshot while writers create newer versions. Most mature engines combine versions, locks, and conflict detection rather than relying on only one mechanism.

A transaction manager tracks state. It assigns transaction identifiers, records which versions a transaction may see, and coordinates the final commit or abort decision. PostgreSQL documents explicit transactions created with BEGIN or START TRANSACTION, ended with COMMIT or ROLLBACK, and implicit single-statement transactions for statements outside an explicit block.

Constraints are checked at defined times. A uniqueness violation may be detected as soon as a statement runs. Some systems also support deferred constraints checked at commit. A transaction that cannot satisfy its constraints is rejected rather than allowed to publish an invalid state.

The recovery subsystem records intent before data pages. Write-ahead logging separates the logical commit decision from the slower task of updating every affected page in its permanent location. Checkpoints periodically bring data files forward, while the log covers changes after the last checkpoint. Undo information or old row versions handle aborted work; redo information restores committed work after a crash.

Distributed transactions add coordination. When one atomic unit spans several database nodes or independent resource managers, a coordinator may use a protocol such as two-phase commit. Participants first prepare and promise they can commit, then receive the final decision. This expands atomic scope, but it adds network round trips and difficult failure states. Many distributed applications instead keep the database transaction local and coordinate services through messages, idempotency keys, and compensating actions.

This machinery explains the main operational rule: keep transactions short. A transaction that waits for user input or an external network call may retain locks, old versions, and other resources while increasing the chance of a conflict.

ACID transactions in SQL databases

SQL databases make transactions a central part of their programming model. Statements enclosed by BEGIN and COMMIT form an explicit transaction, while ROLLBACK abandons the unit. Savepoints provide a nested recovery point inside the transaction:

BEGIN;

INSERT INTO orders (order_id, customer_id, status)
VALUES (9001, 73, 'pending');

SAVEPOINT before_line_items;

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (9001, 42, 1);

-- If the line item fails, revert to the savepoint or roll back everything.
COMMIT;

Savepoints do not normally create independently durable nested transactions. Rolling back to one cancels work after that point, while the surrounding transaction still needs a final commit.

Relational design works closely with ACID consistency because the schema can declare primary keys, unique constraints, foreign keys, and check constraints. The application can update normalized records in several tables without exposing an intermediate join result. That makes SQL databases a natural fit for ledgers, order processing, reservations, and other online transaction processing workloads.

Implementation and configuration still matter. MySQL's documentation describes how InnoDB transactions, locking, crash recovery, buffers, and durability settings interact with the ACID model. The qualification is important: a database brand can support ACID while another storage engine, table type, replication mode, or durability setting within the same product changes the effective guarantee.

Isolation is also a deliberate choice rather than a checkbox. A weaker default may be correct for a high-throughput workload whose statements already update records conditionally. A correctness rule that spans several rows may require explicit locking, a serializable transaction with retries, or a schema change that lets the database enforce the invariant directly. The right level is the weakest one that has a clear argument for preserving the workload's actual rules.

ACID transactions in NoSQL databases

Today, transaction guarantees depend on the specific system and operation scope rather than the SQL or NoSQL category. NoSQL is an umbrella over document, key-value, wide-column, and graph models, and each system defines transaction scope differently.

Document databases often make a single document the natural atomic unit. MongoDB states that each single-document operation is atomic, which lets an embedded order and its line items change together without a multi-document transaction. It also supports transactions across operations, collections, databases, documents, and shards. Its documentation warns that distributed transactions cost more than single-document writes and should not replace a data model that keeps data updated together in one document.

Key-value systems can expose transactions through dedicated APIs rather than SQL-style session boundaries. Amazon DynamoDB groups condition checks and item writes into an all-or-nothing TransactWriteItems call, and groups reads into TransactGetItems. Its transaction documentation explicitly provides ACID guarantees across items and tables. The scope has a boundary: those guarantees apply within the AWS Region where the transaction runs, not across replicas in global tables.

These examples show why an evaluator should ask concrete questions instead of asking whether a NoSQL database is ACID compliant:

• Is atomicity limited to one item, document, partition, shard, or region?
• Can one transaction span collections or tables?
• Which reads participate, and what snapshot or consistency level do they use?
• What conflicts cause an abort, and does the client library retry safely?
• When is a commit durable locally, and when is it visible on remote replicas?
• Are transaction size, duration, or operation count bounded?

NoSQL systems often make transaction boundaries explicit in the data model. Keeping facts that change together in one document or partition can avoid distributed coordination. When a business invariant truly spans those boundaries, use the database's transactional API and design for its limits rather than recreating atomicity in application code.

Conclusion

ACID is a compact way to reason about four separate promises. Atomicity prevents partial commits. Consistency preserves the rules the schema and application actually declare. Isolation keeps concurrent work from producing disallowed outcomes. Durability carries a successful commit through the failures covered by the database's storage and replication contract.

Those promises are strongest when their scope is explicit. Engineers need to know which statements share a transaction, which anomalies the isolation level permits, what a client should retry, and whether durability ends at one process, one node, one region, or a synchronized replica. The SQL or NoSQL label cannot answer those questions on its own.

For relationship-heavy analysis, PuppyGraph can map existing tables in SQL databases, warehouses, and lakehouses to nodes and edges, then run openCypher and Gremlin queries over them without graph-specific ETL. The source remains the system of record, so its transaction and governance model continue to define committed data while the graph layer handles multi-hop queries in its own distributed engine.

Try the forever-free PuppyGraph Developer Edition and book a demo with the team to see how openCypher and Gremlin queries run over warehouse and lakehouse tables, with no graph-specific ETL, while the source systems retain their transactional boundaries and committed state.