Why architecture matters here
Why does a warehouse need transactions at all when 'just reload the partition' worked for a decade? Because the workloads changed shape. CDC ingestion streams row-level upserts continuously — reloading a terabyte partition to apply a thousand updates is economically absurd. Compliance deletion must remove specific rows across years of history on a deadline. Concurrent correctness: analysts query while ingestion writes; without snapshot isolation they see half-applied batches — the classic Monday-morning 'numbers changed while the dashboard refreshed' ticket. ACID's snapshot reads and atomic commits make these boring.
The architecture matters because the costs live in specific places, and operating the system means managing them. Write amplification is deferred, not avoided: updates are cheap at write time (append a delta) but the debt accrues to readers, who must merge ever more deltas, and is repaid by compaction, which rewrites data in bulk. A table receiving frequent small updates without a healthy compactor degrades monotonically — first slow queries, then task OOMs from delta-merge overhead, and the failure arrives days after its cause. The metastore becomes a transaction coordinator: open transactions, locks, and the compaction queue are RDBMS rows; an abandoned client that never heartbeats holds the oldest-open-transaction watermark, blocking cleaners from deleting obsolete files cluster-wide. Concurrency is real but coarse: writers to different partitions proceed in parallel; two MERGEs into the same partition serialize on locks (or conflict), so pipeline design — one writer per partition per window — remains the difference between smooth and stormy.
These are exactly the trade-offs Iceberg and Delta later re-balanced (optimistic concurrency instead of locks, manifest metadata instead of directory listing), which is why fluency in Hive ACID doubles as a map of the design space.
The architecture: every piece explained
Top row: the control plane. Client DML — INSERT, UPDATE, DELETE, MERGE — arrives at HiveServer2. The transaction manager (DbTxnManager, state in metastore tables) opens a transaction, and clients heartbeat it; missed heartbeats let the system abort abandoned work. Global txn IDs map to per-table write IDs — the monotonic sequence that names delta directories and defines visibility. The lock manager takes shared locks for reads, semi-shared for updates/deletes, and exclusive for schema-changing operations, mediating DML vs compaction vs DDL.
Middle row: the storage layout. A table (or partition) directory holds base_N files — a compacted snapshot containing all rows committed up to write ID N — plus delta_x_y directories (inserted/updated rows for write IDs x..y) and delete_delta_x_y directories (tombstones). Every row in an ACID ORC file carries the hidden triple (originalTransaction, bucket, rowId): an update is physically a delete-delta entry addressing the old row's triple plus an insert of the new version; MERGE compiles into exactly these primitives. The snapshot reader gives each query a valid-write-ID list at start (committed, not aborted, below the high-water mark); scans then merge base + insert deltas minus delete deltas — sorted merge on the row triple, with ORC's PPD still applying — so a query sees one consistent version regardless of commits landing mid-flight.
Bottom rows: the repayment plan. Minor compaction merges many small deltas into fewer larger ones — cheap, frequent, keeps file counts sane. Major compaction rewrites base + all deltas into a new base_M, discarding tombstoned rows entirely — expensive, scheduled by thresholds (delta count, delta-to-base ratio) or manually. An initiator thread in the metastore scans for tables crossing thresholds and enqueues work; worker processes (in HMS or, in modern Hive, query-based compaction running as jobs) execute it; a cleaner deletes obsolete files — but only past the oldest snapshot any reader or open transaction might still need, which is precisely why one zombie transaction stalls cleanup everywhere. The ops strip follows: watch the compaction queue, kill stale transactions, verify cleaner progress, and keep stats fresh so the planner understands post-merge cardinalities.
End-to-end flow
Follow a CDC pipeline hour on an orders table (partitioned by day, bucketed by order_id). At :05, the ingest job lands 40k upserts via MERGE: the txn manager opens txn 9107, allocates write ID 512 for the table, and takes semi-shared locks on today's partition. MERGE matches source rows to targets: 28k update matches become entries in delete_delta_512_512 (tombstoning old row triples) plus rows in delta_512_512; 12k non-matches become inserts in the same delta. Commit is atomic: txn 9107 flips to committed in the metastore; write ID 512 becomes visible to new snapshots. A dashboard query that started at :04 with a snapshot excluding 512 continues reading the old version, untroubled.
By :45, eight more MERGEs have stacked deltas 513–520. Reads slow measurably: every scan of today's partition now merges base_498 plus nine deltas and their delete-deltas. The initiator notices the table crossed the delta-count threshold and enqueues a minor compaction; a worker merges deltas 512–520 into delta_512_520 (and likewise the delete deltas), taking locks that coexist with readers and writers. File counts drop; the read path recovers. Saturday night, major compaction rewrites base_498 + all deltas into base_520: tombstoned rows vanish physically (this is where GDPR deletion becomes real), and the compactor recomputes stats. The cleaner waits until the last query whose snapshot referenced base_498 finishes, then removes the old files.
Two concurrency vignettes complete the picture. An analyst's long report opened at :40 holds its snapshot for an hour; cleanup of everything it references stalls — visible in metrics as cleaner lag attributed to that query. And a second writer targeting the same partition at :05 (a manual backfill) blocks on the semi-shared lock until the MERGE commits, then proceeds against the new snapshot — serialized, correct, and the reason the ingestion runbook says 'one writer per partition per window'.