ACID Properties in SQL and DBMS
Introduction
ACID is a set of four properties that describe reliable database transaction behavior: Atomicity, Consistency, Isolation, and Durability. Together they help a DBMS process changes as complete units, preserve defined rules, coordinate concurrent work, and retain committed results after failures.
A bank transfer is the classic example. Debiting account A and crediting account B belong to one logical operation. The transfer must not lose money after only the debit succeeds. Other transactions must not corrupt the balances while the transfer runs. Once the transfer commits, a server restart should not erase it.
ACID is not one switch that makes all application behavior correct. Database constraints define many valid states, transaction code defines the complete unit of work, isolation settings and concurrency techniques control interference, and recovery mechanisms protect committed changes. Understanding how these pieces cooperate is more useful than memorizing four definitions.
What Is a Transaction?
A transaction is one or more database operations treated as one logical unit of work. It begins, performs reads and writes, and ends with either COMMIT or ROLLBACK. The boundary should match one business invariant, such as transferring money, placing an order, reserving inventory, or creating a customer with required related rows.
BEGIN;
UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 2;
COMMIT;
If any required step fails, the application rolls the transaction back. Exact transaction syntax, implicit behavior, and error handling vary by DBMS and client library.
ACID Overview
| Property | Core meaning | Core question |
|---|---|---|
| Atomicity | All required work commits or none does | Did the complete unit happen? |
| Consistency | Committed state satisfies defined rules | Is the resulting state valid? |
| Isolation | Concurrent transactions interact safely | Did concurrency change the outcome incorrectly? |
| Durability | Committed results survive covered failures | Will the accepted result persist? |
A = All or nothing
C = Valid state to valid state
I = Safe concurrent execution
D = Committed data persists
Atomicity
Atomicity means that a transaction is treated as an indivisible unit from the perspective of commit. Either every required database change succeeds and is committed, or the transaction's changes are rolled back. A partially completed business operation should not remain visible as the final result.
Atomicity can cover many SQL statements; it does not mean one statement or instant execution. An order placement may create the order, insert line items, reserve inventory, and record a payment reference. Those statements can form one transaction when they belong to one local atomic unit.
Atomicity in a Bank Transfer
Suppose account A has 1,000 and account B has 500. A transfer of 100 should finish with 900 and 600. If the debit succeeds but the credit fails, atomicity restores the original state rather than leaving 100 missing.
BEGIN;
UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 1
AND balance >= 100.00;
-- Application verifies one source row changed.
UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 2;
-- Application verifies one destination row changed.
COMMIT;
If either verification fails, ROLLBACK ends the transaction. The conditional debit also helps protect the no-overdraft invariant.
COMMIT and ROLLBACK
COMMIT accepts the transaction's changes and makes them permanent according to the database's durability guarantee. ROLLBACK abandons the current uncommitted changes.
ROLLBACK does not undo every historical change made by a user, and COMMIT cannot normally be reversed by issuing a later rollback. Correcting committed data requires a new compensating transaction, restore procedure, or domain-specific reversal.
External side effects such as an email already sent or a remote payment request are not automatically rolled back by a local database transaction. Coordinating database changes with outside systems requires outbox, idempotency, saga, or other distributed-workflow patterns.
Atomicity in E-Commerce
Order placement may create an order, add items, reserve inventory, and write a payment attempt. If these local changes represent one accepted order, a partial commit can create an empty order, unreserved sale, or unexplained inventory decrease.
The exact boundary depends on architecture. A remote payment provider cannot usually participate in the same local transaction. The system may commit a pending order and durable payment request, then complete payment asynchronously. Atomicity applies inside each local transaction, while workflow state and compensation manage the broader process.
Consistency
ACID consistency means a successful transaction moves the database from one state satisfying defined rules to another state satisfying them. Primary Keys remain unique, Foreign Keys remain valid, CHECK constraints remain true or otherwise satisfied under SQL semantics, and business invariants implemented by transaction logic continue to hold.
The database only enforces rules it knows. ACID does not make incorrect business code correct. A transaction can atomically commit a wrong fee calculation if no constraint or logic detects it. Consistency is a shared responsibility between schema design, database mechanisms, and application behavior.
Constraints Support Consistency
CREATE TABLE accounts (
account_id BIGINT PRIMARY KEY,
account_number VARCHAR(30) NOT NULL UNIQUE,
balance DECIMAL(14, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
CHECK (balance >= 0),
CHECK (status IN ('OPEN', 'FROZEN', 'CLOSED'))
);
The Primary Key protects identity, Unique protects the business account number, NOT NULL requires essential values, and CHECK restricts balance and status. Foreign Keys can protect related ledger entries. These constraints reject transactions that would leave known structural rules violated.
Consistency Is About Defined Rules
Consistency is not synonymous with accuracy. Several rows can consistently contain the same incorrect address. Nor does it mean there are no NULL values; NULL can be valid for an optional field. The property depends on explicit invariants.
For a bank transfer, one invariant may be that the combined balance of the two accounts remains unchanged, excluding fees. For ticket booking, one seat cannot be assigned to two active bookings. For inventory, available quantity cannot become negative. Each invariant needs an enforcement strategy.
Isolation
Isolation controls how concurrent transactions observe and affect one another. The goal is not necessarily to run transactions one at a time. It is to produce outcomes permitted by the configured isolation semantics while preventing unacceptable interference.
Without adequate isolation or conflict handling, two users may book the same seat, two updates may overwrite each other, or a report may read a mixture of old and new values. Locks, multiversion concurrency control, atomic statements, version checks, and serialization failures are among the mechanisms databases use.
Serial Execution as a Mental Model
If transactions ran completely one after another, reasoning would be simple but throughput could be poor. Strong isolation aims to make concurrent execution equivalent to some valid serial order for the protected operations, depending on the isolation level and DBMS implementation.
Isolation does not imply that nobody else can read or write during a transaction. Databases allow concurrency where operations are compatible and coordinate only where needed.
Dirty Reads
A dirty read occurs when one transaction reads another transaction's uncommitted change. If the writer rolls back, the reader acted on a value that never became committed truth.
Transaction A: UPDATE accounts SET balance = 0;
Transaction B: reads balance = 0 before A commits;
Transaction A: ROLLBACK;
Transaction B may have made a decision from a temporary state. Isolation levels that prevent dirty reads expose only committed versions.
Non-Repeatable Reads
A non-repeatable read occurs when a transaction reads one row twice and receives different committed values because another transaction updated or deleted it between reads.
This may be acceptable for a short independent query but unsafe when the first value supports a later decision. Repeatable-read or stronger semantics, explicit locking, or optimistic validation may be needed.
Phantom Reads
A phantom occurs when a transaction repeats a range query and sees a changed set of matching rows because another transaction inserted, deleted, or changed rows in the range.
Range-based invariants such as room availability, capacity limits, and "no active record exists" checks are vulnerable. Serializable behavior or product-specific locking and constraint techniques may be necessary.
Lost Updates
A lost update occurs when two transactions read the same value, calculate changes independently, and the later write overwrites the earlier committed result. One operation disappears from the final state.
Atomic updates can avoid simple read-modify-write races.
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 1001
AND available_quantity > 0;
Optimistic version checks or locking handle more complex edits.
Isolation Levels
| Common level | General intent | Important note |
|---|---|---|
| READ UNCOMMITTED | Allows the weakest read isolation | May permit dirty reads where supported |
| READ COMMITTED | Reads committed data | Repeated reads may change |
| REPEATABLE READ | Stabilizes previously read rows | Exact phantom behavior varies |
| SERIALIZABLE | Targets serializable outcomes | May block or abort conflicting work |
These names describe standards-oriented goals, but MVCC, snapshots, locking, and anomaly prevention differ by product. Always consult and test the actual DBMS. Choosing the highest level for every operation can reduce throughput or increase retries; choosing too low can violate invariants.
Locks
Locks coordinate access to rows, keys, ranges, pages, or tables. Shared and exclusive modes, intention locks, range locks, escalation, and duration vary by DBMS. Locks can prevent conflicting changes but also cause waiting and deadlocks.
Keep transactions short, access resources in a consistent order, use selective queries and suitable indexes, and avoid waiting for user input while locks are held. Explicit locking should be used only with a clear concurrency requirement.
Multiversion Concurrency Control
MVCC lets readers access an appropriate row version while writers create new versions, reducing some read-write blocking. Snapshot-style reads can provide a consistent view for a transaction or statement, depending on configuration.
MVCC does not eliminate conflicts. Concurrent writers can still contend, and snapshot isolation can permit anomalies such as write skew for certain multi-row invariants. Version cleanup and long-running transactions also have operational costs.
Optimistic and Pessimistic Concurrency
Optimistic concurrency assumes conflicts are uncommon. A version column ensures the row still matches the version the client read.
UPDATE products
SET price = 29.99,
row_version = row_version + 1
WHERE product_id = 1001
AND row_version = 7;
If no row changes, the client detects a conflict. Pessimistic concurrency locks data before making a decision, which can protect high-conflict operations but increases blocking and deadlock risk.
Durability
Durability means that once the database reports a successful commit, the committed result survives failures covered by the system's guarantee, such as process or server restart. The DBMS can reconstruct committed state through persistent logs and recovery mechanisms.
Durability is not the same as backup. A durable accidental deletion remains durably deleted. Backups, replication, point-in-time recovery, and disaster-recovery plans protect against additional failure classes and operator mistakes.
Write-Ahead Logging and Recovery
Many databases use write-ahead logging: recovery information reaches durable log storage before related data pages are considered safely committed. Data pages can be written later. After a crash, recovery replays committed changes and removes or ignores incomplete work as required by the engine.
Checkpoints bound recovery work by recording progress. Redo and undo information, log buffers, flush policy, storage guarantees, and replication settings differ among systems. Performance-oriented durability settings can weaken guarantees, so production configuration belongs in the ACID discussion.
Atomicity Versus Durability
Atomicity decides whether the complete transaction is accepted. Durability decides whether an accepted transaction persists. Before COMMIT, rollback can remove the transaction's database changes. After successful COMMIT, recovery should retain them.
A transaction that fails halfway relies on atomicity. A transaction that commits and is followed by a crash relies on durability. Logging and recovery often support both, but the properties answer different questions.
Consistency Versus Isolation
Consistency describes valid states and invariants. Isolation controls concurrent interaction so transactions do not create outcomes that violate those invariants. A valid constraint may reject an invalid final state, while stronger isolation can prevent two individually reasonable operations from jointly breaking a rule that spans rows.
Isolation alone cannot define the business rule, and consistency constraints alone may not protect every read-dependent workflow. The design needs both the invariant and the concurrency mechanism.
Autocommit
Many clients use autocommit, where each statement is committed as its own transaction unless an explicit transaction is opened. This is convenient for independent statements but unsafe for a multi-step operation that must be all-or-nothing.
A debit and credit issued as two autocommitted statements have no shared atomic boundary. Use explicit transaction handling and verify connection and framework configuration. Some DDL statements also have product-specific implicit commit behavior.
SAVEPOINT
A savepoint marks a location inside a transaction to which part of the work can be rolled back while the transaction remains active.
BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;
INSERT INTO order_items (...) VALUES (...);
ROLLBACK TO SAVEPOINT after_order;
COMMIT;
Savepoints are useful for optional substeps or batch error recovery, but they do not define the business transaction automatically. Committing an order after rolling back all required items may still be logically wrong.
Deadlocks
A deadlock occurs when transactions wait in a cycle for resources held by one another. The database detects the cycle and aborts one participant so the others can continue. The aborted transaction must usually be retried or reported safely.
Deadlocks are a consequence of concurrency control, not proof that ACID failed. Reduce them by keeping transactions short, accessing rows in a consistent order, indexing predicates, avoiding unnecessary scope, and implementing bounded retries for safe operations.
ACID Does Not Mean No Failures
Transactions can still fail because of validation, constraint violations, deadlocks, timeouts, network loss, resource exhaustion, or application errors. ACID defines how the database protects state around failures; it does not prevent failures from occurring.
Applications need clear error handling, rollback, connection cleanup, retry classification, and user-visible outcomes. After a network interruption during COMMIT, the client may not know whether the transaction committed, so idempotency or status lookup may be necessary.
Designing the Transaction Boundary
The transaction boundary should include every local database change required to preserve one invariant, but it should exclude unrelated work. If order creation and inventory reservation must either both happen or neither happen, they belong together when the same database owns both records. Updating a marketing preference in the same transaction may add contention without protecting the order invariant.
Boundaries that are too small allow partial business operations. Boundaries that are too large hold locks or versions longer, increase rollback cost, and combine failures that could have remained independent. A useful design exercise lists the state that must be visible after success, the state that must remain after failure, and the resources controlled by one transactional manager.
Code structure should make the boundary obvious. Begin immediately before the dependent database work, verify every affected-row expectation, and commit as soon as the invariant is established. Do not hide transaction ownership across unrelated helper methods where an inner function might commit work that an outer operation expects to roll back.
Failure Timing and Transaction Outcomes
Failure can occur before the transaction begins, during a statement, after statements finish but before COMMIT, while COMMIT is being processed, or after the server commits but before the client receives confirmation. Each timing produces a different recovery question.
Before commit, the application can normally roll back or let the database clean up the disconnected transaction. After a confirmed commit, the operation is complete. The difficult case is an interrupted connection during commit: the client has an unknown outcome. Blindly repeating a transfer or order can duplicate the business effect.
Use a stable operation identifier protected by a Unique constraint. On retry, query by that identifier and return the existing result when the first attempt committed. If it did not commit, the retry can safely create it. This approach connects ACID transaction guarantees with network-level idempotency.
External Side Effects and the Outbox Pattern
A local database transaction cannot ordinarily roll back an email, message already published to an independent broker, or charge accepted by a remote payment provider. Sending the external effect before commit risks publishing work whose database transaction later rolls back. Sending it after commit risks losing the effect if the process crashes between commit and send.
The transactional outbox pattern stores the business change and an outgoing-message record in the same database transaction. A separate publisher reads committed outbox rows and sends them with retry and idempotency controls. Atomicity guarantees that the business record and intent to publish appear together, while the publisher handles delivery beyond the database boundary.
The pattern does not create one global transaction. Consumers must tolerate duplicate delivery, messages need stable identities, and operations need monitoring for stuck outbox records. It converts an unsafe timing gap into a durable, recoverable workflow.
Observability for Transaction Reliability
Production systems should record transaction duration, rollback reason, deadlock and serialization counts, retry attempts, connection failures, log pressure, and commit latency. Correlation and operation identifiers connect database activity to API requests, background jobs, and messages without logging sensitive values unnecessarily.
Long-transaction alerts reveal forgotten boundaries and user interactions occurring while locks are held. Rising retry rates may indicate a hot row, missing index, inconsistent lock order, or isolation level unsuited to the workload. Recovery testing should produce evidence that committed data survives and incomplete work does not become visible.
Observability does not replace correctness, but it proves whether the designed ACID behavior remains healthy under real concurrency and failure. A transaction model that cannot be monitored is difficult to operate safely.
Durability Boundaries and Failure Domains
Durability is defined relative to configured failure domains. Flushing a transaction log to local persistent storage may protect against process and operating-system crashes, but not loss of the entire host or storage device. Synchronous replication can extend protection to another node, while cross-region recovery introduces additional latency, cost, and operational complexity.
Acknowledgment settings matter. Some systems can report success before log records are fully persisted, trading stronger throughput for a window of data loss. Replicas may also acknowledge at different stages. Teams should know exactly what a successful commit means in their configuration rather than relying only on the product's default label.
Backups and tested restores remain necessary. Durability keeps committed changes; it also preserves accidental updates, malicious changes, and logical corruption. Recovery objectives, immutable backup copies, point-in-time restoration, replication, and failover tests protect against failure classes beyond one transaction log.
Transaction Retry and Idempotency
Serialization failures and deadlock victims are often transient and can be retried. Retries should be bounded, delayed appropriately, and limited to errors known to be safe. The whole transaction must be replayed because earlier reads may no longer be valid.
Idempotency ensures repeating a request does not create duplicate business effects. A unique request key can let an order or payment API return the original result after a timeout instead of creating a second operation. Database atomicity does not by itself make network retries idempotent.
Short Transaction Principle
A transaction should normally contain only the database work required for one consistency boundary and should commit or roll back promptly. Long transactions retain locks or old versions, increase contention and log usage, delay cleanup, and make rollback more expensive.
Avoid holding a transaction open while waiting for user input, calling a slow external service, or performing unrelated processing. When external coordination is required, use durable workflow states and messages rather than one local transaction stretched across unreliable boundaries.
ACID and Stored Procedures, ORMs, and Pools
Stored procedures can centralize multi-statement transactions near the data. ORMs provide transaction APIs and unit-of-work patterns, but developers must still define the correct boundary and understand generated SQL, flush timing, and optimistic locking.
Connection pools require careful cleanup. A connection returned with an open transaction or modified isolation setting can affect the next borrower. Use structured transaction scopes, always roll back after failures, and let the pool reset connections according to documented behavior.
ACID and Distributed Systems
A local database transaction provides ACID guarantees within its controlled resource boundary. A workflow spanning several service-owned databases, message brokers, and external providers does not automatically become one global ACID transaction.
Distributed transaction protocols exist but add coordination, availability, and operational tradeoffs. Microservices often use local transactions plus durable events, outbox patterns, idempotent consumers, and sagas. Each local step is ACID; the wider workflow reaches a business outcome through retries and compensation.
Saga Concept
A saga divides a distributed business process into local transactions. If a later step fails, compensating actions attempt to reverse or offset earlier effects. Canceling a reservation and issuing a refund are domain operations, not database rollbacks of already committed remote work.
Sagas require explicit states, idempotency, timeouts, retry policy, audit history, and handling for compensation failure. They trade global atomicity for availability and service autonomy while preserving business consistency through workflow design.
ACID Versus BASE
BASE is commonly summarized as Basically Available, Soft state, and Eventual consistency. It describes a family of distributed design tradeoffs rather than a direct replacement for all ACID behavior.
A system can use ACID transactions inside each service and eventual consistency across services. The choice is made per invariant and boundary. Financial ledger posting may require strong local transactions, while a search projection can update eventually.
Performance Tradeoffs
Reliability has costs: log flushes add commit latency, indexes and constraints add write work, isolation can block or abort transactions, and long-lived versions consume storage. Weakening guarantees can improve selected metrics but transfers complexity to applications and operations.
Optimize with evidence. Batch appropriate writes, keep transactions short, index selective predicates, tune connection and log settings, and choose isolation per workflow. Do not disable durability or integrity mechanisms without understanding the failure state being accepted.
Complete Bank Transfer Example
BEGIN;
UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 1
AND status = 'OPEN'
AND balance >= 100.00;
-- Require exactly one affected row.
UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 2
AND status = 'OPEN';
-- Require exactly one affected row.
INSERT INTO transfer_ledger (
transfer_id, from_account_id, to_account_id, amount
) VALUES (
:transfer_id, 1, 2, 100.00
);
COMMIT;
Atomicity keeps debit, credit, and ledger entry together. Consistency constraints preserve valid accounts, amount, and balance rules. Isolation prevents unsafe concurrent balance decisions. Durability preserves the committed transfer. A unique transfer ID also supports idempotent retry.
Ticket Booking Example
Two users may try to reserve the same seat simultaneously. A Unique constraint on the active event-seat identity, combined with a transaction, lets one reservation succeed and rejects the conflicting one. Isolation and constraint behavior coordinate the race.
Atomicity keeps booking, seat allocation, and local payment state together. Consistency protects valid event and customer references. Durability preserves the committed reservation. External payment may still require a saga or pending state.
ACID Testing Strategy
Atomicity tests force failure after an early write and verify every transaction change rolls back. Consistency tests attempt duplicate keys, invalid references, out-of-range values, and business-invariant violations. Isolation tests run controlled concurrent sessions and verify allowed outcomes under the chosen level.
Durability tests commit, restart or fail over the database in a supported test environment, and verify the result remains. Recovery tests should include incomplete transactions, log replay, backup restoration, and point-in-time procedures according to operational goals.
Also test deadlock retries, serialization failures, connection loss around COMMIT, idempotent request replay, and transaction cleanup in connection pools.
Common ACID Mistakes
Atomicity and consistency are not the same: all-or-nothing execution can still commit wrong logic. Isolation does not mean transactions literally run alone. Durability is not backup, and rollback cannot undo an old committed transaction.
Other mistakes include choosing SERIALIZABLE for everything without testing contention, assuming ACID removes the need for constraints or validation, relying on autocommit for multi-step work, holding transactions open during remote calls, and retrying non-idempotent operations blindly.
ACID, Constraints, and Transactions
| Concept | Role |
|---|---|
| Transaction | Defines one logical database unit of work |
| ACID | Describes reliability properties of transaction processing |
| Constraints | Define many allowed-state rules |
| Locks and MVCC | Help implement isolation and concurrency control |
| Logs and recovery | Help implement atomicity and durability |
| Application logic | Defines business invariants and transaction boundaries |
Interview Questions and Answers
What does ACID stand for? Atomicity, Consistency, Isolation, and Durability.
Which property means all or nothing? Atomicity.
Which property concerns concurrent transactions? Isolation.
Which property keeps committed data after a crash? Durability, within the configured system guarantee.
What is the difference between COMMIT and ROLLBACK? COMMIT accepts the current transaction; ROLLBACK abandons its uncommitted changes.
Does ACID guarantee correct business logic? No. The application and constraints must define the correct invariants.
What is a dirty read? Reading another transaction's uncommitted data.
Why can a deadlocked transaction be retried? The DBMS aborts one participant to break the cycle; the logical operation may succeed when replayed safely.
Practical Review Checklist
- Define the complete business unit before opening a transaction.
- Keep every required local database change inside that boundary.
- Use constraints to express identities, references, and valid domains.
- Select isolation according to concrete concurrency anomalies.
- Use atomic statements, version checks, or locks where needed.
- Keep transactions short and avoid remote calls while open.
- Handle rollback and connection cleanup on every failure path.
- Classify deadlock and serialization errors for bounded retry.
- Make network retries idempotent with durable request identity.
- Understand the database's log flush and durability settings.
- Use backup and disaster recovery in addition to durability.
- Coordinate distributed work with outbox, saga, or equivalent patterns.
- Test concurrency with the production DBMS, not only mocks.
- Verify atomicity, consistency, isolation, and durability separately.
Key Takeaway
ACID properties define the core reliability model of database transactions. Atomicity prevents partial local work, Consistency keeps committed states within defined rules, Isolation coordinates concurrent transactions, and Durability preserves acknowledged commits through covered failures.
ACID
= ALL OR NOTHING
+ VALID STATE TRANSITIONS
+ SAFE CONCURRENCY
+ PERSISTENT COMMIT
Reliable systems combine these properties with correct business logic, schema constraints, appropriate transaction boundaries, tested isolation, recovery configuration, idempotent retries, and distributed workflow patterns. ACID is fundamental, but it works as part of a complete database and application design.