Transaction Properties (ACID) in SQL

Introduction

ACID describes four properties that make database transactions dependable: atomicity, consistency, isolation, and durability. Together they define how related changes succeed or fail, how committed state obeys declared rules, how concurrent work interacts, and how accepted changes survive supported failures.

ACID is often summarized in one sentence, but production behavior depends on transaction boundaries, constraints, isolation implementation, logging, storage, retries, and distributed architecture. This tutorial develops each property through SQL examples, concurrency anomalies, recovery mechanisms, vendor differences, and realistic business workflows.

What Are ACID Properties?

PropertyCore questionPractical meaning
AtomicityDid the complete unit happen?All required changes commit or none do
ConsistencyDoes accepted state satisfy its rules?Implemented invariants remain true
IsolationHow does concurrent work interact?Specified anomalies are prevented or detected
DurabilityWill accepted work survive failure?Committed state is recoverable under documented guarantees

The properties reinforce one another, but each addresses a different risk. A transaction can be atomic yet contain logically wrong data if business validation is missing. It can be durable yet expose an application to lost updates if isolation is too weak for the workflow.

Why ACID Is Important

Applications change related data while processes crash, networks fail, storage stalls, and users act concurrently. Without controlled transaction properties, a debit could survive without its credit, duplicate seats could be booked, or a successful order could disappear after restart.

ACID gives engineers a framework for defining acceptable outcomes. It does not eliminate every failure; it ensures failures occur within known boundaries that applications can detect, roll back, retry, or reconcile.

Understanding a Database Transaction

A transaction is one logical unit of database work. It begins explicitly or implicitly, performs reads and writes, validates conditions, and ends with commit or rollback. The correct boundary follows a business invariant rather than an arbitrary number of SQL statements.

BEGIN;

UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 101
  AND balance >= 500.00;

UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 102;

COMMIT;

This transfer should commit only after both updates affect exactly one expected row and all currency and authorization rules pass.

Atomicity in SQL

Atomicity means a transaction is accepted as one indivisible unit. Intermediate statements may execute, but the database does not preserve a committed half-result. A successful commit accepts all retained work; rollback abandons it.

The atomic boundary is normally limited to resources participating in the transaction. A local SQL transaction does not automatically include a payment provider, file write, email, or independent message broker.

Atomicity with COMMIT and ROLLBACK

BEGIN;

INSERT INTO orders (order_id, customer_id, status)
VALUES (8810, 505, 'CREATED');

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (8810, 7001, 2);

-- Commit only when every required operation succeeds.
COMMIT;

If the item insertion fails, the application issues ROLLBACK, preventing an incomplete order header from remaining. Database constraints and exception handling make this outcome reliable.

Atomicity Across INSERT, UPDATE, and DELETE

A transaction can combine every data manipulation statement. An order cancellation may insert an audit record, update the order, restore inventory, and delete a temporary reservation. Atomicity covers the combined unit, not each statement in isolation.

Autocommit changes the boundary by treating each standalone statement as its own transaction. Related statements therefore require an explicit transaction; otherwise an earlier successful statement cannot be rolled back after a later failure.

Atomicity and SAVEPOINT

A savepoint creates a partial rollback marker but does not create an independent durability boundary. The application can discard an optional substep while retaining earlier work, yet the final outer commit or rollback still determines the transaction's outcome.

Use savepoints only when the retained state is valid without the failed substep. A mandatory inventory reservation cannot be made optional merely by surrounding it with a marker.

Consistency in SQL

Consistency means a successful transaction moves the database from one valid state to another according to rules actually implemented by the schema and transaction logic. Primary keys, foreign keys, unique constraints, checks, triggers, data types, and application validation all contribute.

The database cannot enforce a rule it does not know. If negative balances are allowed by the schema and code, committing one can be mechanically ACID while violating an unstated business requirement. Consistency is not automatic correctness.

Consistency with CHECK

CREATE TABLE accounts (
    account_id BIGINT PRIMARY KEY,
    balance DECIMAL(15, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT ck_accounts_balance
        CHECK (balance >= 0),
    CONSTRAINT ck_accounts_status
        CHECK (status IN ('ACTIVE', 'FROZEN', 'CLOSED'))
);

The checks reject invalid accepted state at the database boundary. Product-specific rules determine whether constraints can be deferred until commit.

Consistency with Keys

A primary key prevents duplicate or missing row identity. A foreign key ensures that a child references an allowed parent. A unique constraint protects alternate business identifiers such as email, order number, or idempotency key.

These rules also protect against concurrency races. Two requests can both believe an email is available, but the unique constraint decides which insertion can commit.

NOT NULL and Data Types

NOT NULL distinguishes mandatory fields from absent information. Suitable data types restrict representation: exact numeric types protect financial calculations, timestamps preserve temporal values, and bounded strings prevent unexpected data size.

Types alone do not provide domain meaning. A decimal amount may still need a positive-value check and a currency relationship. Layer constraints so invalid state is difficult to represent.

Consistency with Business Rules

Some invariants span rows or services and cannot be represented by a simple check. Examples include account limits based on customer tier, one active subscription across products, or order transitions that depend on payment state.

Enforce critical local rules with atomic SQL, stored procedures, locking, version columns, or serializable transactions. Application validation improves messages but should not be the sole defense against concurrent writes.

Isolation in SQL

Isolation controls how concurrent transactions observe and affect one another. The idealized serial model behaves as if transactions ran one at a time, but databases use locks, multiversion concurrency control, validation, or combinations to provide concurrency efficiently.

An isolation level is a contract about permitted behavior, not simply a performance switch. The same level name can have different implementation details across products, so test the actual engine.

Why Isolation Is Necessary

Without isolation, one transaction could read data another later abandons, overwrite a concurrent change, or make a decision from a set that changes midway. Such anomalies are often rare in development but frequent under production concurrency.

Correct isolation depends on the workflow. A reporting query may tolerate a changing view, while allocating the final seat or transferring money requires stronger coordination.

Standard Isolation Levels

LevelGeneral intentTypical tradeoff
Read UncommittedAllows the weakest read guaranteesHigh concurrency, potentially dirty reads
Read CommittedReads only committed versionsRepeated reads may change
Repeatable ReadStabilizes previously read dataMore retention, locking, or conflicts
SerializableEnforces an outcome equivalent to serial executionMore blocking or transaction aborts

SQL standards describe phenomena, while engines provide product-specific semantics. Snapshot isolation is another common model and is not identical to serializable isolation.

Read Uncommitted

Read Uncommitted may permit a transaction to observe another transaction's tentative work. If that writer rolls back, the reader acted on a value that never became committed. Some MVCC databases effectively provide stronger behavior even when this level is requested.

Use it only when the exact database behavior and stale or inconsistent results are acceptable. It is unsuitable for decisions that trigger writes or external actions.

Read Committed

Read Committed prevents ordinary dirty reads. Each statement commonly sees data committed before that statement or snapshot begins. A second query in the same transaction may therefore return a newer value or different set.

This level is a common default and works well for many short operations, but read-modify-write logic still needs atomic predicates, locks, or optimistic version checks.

Repeatable Read

Repeatable Read aims to keep previously read rows stable for the transaction. Lock-based implementations may retain read locks, while MVCC implementations may provide a transaction snapshot. Phantom and write-conflict behavior varies significantly by engine.

Do not infer complete serializability from stable repeated reads. Transactions can still make incompatible decisions about different rows under some implementations.

Serializable

Serializable provides the strongest standard isolation goal: committed transactions have an outcome equivalent to some serial order. Engines can achieve this through strict locking, predicate locks, serializable snapshot validation, or other methods.

Applications must expect blocking, deadlocks, or serialization failures and retry the complete transaction safely. Strong isolation improves correctness only when retry handling is part of the design.

Dirty Reads

A dirty read occurs when one transaction reads another's uncommitted value. If the writer rolls back, the read never represented accepted state. A report could show nonexistent revenue, or an application could make an invalid decision.

Read Committed and stronger mainstream levels prevent ordinary dirty reads, though exact visibility remains product-specific.

Non-Repeatable Reads

A non-repeatable read occurs when a transaction reads one row twice and gets different committed values because another transaction updated it between statements. The first value was valid when read, but it did not remain stable.

Repeatable Read commonly prevents this phenomenon. At Read Committed, code should avoid assuming that an earlier value remains current when it later performs an update.

Phantom Reads

A phantom occurs when repeating a predicate query returns added or removed rows due to another committed transaction. For example, the count of pending orders can change even if rows previously read remain stable.

Serializable isolation prevents standard phantom anomalies. Repeatable Read behavior varies, particularly between locking and snapshot implementations.

Lost Updates

A lost update happens when two transactions read one value, calculate changes independently, and the later write overwrites the earlier result. Both operations may report success even though one effect disappears.

Use atomic updates such as SET quantity = quantity - 1 with a safe predicate, row locks, serializable isolation, or optimistic version columns. Merely reading at Read Committed does not prevent every lost-update pattern.

Write Skew

Write skew occurs when concurrent transactions read a shared condition but update different rows, allowing a cross-row invariant to become false. Two doctors might each observe that another remains on call and independently mark themselves off duty.

Snapshot isolation can permit this anomaly because the writes do not directly conflict. Serializable isolation, explicit locking of a shared guard row, or a redesigned constraint can prevent it.

Snapshot Isolation

Snapshot isolation lets a transaction read from a consistent committed snapshot while writers create new row versions. Readers generally do not block writers, and writers generally do not block ordinary readers. If two transactions update the same row, one is commonly rejected or forced to retry.

This model prevents dirty reads and gives stable snapshot reads, but it is not automatically serializable. Write skew can occur when transactions update different rows after reading the same broader condition. Some databases offer a serializable snapshot mode that detects dangerous dependency patterns and aborts a participant.

Version retention has operational costs. Long-running snapshots can delay cleanup and increase storage pressure, so applications should close transactions promptly even when reads do not hold traditional locks.

Isolation Comparison

Choosing the strongest level for every transaction can reduce throughput and increase retries, while choosing the weakest can corrupt business outcomes. Start from the anomalies that would violate the invariant, then select the engine feature that prevents or detects them.

Document the choice near transaction code and test concurrent executions. Single-threaded unit tests cannot prove isolation correctness.

Durability in SQL

Durability means that once a commit is successfully acknowledged, the database can recover that committed state after failures covered by its guarantee. The scope may include process crashes, operating-system crashes, or storage failures depending on configuration and architecture.

Durability is not a promise against every disaster. A destroyed region, corrupted backup chain, or intentionally relaxed flush setting requires additional recovery design.

How Databases Provide Durability

Databases commonly record changes in transaction logs, redo logs, or write-ahead logs before modified data pages reach their final storage locations. At commit, the required log information is made durable according to configured policy. Recovery later replays committed changes and removes incomplete work.

Checksums, page recovery, mirrored logs, storage barriers, and replication can strengthen the design. Exact guarantees depend on the engine, operating system, filesystem, controller, and hardware.

Write-Ahead Logging

Write-ahead logging requires the recovery record to reach durable storage before the corresponding data page is considered safely persisted. This lets the database acknowledge commit without synchronously writing every scattered page.

Group commit can flush records for several independent transactions together, improving throughput while preserving their logical boundaries. Applications should normally rely on the engine rather than introducing artificial commit delays.

Recovery Processing

After restart, the database analyzes its logs, redoes changes from committed transactions that were not fully reflected in data files, and undoes or ignores incomplete transactions. Users see a consistent recovered state rather than half-written operations.

Recovery time depends on workload, checkpoint strategy, log volume, and engine design. Operational testing should include restart and restore exercises, not only successful query execution.

Durability Configuration Tradeoffs

Database products often expose settings controlling when commit records are flushed, how many replicas must acknowledge, and whether storage synchronization can be deferred. Relaxed settings can improve throughput or latency, but they may allow recently acknowledged transactions to disappear after a process, operating-system, power, or primary failure.

Changing such settings is a business decision as much as a technical one. Document the potential loss window, measure realistic workloads, validate storage behavior, and align the choice with recovery point objectives. A benchmark result is not sufficient justification for weakening an order, payment, or ledger guarantee.

Unknown Commit Outcomes

A client can send COMMIT and lose the connection before receiving the response. The database may have committed durably even though the application observed a timeout. Treating that timeout as a confirmed rollback and blindly retrying can duplicate an order, transfer, or payment record.

Assign a unique idempotency key before the transaction and store it with the business result. After an uncertain response, reconnect and query by that key. If the result exists, return the committed outcome; if durable state proves it did not happen, retry safely. ACID protects the database transaction, while idempotency resolves uncertainty across the network boundary.

Durability vs Backup

Durability protects committed work within the database's supported failure model. Backups protect against broader events such as accidental deletion, corruption, malware, or site loss. A durable wrong update is still wrong and may require point-in-time recovery.

Backups must be restorable, retained, encrypted, and tested. A backup that has never passed a restore drill is an assumption rather than a recovery capability.

Durability vs Replication

Replication copies changes to other database instances, but local commit may be acknowledged before asynchronous replicas receive or apply it. An immediate replica read can therefore return older data, and a primary loss can risk recent commits depending on failover policy.

Synchronous replication can wait for configured acknowledgments but increases latency and still has product-specific semantics. Replication improves availability; it does not replace versioned backups because mistakes replicate too.

COMMIT, ROLLBACK, and SAVEPOINT

COMMIT accepts the transaction and establishes its durability point. ROLLBACK enforces the failure side of atomicity by abandoning uncommitted work. SAVEPOINT provides partial recovery inside the transaction without independently committing anything.

These commands express application intent, while the engine's logging, concurrency, and recovery systems implement the guarantees.

ACID Transaction Lifecycle

  1. Define one business invariant and transaction boundary.
  2. Begin the transaction with an appropriate isolation level.
  3. Read or lock the state required for validation.
  4. Execute related writes using constraints and safe predicates.
  5. Verify affected rows and business conditions.
  6. Commit when all required work succeeds.
  7. Roll back after failure or retryable conflict.
  8. Resolve unknown outcomes using durable request identity.

Banking Example

A transfer demonstrates all four properties. Atomicity keeps debit and credit together. Consistency enforces valid accounts, currencies, amounts, and ledger rules. Isolation prevents concurrent withdrawals from spending the same balance. Durability preserves the acknowledged transfer after restart.

A production system also uses unique transfer identifiers, immutable ledger entries, exact numeric types, authorization, reconciliation, and controlled retry behavior. ACID supports these rules but does not invent them.

E-Commerce and Inventory Example

Order creation may include an order row, items, inventory reservation, and outbox event. A conditional inventory update prevents quantity from dropping below zero, while uniqueness and foreign keys protect identifiers and relationships.

Payment through an external provider is outside the local transaction. Use idempotent APIs, explicit order states, durable messages, and compensation rather than keeping database locks open during a remote call.

Ticket Booking Example

Concurrent users can select the same seat before either confirms. A unique constraint on event and seat provides a final consistency rule, while the transaction commits only one booking. The losing request receives a controlled conflict and rolls back.

A prior availability read is not enough because it becomes stale. Correctness must be enforced at the write boundary under concurrency.

ACID Across Database Systems

DatabaseImportant implementation notes
PostgreSQLUses MVCC and WAL; statement errors normally abort the current transaction until rollback.
MySQLInnoDB provides transactional guarantees; behavior differs for nontransactional engines and implicit-commit DDL.
SQL ServerUses locking and row-versioning options; recovery relies on its transaction log and configuration.
OracleUses multiversion read consistency and redo/undo mechanisms; DDL commonly commits implicitly.
SQLiteProvides transactional behavior through journaling modes with a different concurrency model from client-server systems.

Do not compare products using labels alone. Storage engine, isolation mode, durability configuration, replication, and version determine actual behavior.

ACID vs BASE

BASE is a broad description often associated with highly available distributed systems and eventual consistency. It does not mean "no transactions," just as ACID does not require one global transaction for an entire application.

Modern systems commonly combine strong ACID transactions inside one service with asynchronous propagation between services. The decision is made per invariant and boundary.

ACID and the CAP Theorem

CAP concerns the behavior of a distributed system during a network partition: it cannot simultaneously guarantee both linearizable consistency and availability for every request while the partition persists. The consistency term in CAP is not the same as the consistency property in ACID.

ACID and CAP answer different questions. A distributed database can provide ACID transactions while making a documented availability or consistency tradeoff during partitions.

Distributed Transactions

A local ACID transaction cannot atomically commit an unrelated database and message broker by itself. A transaction coordinator can use two-phase commit when all participants support it: participants prepare first, then commit only if every participant is ready.

This offers strong coordination but introduces blocking, coordinator recovery, latency, and operational complexity. It should be chosen from explicit consistency requirements.

Saga and Outbox Patterns

A saga models a distributed workflow as local transactions with compensating actions. If shipping fails after payment, a refund is a new business event rather than a physical rollback of the original payment.

The transactional outbox writes business data and an event record in one local ACID transaction. A relay publishes committed events, and idempotent consumers handle possible duplicate delivery. This avoids a gap between database commit and message publication.

ACID and Performance

Transactions require logging, concurrency control, validation, and recovery metadata. Stronger isolation can increase blocking or aborts, while synchronous durability can add commit latency. These costs buy specific correctness guarantees.

Optimize transaction duration, indexing, lock order, batch size, query plans, and contention before weakening guarantees. Relax durability or isolation only when the business can accept the precise failure mode introduced.

Testing ACID Behavior

Integration tests should force failure after each statement and verify atomic rollback. Constraint tests should attempt invalid keys and values. Concurrency tests should reproduce lost-update, uniqueness, inventory, and write-skew scenarios under the chosen isolation level.

Recovery tests should commit known data, restart the database safely, and verify persistence. Backup restore and failover drills validate broader durability and availability assumptions. Mocks cannot prove these properties.

Monitoring Transaction Guarantees

Track commit latency, rollback ratio, transaction age, lock waits, deadlocks, serialization failures, retry exhaustion, log flush time, log growth, replica lag, and restore objectives. Correlate database activity with application request identifiers without logging sensitive payloads.

Metrics need business context. Some conflicts are expected under contention, but rising rates or long transactions can indicate a regression that threatens correctness or capacity.

Common ACID Mistakes

  • Assuming ACID automatically validates every business rule.
  • Using autocommit for a multi-statement invariant.
  • Choosing an isolation level without identifying anomalies.
  • Ignoring affected-row counts and optimistic conflicts.
  • Holding transactions open during user input or remote calls.
  • Retrying deadlocks without idempotency or a limit.
  • Equating durability with backup or replication.
  • Assuming all databases implement level names identically.
  • Expecting one local transaction to cover external services.
  • Weakening guarantees before measuring the actual bottleneck.

ACID Best Practices

  • Define each business invariant and transaction boundary explicitly.
  • Enforce critical local rules with database constraints.
  • Select isolation from the anomalies the workflow cannot tolerate.
  • Keep transactions short without splitting indivisible work.
  • Use atomic SQL predicates and verify affected rows.
  • Handle deadlocks and serialization failures with bounded retries.
  • Use idempotency keys to resolve duplicate or uncertain requests.
  • Use outbox or saga patterns for distributed workflows.
  • Test crash recovery, restores, and concurrent execution.
  • Document any relaxed durability or consistency decision.

Interview Questions and Answers

What does ACID stand for?

Atomicity, consistency, isolation, and durability.

Does consistency mean all replicas immediately match?

Not in the ACID definition. It means a transaction preserves implemented database invariants. Replica consistency is a separate distributed-systems concern.

Which property prevents partial transactions?

Atomicity ensures the complete unit commits or its uncommitted changes are abandoned.

Does Serializable mean transactions literally run one at a time?

No. They may run concurrently, but the committed outcome must be equivalent to an allowed serial ordering.

Does durability replace backups?

No. Durability protects acknowledged commits under documented failures; backups address broader recovery events and human mistakes.

Practical Exercises

  1. Build a two-statement transfer and force the second update to fail.
  2. Add check, foreign-key, unique, and not-null constraints to a sample schema.
  3. Use two sessions to reproduce a non-repeatable read at Read Committed.
  4. Implement an optimistic version column to prevent lost updates.
  5. Model a write-skew rule and test it under snapshot and serializable isolation.
  6. Inspect your database's transaction or write-ahead log documentation.
  7. Measure commit latency with asynchronous and documented synchronous durability settings in a safe environment.
  8. Restore a test backup and verify its recovery point.
  9. Design an outbox table for order-created events.
  10. Document the ACID boundary of one real service workflow.

Quick Reference

ConceptMeaning
AtomicityAll required work succeeds or none is committed
ConsistencyCommitted state obeys implemented invariants
IsolationConcurrent interaction follows defined guarantees
DurabilityAcknowledged commits survive covered failures
WALRecovery log is persisted before related data pages
SerializableOutcome is equivalent to a valid serial order
OutboxBusiness data and event record commit locally together
CompensationNew action offsets an earlier distributed effect

Complete ACID Workflow

  1. Define the invariant and every participating local row.
  2. Separate external effects from the local transaction.
  3. Select constraints and isolation from concrete failure scenarios.
  4. Begin one short transaction on a managed connection.
  5. Read or lock only the data required for validation.
  6. Execute writes with safe predicates and verify row counts.
  7. Write any outbox event before the same local commit.
  8. Commit after every mandatory rule passes.
  9. Roll back and classify failures when they do not.
  10. Retry only transient conflicts with idempotency and backoff.
  11. Resolve ambiguous outcomes by querying durable request state.
  12. Monitor, recover, restore, and test the complete lifecycle.

Key Takeaway

ACID is the foundation for dependable SQL transactions, but it is not a substitute for schema design or business reasoning. Atomicity defines the unit, consistency protects implemented invariants, isolation controls concurrency, and durability preserves accepted work under documented failures.

Production reliability comes from applying those properties deliberately: define narrow transaction boundaries, enforce constraints, select isolation from real anomalies, handle retries and unknown outcomes, and use durable coordination patterns outside the local database. When the guarantees and application responsibilities align, concurrent failures become controlled outcomes rather than corrupted data.