ACID Transactions

A transaction is a logical unit of work that reads or changes data and either commits or aborts. ACID describes four properties of that unit: atomicity, consistency, isolation, and durability. These properties are useful whenever partially completed or conflicting changes would produce incorrect results, such as charging an account, reserving inventory, or publishing an ingestion batch.

Atomicity means that a transaction's changes take effect together or not at all. For an account transfer, the debit and credit must commit together; a failure between them must not leave only the debit applied. Consistency means that a successful transaction preserves defined data invariants, such as unique order identifiers, valid foreign keys, or a balance rule. The database enforces declared constraints, but application logic must still express business rules correctly. ACID consistency does not mean that every replica immediately returns the latest value.

Isolation controls how concurrent transactions interact. Serializable isolation aims to produce results equivalent to some serial execution, while weaker isolation levels allow more concurrency but can permit anomalies. Multiversion concurrency control lets readers access appropriate versions without blocking every writer, although conflicting writes can still wait or fail. Durability means that acknowledged commits survive failures within the system's documented guarantees, typically through transaction logs and persistent or replicated storage. Backups remain necessary for accidental deletion, corruption, and disaster recovery.

For a relational application, begin a transaction, perform the related statements, and commit only when the entire operation succeeds. Keep transactions short and avoid waiting for external API calls while holding locks. MySQL with InnoDB and PostgreSQL both support ACID transactions; ACID support alone is not a reason to select one over the other. Their defaults differ: InnoDB normally uses repeatable read, while PostgreSQL normally uses read committed. Evaluate isolation behavior, indexing, query plans, extensions, connection limits, and workload performance rather than assuming identical behavior from the same SQL syntax.

Stateful and stateless processing describe whether work retains information across operations, not whether it supports ACID. A stateful ingestion processor might maintain running totals, deduplication records, or window state. A stateless processor transforms each record independently. A stateless application worker can still execute a database transaction, and a stateful streaming job is not automatically transactional. Checkpoint recovery, source offsets, sink commits, and idempotent writes must be coordinated to prevent duplicate or missing results. ACID at the destination alone does not establish end-to-end exactly-once processing.

In a data lake, Apache Iceberg supplies transactional table semantics through snapshots, metadata, and a compatible catalog commit mechanism. Writers prepare data files and metadata, then publish a new table state through an atomic metadata update. Readers use a committed snapshot rather than seeing a partially published collection of files. Concurrent writers use optimistic concurrency control and validation; conflicts may require retries or recomputation. These guarantees generally apply to an individual table commit, not automatically to a business operation spanning multiple tables. Object storage consistency by itself does not make a directory of Parquet files an ACID table.

Transaction boundaries are an architectural decision. A database commit does not normally include a message sent to an external queue or a payment-provider request. A transactional outbox can record both business changes and an event in one database transaction, followed by asynchronous publication with deduplication. Across independent services, coordinated transactions or compensating workflows require additional design. Stronger isolation, distributed coordination, logging, and replication can increase latency, contention, and cost. Use the smallest transaction boundary that protects the required invariants, and implement bounded retries and idempotency for deadlocks, serialization failures, and uncertain commit outcomes.