ACID Properties as Design Constraints

Learning outcomes

ACID summarizes four essential transaction properties: Atomicity, Consistency, Isolation, and Durability. These are not marketing labels; each corresponds to specific failure modes that the data model and DBMS must address.

Atomicity

Atomicity means a transaction's database changes are all-or-nothing. If a failure occurs before commit, partial changes are rolled back.

BEGIN;UPDATE inventory ...;INSERT part_usage ...;COMMIT;

If the insert fails, the inventory decrement should not remain committed.

Atomicity is not distributed magic

A database transaction cannot automatically roll back an email already sent or an HTTP request already accepted by another service. Cross-system workflows require patterns such as outbox, idempotency, sagas, or compensating actions.

Consistency

Consistency means a valid transaction takes the database from one state satisfying its enforced invariants to another valid state.

Examples:

• foreign keys remain valid;
• quantities remain positive where required;
• unique keys remain unique;
• application-managed invariants remain true.

Consistency is shared responsibility

The DBMS can enforce declared constraints, but it does not know every business rule automatically. If the rule “a closed WorkOrder has no active Assignment” is not encoded anywhere, ACID does not invent it.

Isolation

Isolation controls how concurrent transactions observe and interfere with one another. Ideally, concurrent execution behaves like some serial ordering, but lower isolation levels permit certain anomalies for performance and concurrency.

Durability

Durability means that after the DBMS reports a successful commit, the transaction's effects survive expected failures according to the database's configured durability guarantees.

ACID through one WorkshopHub operation

For RecordPartUsage:

Failure before commit

If the process crashes after updating two rows but before commit, atomicity requires rollback during recovery.

Failure after commit acknowledgement

If the process crashes after commit completes, durability requires the committed state to survive within the DBMS's guarantee model.

Commit uncertainty

A client can lose its network connection just after sending COMMIT. It may not know whether commit succeeded. This is why application operations often need idempotency keys: retries must be safe even when the result of the first attempt is uncertain.

ACID does not mean “no concurrency”

Isolation permits concurrency while controlling anomalies. Database engines use locks, MVCC, validation, serialization checks, or combinations of these techniques.

Durability versus performance

Some systems allow configuration that trades strict durability for latency or throughput. Such choices are operational decisions with data-loss implications and should not be accidental.

Database versus application state

A transaction can atomically persist database rows, but in-memory caches or files may lag. Treat the database commit as one consistency boundary and design downstream projections carefully.

ACID and constraints

Strong declarative constraints reduce how much business consistency must be implemented manually:

PRIMARY KEYUNIQUEFOREIGN KEYCHECKNOT NULL

ACID and long workflows

Long business processes such as repair approval, shipping, or payment settlement are usually composed of many short ACID transactions connected by durable state transitions.

Practice: map the failure mode

Summary and next lesson

ACID connects failure handling to schema design. Atomicity protects units of work, consistency preserves declared invariants, isolation governs concurrent interaction, and durability protects committed state. The next lesson focuses on isolation in detail and shows how concurrency can break invariants even when every individual transaction looks correct.

References

• Jim Gray and Andreas Reuter, Transaction Processing: Concepts and Techniques.
• Martin Kleppmann, Designing Data-Intensive Applications.
• PostgreSQL documentation on transaction isolation.