Skip to content
SQLTransactions and Indexesadvanced25 min read

ACID Properties

Understand Atomicity, Consistency, Isolation, and Durability, and learn how databases protect data during transactions, concurrent operations, and failures.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Define Atomicity, Consistency, Isolation, and Durability with practical examples
  • ✓Explain how COMMIT and ROLLBACK support transaction reliability
  • ✓Understand dirty reads, non-repeatable reads, and phantom reads
  • ✓Compare Read Committed, Repeatable Read, and Serializable isolation levels
  • ✓Demonstrate transaction behavior using PostgreSQL
  • ✓Apply ACID principles when designing reliable backend operations

Introduction to ACID Properties

ACID describes four important properties that help relational databases process transactions reliably.

Imagine transferring ₹1,000 from Account A to Account B. The database must deduct ₹1,000 from A and add ₹1,000 to B. If only one operation succeeds, the account balances become incorrect.

ACID helps prevent this kind of problem.

The four properties are:

1. Atomicity: All operations in the transaction succeed together, or none of their changes are committed.
2. Consistency: A successful transaction preserves the database's defined rules and constraints.
3. Isolation: Concurrent transactions are controlled so that their interactions follow the selected isolation level.
4. Durability: Once a transaction is committed, its changes are retained according to the database's durability configuration and guarantees.

ACID does not mean that a database can automatically detect every business mistake. For example, a transaction that accidentally transfers money to the wrong account may satisfy ACID while still being logically incorrect.

The database guarantees reliable transaction processing; the application and schema must define the correct business rules.

Atomicity: All or Nothing

Atomicity prevents a transaction from leaving only part of its intended database changes committed.

Consider a bank transfer:

- Deduct ₹1,000 from Account A.
- Add ₹1,000 to Account B.
- Commit both changes together.

If the second operation fails and the transaction is rolled back, the first change must not remain committed.

COMMIT makes the transaction's changes permanent according to the database's transaction guarantees. ROLLBACK cancels the uncommitted changes made by the current transaction.

Atomicity applies to the database transaction. It does not automatically undo an email, an HTTP request, or another external action performed by your application.

Atomicity in PostgreSQL

The following example creates a temporary account table and performs a transfer inside a transaction.

A temporary table exists only for the database session that creates it. The initial setup is committed before the transfer begins so that the example clearly separates setup from transaction changes.

The balance CHECK constraint prevents negative account balances.

Bank Transfer with COMMIT

Bank Transfer with COMMIT
sql
CREATE TEMP TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);
 
INSERT INTO accounts (account_id, balance)
VALUES
    (1, 5000.00),
    (2, 2000.00);
 
BEGIN;
 
UPDATE accounts
SET balance = balance - 1000.00
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 1000.00
WHERE account_id = 2;
 
COMMIT;
 
SELECT account_id, balance
FROM accounts
ORDER BY account_id;
Output
account_id | balance ------------+--------- 1 | 4000.00 2 | 3000.00 (2 rows)

The first account loses ₹1,000, and the second account receives ₹1,000. Both updates are committed as part of the same transaction. The total balance remains ₹7,000. The example assumes both account IDs exist and both updates execute successfully. Production code should also verify that the expected rows were updated and handle errors appropriately.

Atomicity with ROLLBACK

ROLLBACK cancels changes made during the current uncommitted transaction.

In this example, both balances are updated, but the transaction is rolled back instead of committed. The balances therefore return to their values before BEGIN.

Run this example after creating and populating the accounts table in the preceding example, in the same session.

Cancel an Uncommitted Transfer

Cancel an Uncommitted Transfer
sql
BEGIN;
 
UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 2;
 
ROLLBACK;
 
SELECT account_id, balance
FROM accounts
ORDER BY account_id;
Output
account_id | balance ------------+--------- 1 | 4000.00 2 | 3000.00 (2 rows)

The updates temporarily changed the balances inside the transaction. ROLLBACK canceled those uncommitted changes, so the previously committed balances remain unchanged. A rollback cannot undo a transaction that has already been committed. A later correction would require another transaction.

Consistency: Preserve Database Rules

Consistency means that a successful transaction preserves the database's defined integrity rules.

These rules can include:

- PRIMARY KEY: prevents duplicate primary-key values.
- FOREIGN KEY: ensures that referenced rows exist according to the constraint.
- UNIQUE: prevents duplicate values for the constrained columns.
- NOT NULL: prevents NULL values in the constrained column.
- CHECK: requires a specified condition to hold for each affected row.

In the account example, CHECK (balance >= 0) prevents an account balance from becoming negative.

If an update violates that constraint, PostgreSQL rejects the statement. If the statement runs inside an explicit transaction, the transaction enters an aborted state unless the error is handled through an appropriate savepoint or rollback.

Consistency does not mean that every possible business rule is automatically enforced. Rules that are not represented by database constraints or correct transaction logic may still be violated.

Consistency Example: CHECK Constraint

The following example demonstrates a constraint that prevents negative inventory quantities.

Prevent Negative Inventory

Prevent Negative Inventory
sql
CREATE TEMP TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
 
INSERT INTO inventory (product_id, product_name, quantity)
VALUES (1, 'Keyboard', 10);
 
SELECT product_name, quantity
FROM inventory;
Output
product_name | quantity --------------+---------- Keyboard | 10 (1 row)

The CHECK constraint requires quantity to be zero or greater. Try executing UPDATE inventory SET quantity = -1 WHERE product_id = 1;. PostgreSQL rejects the update because it violates the constraint. The original quantity remains 10. If the failing update is executed inside an explicit transaction, issue ROLLBACK or recover through a savepoint before continuing with other statements in that transaction.

Isolation: Control Concurrent Transactions

Isolation describes how concurrent transactions interact and which changes they can observe.

Imagine two users attempting to buy the final item in stock at almost the same time. Without suitable coordination, both applications might incorrectly decide that the item is available.

Database isolation mechanisms help control concurrent access. Depending on the database and isolation level, they may use locks, multiversion concurrency control (MVCC), or other techniques.

Isolation is not simply an on/off switch. Different isolation levels permit different observations and concurrency behaviors.

Important concurrency phenomena include:

1. Dirty read: a transaction reads changes that another transaction has not committed.
2. Non-repeatable read: reading the same row twice within a transaction returns different committed values.
3. Phantom read: repeating a query that matches a condition returns a different set of rows.

PostgreSQL uses MVCC, or multiversion concurrency control, to help readers and writers work concurrently. Its supported isolation levels have PostgreSQL-specific behavior that is important to understand.

Dirty Reads, Non-Repeatable Reads, and Phantom Reads

Dirty read:
Transaction A updates a balance but has not committed. Transaction B reads the uncommitted value. If A rolls back, B has observed a value that never became committed data.

PostgreSQL does not allow dirty reads. Even when READ UNCOMMITTED is requested, PostgreSQL behaves as READ COMMITTED.

Non-repeatable read:
Transaction A reads a customer's balance. Transaction B changes and commits that balance. If A reads the same row again under READ COMMITTED, it may see the new value.

Phantom read:
Transaction A queries all orders with an amount greater than 100. Transaction B inserts another qualifying order and commits. When A repeats the query, it may find an additional row under an isolation level that permits this behavior.

The distinction is useful:

- A non-repeatable read concerns a row already read whose values change.
- A phantom read concerns the set of rows matching a condition changing.

Exact behavior depends on the database system and isolation level. In PostgreSQL, REPEATABLE READ uses a stable transaction snapshot and prevents ordinary non-repeatable and phantom reads. SERIALIZABLE adds protection against serialization anomalies by requiring concurrent transactions to behave as though they ran in some serial order, aborting transactions when necessary.

Isolation Levels in PostgreSQL

PostgreSQL supports three distinct standard isolation-level behaviors:

- READ COMMITTED: each statement generally sees a snapshot of data committed before that statement began.
- REPEATABLE READ: statements in the transaction use a stable snapshot established by the first relevant statement.
- SERIALIZABLE: provides serializable isolation, detecting potentially unsafe concurrent executions and sometimes aborting transactions.

PostgreSQL accepts READ UNCOMMITTED, but implements it with READ COMMITTED behavior.

Choose an isolation level based on the correctness requirements of the operation, the application's concurrency model, and the cost of retries.

Set the isolation level at the beginning of a transaction, before running queries that would prevent changing it.

Set a Transaction Isolation Level

Set a Transaction Isolation Level
sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
SELECT account_id, balance
FROM accounts
ORDER BY account_id;
 
COMMIT;
 
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
SELECT account_id, balance
FROM accounts
ORDER BY account_id;
 
COMMIT;
 
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
SELECT account_id, balance
FROM accounts
ORDER BY account_id;
 
COMMIT;
Output
Each transaction returns the committed account rows visible to its snapshot. For the example's initial committed account data: account_id | balance ------------+--------- 1 | 4000.00 2 | 3000.00 (2 rows) The same rows are returned by each transaction when no concurrent transaction changes the data.

These examples demonstrate how to declare each isolation level. They do not, by themselves, demonstrate a concurrency anomaly because no concurrent changes are shown. The example assumes the accounts table exists in the current session. The SELECT result is identical for all three transactions when the underlying visible data has not changed.

Durability: Committed Data Persists

Durability means that after a transaction is committed, its changes are retained even if the database subsequently encounters a failure covered by its durability guarantees.

Database systems typically use transaction logs and recovery mechanisms to restore committed changes after a crash. PostgreSQL uses write-ahead logging (WAL).

In simplified terms, write-ahead logging ensures that the required log records are made durable before corresponding data-page changes must be relied on for crash recovery.

Durability still depends on the database configuration and the failure scenario. For example, synchronous replication settings influence whether a commit waits for acknowledgment from a standby server.

A successful COMMIT is not the same as a guarantee that every possible disaster, storage failure, or loss of all replicas is impossible. Backups, replication, monitoring, and recovery testing remain essential.

Durability Example: Verify a Committed Change

This example commits a change and then queries the row in a subsequent transaction.

The SELECT demonstrates that the committed value is visible afterward in the same database session. It is not a simulated crash-recovery test; crash recovery and replication durability require separate operational tests.

Commit and Read the Stored Value

Commit and Read the Stored Value
sql
BEGIN;
 
UPDATE accounts
SET balance = balance + 250.00
WHERE account_id = 1;
 
COMMIT;
 
SELECT account_id, balance
FROM accounts
WHERE account_id = 1;
Output
account_id | balance ------------+--------- 1 | 4250.00 (1 row)

The committed balance is visible to the subsequent query. In a properly configured database, the transaction log and recovery system protect committed changes against the failures covered by the system's durability guarantees. The example assumes account 1 exists and begins with a balance of 4000.00.

Putting All Four ACID Properties Together

Consider a bank transfer of ₹500 from Account A to Account B.

Atomicity:
Both balance updates belong to one transaction. If the transaction is rolled back, neither update remains committed.

Consistency:
Constraints and transaction logic preserve required rules, such as nonnegative balances. The application must also verify that both accounts exist and that the expected updates actually occurred.

Isolation:
Concurrent transfers must not create incorrect balances or violate business invariants. Locks, appropriate isolation levels, and careful transaction design help protect these operations.

Durability:
Once the transfer is committed, the database retains the committed changes according to its durability configuration.

A production implementation should validate the amount, confirm both accounts, handle insufficient funds, check affected row counts, and respond correctly to transaction failures. It should also avoid reporting success until the database transaction has committed.

ACID and Backend Application Design

Backend services often perform several database operations as part of one business action.

Examples include:
- Creating an order and its order items.
- Reserving inventory while creating a purchase.
- Updating an account balance and recording a ledger entry.
- Creating a payment record alongside related database state.

When these operations must succeed together, place them in an appropriate database transaction.

Important practices:

1. Keep transactions as short as practical.
2. Validate inputs before opening a transaction when validation does not depend on mutable database state.
3. Enforce important invariants using database constraints where possible.
4. Lock rows or select an appropriate isolation level when concurrent updates can conflict.
5. Handle deadlocks and serialization failures with bounded retries where safe.
6. Do not assume a transaction can roll back an external payment, email, or HTTP request.
7. Use patterns such as a transactional outbox when database changes must reliably trigger external events.
8. Configure backups and recovery procedures to support operational durability.

For example, a checkout service may commit an order and an inventory reservation in one database transaction. Sending an email afterward is an external side effect and is not automatically included in that transaction.

Common Mistakes

ACID concepts are easy to memorize but can be misunderstood when applied to real systems. Focus on what the database actually guarantees, what the application must implement, and which concurrency behaviors the selected isolation level permits.

Practice Exercises

Exercise 1: Atomicity
Create a transaction that changes two account balances. Roll it back and verify that both balances remain unchanged.

Exercise 2: Consistency
Create a table with a CHECK constraint that prevents negative inventory. Try to update the quantity to a negative number and explain the error.

Exercise 3: Isolation
Explain how two transactions using READ COMMITTED can read different values for the same row when one transaction commits an update between the reads.

Exercise 4: Isolation Levels
Describe the main difference between PostgreSQL READ COMMITTED and REPEATABLE READ.

Exercise 5: Durability
Explain why a committed database change should be paired with suitable backup, replication, and recovery practices.

Exercise 6: Backend Design
A checkout operation must create an order, create its order items, and reserve inventory. Identify which database operations should belong in the same transaction and which external actions are outside that transaction.

Exercise 7: Concurrency
Explain why SERIALIZABLE transactions may fail with a serialization error and why an application may need to retry the complete transaction.

Knowledge Check

Question 1: What does Atomicity mean?

A. Every query runs faster.
B. A transaction's changes succeed together or are not committed.
C. All users see every uncommitted change.
D. Every table must have an index.

Answer: B. Atomicity prevents partial commitment of a transaction's changes.

Question 2: Which ACID property focuses on database rules and constraints?

A. Atomicity
B. Consistency
C. Isolation
D. Durability

Answer: B. Consistency means successful transactions preserve defined database invariants.

Question 3: What is a dirty read?

A. Reading uncommitted changes made by another transaction.
B. Reading a table without an index.
C. Reading the same committed row twice.
D. Reading data after a backup.

Answer: A. A dirty read exposes another transaction's uncommitted changes. PostgreSQL prevents dirty reads, including when READ UNCOMMITTED is requested.

Question 4: Which PostgreSQL isolation level uses a stable transaction snapshot for ordinary reads?

A. READ COMMITTED
B. REPEATABLE READ
C. READ UNCOMMITTED with its distinct standard behavior
D. No isolation level

Answer: B. PostgreSQL REPEATABLE READ uses a stable snapshot and prevents ordinary non-repeatable and phantom reads.

Question 5: What is the primary purpose of Durability?

A. Prevent duplicate rows in every table.
B. Make queries execute in parallel.
C. Retain committed changes according to the database's durability guarantees.
D. Prevent all application errors.

Answer: C. Durability protects committed transaction changes against covered failures.

Question 6: What may happen when a PostgreSQL SERIALIZABLE transaction encounters a serialization conflict?

A. The database silently commits both conflicting transactions in every case.
B. PostgreSQL may abort a transaction, requiring the application to retry it safely.
C. All constraints are automatically disabled.
D. The transaction becomes READ UNCOMMITTED.

Answer: B. Serializable isolation may reject a transaction to prevent a nonserializable outcome.

Interview Questions and Answers

Q1. What are ACID properties?

Answer:
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees that make database transactions reliable.

Q2. Explain Atomicity with an example.

Answer:
During a bank transfer, the sender's balance is reduced and the receiver's balance is increased. Both updates should be committed together. If the transaction fails before commit and is rolled back, neither update remains committed.

Q3. How does a database maintain Consistency?

Answer:
It enforces defined rules through constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK, along with correct transaction logic. Business rules that are not encoded or validated can still be violated.

Q4. What is Isolation?

Answer:
Isolation defines how concurrent transactions interact and which data changes they can observe. Its exact behavior depends on the database and selected isolation level.

Q5. What is the difference between READ COMMITTED and REPEATABLE READ in PostgreSQL?

Answer:
READ COMMITTED generally obtains a new snapshot for each statement. REPEATABLE READ uses a stable transaction snapshot for ordinary reads, so repeated reads do not observe newly committed changes from other transactions in that snapshot.

Q6. Does PostgreSQL allow dirty reads?

Answer:
No. PostgreSQL's READ UNCOMMITTED behaves like READ COMMITTED, so uncommitted changes from another transaction are not exposed as dirty reads.

Q7. What is a phantom read?

Answer:
A phantom read occurs when repeating a query that matches a condition returns a different set of rows because of concurrent changes. PostgreSQL REPEATABLE READ prevents ordinary phantom reads using its stable snapshot.

Q8. How does PostgreSQL support Durability?

Answer:
PostgreSQL uses write-ahead logging and recovery mechanisms to protect committed changes against covered failures. Actual guarantees also depend on settings, storage, replication, and the failure scenario.

Q9. What is the difference between ROLLBACK and a serialization failure?

Answer:
ROLLBACK is a command that cancels the current transaction's uncommitted changes. A serialization failure is an error indicating that a transaction cannot safely complete under SERIALIZABLE isolation. The application may need to roll back and retry the entire transaction.

Q10. Does ACID cover an email sent by a backend service?

Answer:
No. A database transaction cannot automatically undo an email or another external side effect. A transactional outbox or another reliable integration pattern can coordinate database changes with external processing.

Q11. Why might a transaction be retried?

Answer:
Deadlocks, serialization failures, and certain transient errors can prevent a transaction from completing. Retrying should be bounded, safe, and applied to the appropriate transaction boundary.

Q12. Is ACID enough to make a banking system correct?

Answer:
No. ACID provides transaction guarantees, but the application still needs correct business logic, authorization, validation, concurrency control, auditability, security, and operational recovery procedures.

Lesson Summary

In this lesson, you learned the four ACID properties:

- Atomicity: a transaction's changes are committed together or canceled before commit.
- Consistency: successful transactions preserve defined constraints and invariants.
- Isolation: concurrent transactions follow the visibility and conflict rules of the selected isolation level.
- Durability: committed changes are retained according to the database's durability guarantees.

You also learned the key PostgreSQL isolation levels, the meanings of dirty reads, non-repeatable reads, and phantom reads, and why applications must handle concurrency errors safely.

Remember: ACID is essential for reliable transactions, but it does not replace correct business logic, constraints, safe external-side-effect handling, backups, or recovery planning.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming two related updates are atomic without a transaction

Why it happens: Developers may execute multiple UPDATE statements independently and assume the database treats them as one business operation.

Incorrect Approach
sql
sql
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
Correct Solution
sql
sql
BEGIN;
 
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;
 
COMMIT;

Key takeaway: When the operations must succeed together, use a transaction and handle failures. Also verify that the expected accounts were updated; a transaction alone does not guarantee that the SQL expresses the intended business operation.

#2 Believing ACID automatically enforces every business rule

Why it happens: Consistency is misunderstood as a guarantee that the database will recognize every invalid business outcome.

Incorrect Approach
sql
sql
-- This update can be logically wrong if the
-- application has not validated the transfer.
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
Correct Solution
sql
sql
-- Define a database-level invariant.
CREATE TEMP TABLE stock_example (
    product_id INTEGER PRIMARY KEY,
    quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
 
INSERT INTO stock_example (product_id, quantity)
VALUES (1, 10);
 
-- This update is rejected because it violates CHECK.
UPDATE stock_example
SET quantity = -1
WHERE product_id = 1;

Key takeaway: ACID preserves the rules that are actually enforced by constraints and correct transaction logic. Define business invariants explicitly and validate operation outcomes.

#3 Assuming READ COMMITTED gives the same snapshot to every query in a transaction

Why it happens: Developers confuse transaction boundaries with snapshot behavior.

Incorrect Approach
sql
sql
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
SELECT balance
FROM accounts
WHERE account_id = 1;
 
-- Another transaction may update and commit this row.
 
SELECT balance
FROM accounts
WHERE account_id = 1;
 
COMMIT;
Correct Solution
sql
sql
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
SELECT balance
FROM accounts
WHERE account_id = 1;
 
-- Subsequent reads use the transaction's stable snapshot.
 
SELECT balance
FROM accounts
WHERE account_id = 1;
 
COMMIT;

Key takeaway: Under PostgreSQL READ COMMITTED, each statement generally sees a new snapshot. REPEATABLE READ uses a stable snapshot for ordinary reads within the transaction. Concurrent updates can still cause conflicts that the application must handle.

#4 Assuming a successful COMMIT protects against every possible disaster

Why it happens: Durability is treated as an absolute guarantee independent of configuration and infrastructure.

Incorrect Approach
sql
sql
-- A commit does not replace backups,
-- replication planning, or recovery testing.
BEGIN;
 
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 1;
 
COMMIT;
Correct Solution
sql
sql
-- Combine transactional correctness with
-- an operational recovery strategy:
-- 1. Use appropriate WAL and durability settings.
-- 2. Configure backups and retention.
-- 3. Test restoration and disaster recovery.
-- 4. Monitor storage and replication health.

Key takeaway: A commit provides the database's configured transaction durability guarantee. Backup, replication, and recovery procedures protect against additional failure scenarios.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • Atomicity means a transaction succeeds completely or its changes are rolled back.
  • Consistency means a transaction must preserve the database rules and constraints.
  • Isolation controls how concurrent transactions interact and what changes they can observe.
  • Durability means committed changes survive subsequent failures within the database's durability guarantees.
  • Isolation levels balance concurrency, consistency, and the possibility of transaction retries.
  • ACID is a set of transaction guarantees, not a substitute for correct schema design or application logic.
Was this lesson helpful?
Found an error? Report an issue