# ACID Transactions

ACID transactions group data operations into a reliable unit of work with atomicity, consistency, isolation, and durability guarantees.

Cloud concept. Updated 2026-10-29. Page: https://blowstack.com/tools/cloud-concepts-hub/acid-transactions

> ACID properties help applications update shared data correctly despite concurrent requests, processing errors, and infrastructure failures. They apply to relational databases, some NoSQL operations, and transactional lakehouse tables, but their scope and isolation guarantees depend on the implementation.

Practice exams covering ACID Transactions: [DEA-C01 AWS Certified Data Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate), [DVA-C02 AWS Certified Developer - Associate](https://blowstack.com/practice-exams/aws-certified-developer-associate), [SAA-C03 AWS Certified Solutions Architect - Associate](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate), [SAP-C03 AWS Certified Solutions Architect - Professional](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional).

## 6 key aspects of ACID Transactions

ACID protects a defined unit of work, while isolation settings, transaction boundaries, and recovery design determine its practical guarantees.

1. **Atomicity**: All changes within the transaction boundary commit together or are rolled back. Place dependent operations, such as decrementing inventory and creating a reservation, inside that same boundary.
2. **Consistency**: Successful transactions preserve declared constraints and correctly implemented business invariants. This property does not guarantee application correctness or immediate freshness on every read replica.
3. **Isolation**: Isolation determines which effects concurrent transactions can observe and which anomalies are possible. Choose an isolation level deliberately and handle conflicts or serialization failures with safe retries.
4. **Durability**: Once a commit is acknowledged, its changes persist within the configured failure guarantees. Logging and replication support durability, while backups address additional recovery requirements.
5. **Explicit transaction scope**: Guarantees apply only to participating operations and resources. A single-table lakehouse commit or database transaction does not automatically include another table, an external API, or a message broker.
6. **Processing state and recovery**: Stateful processors retain information between events; stateless processors do not require that retained processing state. Either can interact with an ACID destination, but reliable ingestion also requires coordinated checkpoints, offsets, and duplicate handling.

## Cloud services for ACID Transactions

Amazon RDS and Amazon Aurora can protect operational changes with relational transactions, while Amazon DynamoDB offers transactional APIs for related item updates. For analytical ingestion, AWS Glue can write Iceberg tables and Amazon Athena can query or modify supported Iceberg tables through their transactional metadata model. These services do not share one automatic transaction boundary. Moving data between an operational database and a lakehouse requires additional coordination, such as change data capture, checkpointing, idempotent writes, and reconciliation. Select each service according to its transaction scope, concurrency behavior, and recovery requirements.

- **[Amazon RDS](https://blowstack.com/tools/cloud-services-hub/amazon-rds)**: Provides managed relational database engines that support transactions, including PostgreSQL and MySQL with InnoDB. Engine behavior, isolation levels, storage engines, and durability settings remain important when designing transaction-sensitive workloads.
- **[Amazon Aurora](https://blowstack.com/tools/cloud-services-hub/amazon-aurora)**: Provides MySQL-compatible and PostgreSQL-compatible relational databases with ACID transactions and distributed storage. Applications must still account for engine-specific isolation behavior and potentially stale reads from reader instances.
- **[Amazon DynamoDB](https://blowstack.com/tools/cloud-services-hub/amazon-dynamodb)**: Supports ACID transactions across multiple items and tables within one AWS account and Region through transactional APIs. These guarantees do not turn global-table replication into a cross-Region atomic transaction; transactional requests also consume additional capacity compared with ordinary item operations.
- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue Spark jobs can read and write Apache Iceberg tables using supported integrations and configuration. The AWS Glue Data Catalog can serve as the Iceberg catalog, while Iceberg's commit protocol provides table-level transactional publication.
- **[Amazon Athena](https://blowstack.com/tools/cloud-services-hub/amazon-athena)**: Supports transactional reads and supported data modifications on Apache Iceberg tables using the appropriate engine version and catalog configuration. Iceberg snapshots provide consistent table views, but separate statements should not be assumed to form a general-purpose multi-table transaction.

## ACID Transactions exam questions

Real practice exam questions on ACID Transactions, with the correct answer and its explanation.

### Question 1 (DEA-C01)

**Question:**

A company uses an AWS Glue Spark job to ingest each supplier delivery into an Amazon S3 data lake. The job validates and normalizes each record independently, without retaining information from previously processed records. A delivery contains files for several business dates.

Analysts query the destination while ingestion is running. Each query must see either none or all of a delivery's new records, never a partially published delivery. If the job fails before publishing the delivery, previously committed data must remain queryable and unchanged. The query engine supports Apache Iceberg tables.

Which design meets these requirements?

**Correct answer:** Use an Apache Iceberg destination table and append the entire delivery through one table commit. Keep the record-level transformations independent of prior records.

**Explanation:** An Iceberg table commit atomically publishes a new snapshot containing the delivery's changes. Snapshot-aware queries see the previously committed table or the newly committed table, not a partially published delivery. Files written before a failed commit are not part of the committed table snapshot. Stateless record transformations and transactional destination writes are compatible: they describe different aspects of the pipeline.

### Question 2 (SAP-C03)

**Question:**

A retailer stores its order history in Amazon S3 and uses Amazon Athena for interactive analytics. Every 15 minutes, a change-data-capture pipeline delivers a batch containing inserts, updates, and deletes, with at most one change per order ID. Analysts must continue querying during ingestion, and each query must see either the complete state before a batch or the complete state after it, never a partially applied batch. Audit reports record the table version they queried and must be reproducible for seven days. The company requires serverless SQL processing, no separate warehouse copy, and no full-table rewrite for each batch. Which solution meets these requirements?

**Correct answer:** Use an Apache Iceberg table with Athena engine version 3. Apply each batch with one MERGE INTO statement. Record the committed snapshot ID for each report, retain snapshots for at least eight days, and prevent S3 lifecycle rules from deleting files required by those snapshots.

**Explanation:** Athena supports transactional MERGE INTO operations on Iceberg tables. A single statement commits the batch atomically, while concurrent queries read consistent snapshots. Athena version-travel queries can reproduce reports using recorded snapshot IDs, provided snapshot metadata and referenced files remain available. An eight-day retention window provides a buffer beyond the seven-day requirement.

### Question 3 (SAA-C03)

**Question:**

A logistics company is migrating a MySQL application to a managed AWS database. The application depends on MySQL-specific stored procedures that cannot be rewritten before the migration deadline. Each shipment confirmation updates a shipment table and inserts a billing record within an explicit transaction. Both changes must commit together or be rolled back, and acknowledged commits must survive a database restart. The workload requires low-latency, concurrent writes. Which database and storage-engine selection meets these requirements without application changes?

**Correct answer:** Use Amazon RDS for MySQL with InnoDB tables for the shipment and billing data.

**Explanation:** RDS for MySQL preserves the required engine compatibility. InnoDB supports ACID transactions, allowing both changes to commit together or roll back together, and provides durable storage for committed changes. Its row-level locking supports concurrent transactional writes.

## Practice ACID Transactions in a full exam

Questions on ACID Transactions in each practice exam, with every answer option and an explanation for each:

- [DEA-C01](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate): 5 questions
- [SAA-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate): 2 questions
- [SAP-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional): 1 question
- [DVA-C02](https://blowstack.com/practice-exams/aws-certified-developer-associate): 1 question

## What is 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.

## Learn ACID Transactions in lessons

5 short lessons on ACID Transactions in 3 certification courses.

- DEA-C01: ACID Transactions: transaction boundaries in ingestion (Premium), https://app.blowstack.com/lessons/acid-transactions-transaction-boundaries-in-ingestion-dea-c01
- SAP-C03: ACID Transactions: Atomic Lakehouse Table Commits (Premium), https://app.blowstack.com/lessons/acid-transactions-atomic-lakehouse-table-commits-sap-c03
- SAA-C03: ACID Transactions: database transaction guarantees (Premium), https://app.blowstack.com/lessons/acid-transactions-database-transaction-guarantees-saa-c03
- DEA-C01: ACID Transactions: atomic table commits in Apache Iceberg (Premium), https://app.blowstack.com/lessons/acid-transactions-atomic-table-commits-in-apache-iceberg-dea-c01
- DEA-C01: ACID Transactions vs Idempotency (Premium), https://app.blowstack.com/lessons/acid-transactions-vs-idempotency-dea-c01
