AI Glossary / Data engineering and architecture / Intermediate

ACID transactions

Definition

ACID transactions are database transactions designed around four properties: atomicity, consistency, isolation and durability. Together, these properties help related data changes remain dependable when operations fail, overlap or are followed by a system restart. The guarantees apply within a defined transaction boundary and depend on the database's supported behavior and configuration.

What it means

A transaction groups operations into a unit of work. Atomicity means the unit's transactional changes take effect together or do not take effect. Consistency concerns preserving the database's defined integrity rules when moving between valid states. Isolation controls how concurrent transactions interact and what intermediate information they can observe. Durability concerns retaining committed changes after the failures covered by the system's guarantees.

These words do not mean that all stored information is factually correct. A transaction can record an incorrect address. Nor does consistency here automatically mean that every replica everywhere immediately shows the same value; distributed-system terminology uses that word in other ways.

Applications must define boundaries and rules. A database cannot enforce an unstated business requirement simply because it advertises ACID support.

How it works

An application begins a transaction, performs supported operations and either commits the work or rolls it back. The database coordinates visibility, conflict handling and recovery using mechanisms such as locks, multiple data versions and transaction logs.

Isolation has levels. A system's default may permit observations or anomalies that a stronger level prevents. Serializable execution aims to make committed concurrent transactions equivalent to some serial execution, but conflicts may require a transaction to abort and be retried.

The guarantee's scope matters. A transaction may cover one table, several tables or a supported distributed operation. An external email, payment request or file write is not automatically rolled back with database changes.

Durability also has an operational boundary. Storage failures, replication configuration and acknowledged-commit settings must be understood. Backups and recovery planning remain necessary because durable storage can faithfully retain an accidental but valid deletion.

Three practical examples

• A stock service records a reservation and its corresponding inventory adjustment in one supported transaction, so a failure cannot leave only one change committed.
• A course platform creates an enrollment and the associated internal entitlement together, subject to uniqueness and capacity rules.
• A data platform publishes a transactional table update so readers do not treat an unfinished collection of file changes as a completed table version.

Think of it this way

Think of submitting a completed application envelope whose pages are accepted as one packet. The packet should not be filed with half its pages missing; staff must also follow rules for handling simultaneous applications and preserving accepted records.

The analogy is limited. Database isolation has precise technical meanings, and the institution still needs rules about what makes an application valid. An accepted envelope does not prove the statements inside it are true, just as ACID does not validate every real-world fact.

Worked example

A reservation that must remain balanced

Consider an invented warehouse with 12 available units and no reservation for request R. A request for three units should create a reservation of three and reduce availability to nine. Assume both records are inside the same supported transaction and the program checks its inventory rule correctly.

If creating the reservation fails, atomicity prevents the inventory decrement from becoming a committed change by itself. If both operations succeed and the transaction commits, the intended state is availability nine and reservation three.

Now add another simultaneous request for ten units. Atomicity alone does not decide which request may succeed. The application needs suitable concurrency control and a stock rule that prevents both from consuming the original availability independently.

Finally, suppose the client loses its connection after the server commits. Retrying the request blindly could create another reservation. A stable request identifier and duplicate-handling design address this uncertainty; the existence of an ACID transaction does not make every client retry harmless.

Illustrative case study

A fictional subscription company updates a customer record and immediately sends a welcome message inside its application workflow. A later database operation fails and the transaction rolls back, but the already sent message cannot be recalled by that rollback.

The team changes the design to record a pending notification alongside the subscription update within the database transaction. A separate delivery process handles committed notification records and tracks retries. It defines how duplicate deliveries are detected instead of assuming that retrying guarantees exactly one message.

The case shows that transaction design includes boundaries between systems. The database transaction can protect its own records while additional coordination is needed for external effects.

Common misconceptions and mistakes

• ACID prevents incorrect business decisions. It protects defined transaction properties; applications must still implement correct rules and use accurate inputs.
• All ACID databases provide identical default isolation. Check the actual level and anomalies.
• A rollback reverses everything the application did. External side effects may lie outside the transaction and require separate handling.

Business use

ACID transactions matter wherever several records represent one business event. They help prevent incomplete updates from becoming the accepted state and provide a foundation for predictable recovery.

When assessing a database or data platform, ask which operations and tables share a transaction, what isolation is used and how conflicts are reported. Test realistic failures: a rejected constraint, concurrent demand, a lost client connection and a restart after commit.

For AI-driven workflows, keep the same discipline. A model deciding to reserve stock does not replace inventory constraints. Record stable operation identifiers, validate proposed changes and separate committed database outcomes from external tasks still awaiting delivery. This helps explain whether an action completed.

People also ask

Does ACID require a relational database?

No. Transactional guarantees can also be provided by other database and table-storage designs.

Does serializable mean operations physically run one at a time?

No. They may run concurrently while their committed result satisfies serial-equivalence requirements.

Does durability replace backups?

No. Backups address additional recovery needs, including recovering from unintended committed changes.

Check your understanding

A transaction commits a subscription record, but the client never receives confirmation. Is blindly repeating the request necessarily safe?

Answer: No. The first transaction may already have committed. The application needs a way to identify the original request and avoid treating its retry as a new business action.

Related terms

• Commit (data management)
• Concurrency control
• Transaction
• commit
• rollback
• isolation level
• serializability
• write-ahead log
• idempotency
• transactional outbox

Learn next

Read transaction boundaries and isolation levels. Draw one business operation across its database and external services, marking exactly which changes commit together and which effects need separate retry handling.

More in Data engineering and architecture

• Access control list - An access control list, or ACL, is a set of entries describing access permissions associated with a resource.
• Access Manager - In database architecture, an access manager is the part of a database management system that turns an execution plan into operations that access stored data.
• Access Method - In database systems, an access method is a set of software routines that provides operations for a particular table or index structure.
• Action schema - An action schema is a parameterized description of a type of action in an AI planning model.
• Additive Rule - The additive rule, also called the union rule, is an inference rule for functional dependencies in relational database theory.
• Address space - An address space is the set or range of addresses used to identify locations within a defined system.

Sources

PostgreSQL, Transactions: https://www.postgresql.org/docs/16/tutorial-transactions.html. Primary introduction to transaction grouping, visibility and completion.

PostgreSQL, Transaction Isolation: https://www.postgresql.org/docs/16/transaction-iso.html. Primary documentation of isolation levels and serialization retries.

Databricks, What are ACID guarantees on Databricks?: https://docs.databricks.com/gcp/en/lakehouse/acid. Primary discussion of product-specific guarantees. The warehouse and subscription cases are original illustrations.

Previous: ACE model Next: Acoustic model