ACID transactions

What are ACID transactions?

A transaction is a group of changes that succeeds or fails as one unit. ACID is the set of four guarantees behind that behaviour: atomicity, consistency, isolation, and durability. Together they keep data correct when a process crashes, a network call fails, or several jobs write at the same time.

The usual example is a bank transfer. Taking money from account A and adding it to account B are two changes, but they only make sense as one transaction. If the first change succeeds and the server crashes before the second, the money cannot simply disappear. ACID means both changes are committed, or neither change is.

For data teams, the same idea appears in less dramatic places: loading yesterday's sales, merging new customer records, updating stock levels, or replacing a fact table. ACID is the difference between a table that is either old or new, and a table that is halfway between both.

The four ACID guarantees

Atomicity
A transaction is all-or-nothing. If step three of a five-step write fails, the database rolls back the earlier steps instead of leaving partial work behind.

Consistency
A transaction takes the database from one valid state to another valid state. Rules such as foreign keys, unique constraints, required fields, and balance checks still hold after the transaction completes.

Isolation
Concurrent transactions do not see each other's unfinished work. One process should not read a half-updated table, and two writers should not silently overwrite each other's changes.

Durability
Once the system says a transaction is committed, the result should survive a crash. The database records enough information on durable storage to recover the committed change.

What can go wrong without ACID?

Imagine a nightly pipeline that refreshes a sales table. It deletes old rows, writes fresh rows from the webshop and point-of-sale system, and then updates a dashboard. Halfway through, the connection drops.

Without transactional guarantees, the table may contain a mix of old and new data. The morning revenue dashboard still opens, but the numbers are wrong in a way that is hard to spot. With ACID, the load either finishes and becomes visible as a complete new version, or it rolls back and the previous version remains available.

Concurrent writers cause a second class of problems. One job corrects customer addresses while another job deduplicates customer records. If both jobs read the same starting table and then write back their own version, the slower job can erase the faster job's work. Isolation and conflict detection are the guardrails that stop this from becoming a silent data loss problem.

ACID in databases and lakehouses

Classic relational databases such as PostgreSQL, SQL Server, MySQL, and Oracle were built around transactions. They manage their own storage, keep transaction logs, enforce constraints, and use locks or multi-version concurrency control to decide what each session can see.

A plain data lake works differently. Data is stored as files, often Parquet, on object storage such as Amazon S3, Azure Blob Storage, or Google Cloud Storage. Object storage is good at storing files, but it does not know that a folder of files is meant to behave like one table. If a job writes only half the files for a table, a plain reader may still pick those files up.

Lakehouse table formats add a transaction layer on top of those files. Delta Lake, Apache Iceberg, and Apache Hudi keep metadata about which files belong to which version of a table. Readers use that metadata to see a consistent snapshot. Writers commit through the table format, so a failed or conflicting write does not quietly become the current table.

The guarantee feels similar to a database, but the grain is often larger. In an operational database, a transaction might update a few rows in milliseconds. In a lakehouse, a transaction may publish a batch load, a merge, or a compaction job that touches many files. That is why ACID in a lakehouse is excellent for analytics correctness, while operational apps still usually keep their core transactions in an OLTP database.

ACID transactions versus eventual consistency

ACID is a strict correctness model. The system waits until it can make a clear promise: this change is committed, visible in the right way, and recoverable after failure. That promise is worth the overhead when the data is financial, operational, regulated, or used for management decisions.

Eventual consistency makes a different trade-off. A distributed system may accept that different readers temporarily see different states, as long as they converge later. That can be fine for likes, cached search results, telemetry, or product recommendations. It is much harder to accept for invoices, stock levels, payroll, or a management dashboard that will be exported to the board pack.

The practical question is not whether ACID is always better. The question is where a temporary inconsistency would hurt. If a wrong intermediate state can trigger a bad decision, a broken reconciliation, or a compliance issue, you want transactional guarantees.

What to watch out for with ACID transactions

• Long-running transactions
  A transaction that stays open for minutes can hold locks, delay cleanup, or increase conflict risk. Keep the transaction boundary around the work that must be committed together.

• Isolation level choices
  Databases usually offer several isolation levels. Lower isolation can improve throughput, but it may allow behaviours such as non-repeatable reads or write conflicts. Pick the level for the business risk, not only for speed.

• Storage and configuration
  Durability depends on logs, flush settings, storage behaviour, backups, and recovery setup. A product can support ACID while still letting you weaken guarantees through configuration.

• Lakehouse scope
  Table formats usually give guarantees at the table or platform-specific transaction scope. Do not assume that every multi-table workflow is atomic unless the engine documents that behaviour.