ACID Transactions on Azure Databricks: What Breaks Without Retry Logic

A warehouse has always guaranteed that updating two tables together either both succeed or both roll back. A lakehouse, historically, has not. Multi-table, multi-statement transactions reaching general availability on Unity Catalog close that gap directly on Delta Lake and Apache Iceberg tables, without a separate transactional system sitting in front of the lake.

Transactions on Azure Databricks reached general availability on October 16, 2026. I covered the headline already in a shorter piece. This is the expanded version: both transaction modes in full, the four documented conflict scenarios, a staging-and-validation ETL pattern end to end, JDBC and Python client code with retry logic added on top of what the documentation shows, and the specific limitations worth knowing before a production pipeline leans on this.

[INSERT IMAGE HERE using the editor's own Insert Image button, not pasted markup. Upload a diagram showing two tables being updated inside one atomic boundary, with a commit arrow joining both, or a screenshot of a BEGIN ATOMIC block running in the SQL Editor. Suggested alt text: "Two tables updated inside a single atomic transaction boundary on Azure Databricks".]

1. Why this used to need two separate systems

Moving money between two accounts and writing an audit row look like three small operations, but a lakehouse without native transactions only gave you ACID guarantees per statement, per table. If the audit insert failed after both balance updates had already landed, nothing rolled the first two back automatically. Teams built custom reconciliation jobs to detect and repair that drift, or they ran the whole workflow on a separate warehouse system and synced results back into the lake afterward, duplicating both the data and the governance policy that protected it.

Multi-statement, multi-table transactions remove that second system. The requirement is narrow but firm: every table written to must be a Unity Catalog managed table, Delta Lake or Iceberg, with catalog commits enabled.

ALTER TABLE main.default.accounts
SET TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

My take: that single table property is doing more work than its one line suggests. It is the same catalog commits feature that cross-engine ABAC depends on for external reads, which means a table you enable for transactions is, incidentally, already a step closer to being safely readable from outside Azure Databricks too. Worth enabling deliberately on tables you expect to matter for governance later, not only the ones with an immediate transactional need.

2. Two modes, and two different conflict-detection granularities

Non-interactive transactions use a `BEGIN ATOMIC ... END;` block. Azure Databricks commits automatically if every statement succeeds, and rolls back automatically if any statement fails. This mode runs on any SQL warehouse, serverless compute, or a cluster on Databricks Runtime 18.0 and above, and it accepts SQL, PySpark `spark.sql()` calls, and Scala `spark.sql()` calls alike:

BEGIN ATOMIC
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO audit_log VALUES (1, 2, 100, current_timestamp());
END;

Interactive transactions use `BEGIN TRANSACTION`, followed by an explicit `COMMIT` or `ROLLBACK`, and they only run on a SQL warehouse:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO audit_log VALUES (1, 2, 100, current_timestamp());
COMMIT;

The two modes are not interchangeable conveniences, they detect conflict at a different granularity. Non-interactive transactions detect conflict at the row level: two transactions touching different rows in the same table do not conflict with each other. Interactive transactions detect conflict at the table level, except for unconditional appends. If your workload has many small transactions running concurrently against a shared table, that difference alone decides whether most of them succeed or most of them fail and retry.

3. Optimistic concurrency, consistent snapshots, and four ways to conflict

Neither mode locks a row while a transaction is open. Both use optimistic concurrency: the transaction runs freely, and Azure Databricks checks for conflicting changes only at commit time. If another transaction modified the same data after yours began, your commit fails and the transaction needs to be retried against fresh data.

The first read of a table inside a transaction fixes a snapshot, and every later read of that same table inside the same transaction uses that same snapshot, even if another session commits a change to it in between:

BEGIN TRANSACTION;
SELECT * FROM products WHERE product_id = 1001;
-- another session now runs, and commits:
--   UPDATE products SET price = 29.99 WHERE product_id = 1001;
SELECT * FROM products WHERE product_id = 1001;
-- still returns the original price, the snapshot was fixed
-- at the first read, not re-taken on the second one
COMMIT;

Four distinct scenarios can cause a commit to fail:

• Write-write conflicts: two transactions update or delete the same rows.
• Write-read conflicts: another transaction modified rows that yours read, on a table your transaction also wrote to. This applies only under Serializable isolation.
• Phantom read conflicts: another transaction inserted new rows matching a predicate your transaction read. This applies under both WriteSerializable and Serializable isolation.
• Metadata conflicts: another transaction changed the table's schema or properties mid-transaction.

Uncommitted changes inside an interactive transaction are visible only to your own session. A second session querying the same table sees nothing until you run `COMMIT`, which is the isolation guarantee that keeps one session from reading data another session might still roll back.

4. Hands-on: a staging, validate, and merge pattern

The pattern that benefits most from this feature is the one almost every ETL pipeline already has informally: land data in staging, check it, then merge it into production only if it passes. Written as one atomic unit, a failed check rolls back the staging load too, instead of leaving half-validated rows sitting in a staging table for the next run to trip over:

BEGIN ATOMIC
  -- Stage today's incoming rows
  INSERT INTO staging_customers
  SELECT * FROM external_customers
  WHERE ingest_date = current_date();

  -- Fail the whole batch if any row looks malformed
  IF (SELECT COUNT(*) FROM staging_customers
      WHERE ingest_date = current_date() AND email NOT LIKE '%@%') > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Malformed email addresses in today''s batch';
  END IF;

  -- Only reached if validation passed
  MERGE INTO customers AS target
  USING staging_customers AS source
  ON target.customer_id = source.customer_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;
END;

`SIGNAL` is what makes this work as a hard stop rather than a warning: raising it inside a `BEGIN ATOMIC` block triggers the same automatic rollback as any other statement failure, which means the staging insert from the first step also gets undone, not just the merge that never ran.

5. Error handling you control: SIGNAL, ROLLBACK, and GET DIAGNOSTICS

Interactive transactions give you the same decision point without forcing it into a single block. You can insert, inspect the result with an ordinary query, and only then decide whether to commit or discard:

BEGIN TRANSACTION;

UPDATE inventory
SET quantity = quantity - 50
WHERE product_id = 2001;

-- inspect before deciding
SELECT quantity FROM inventory WHERE product_id = 2001;

-- if the number above is negative, discard everything:
ROLLBACK;
-- otherwise:
-- COMMIT;

For non-interactive transactions driven by business logic rather than manual inspection, combine `SIGNAL` with `GET DIAGNOSTICS` to retrieve how many rows the most recent statement actually touched, which is useful for asserting that an update affected exactly the rows you expected before letting the transaction proceed:

BEGIN ATOMIC
  DECLARE rows_touched INT;

  UPDATE inventory SET quantity = quantity - 50 WHERE product_id = 2001;
  GET DIAGNOSTICS rows_touched = ROW_COUNT;

  IF rows_touched = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product 2001 not found, nothing to deduct';
  END IF;
END;

6. Transactions from an external client, with retry logic the docs leave to you

JDBC clients opt into interactive transactions by turning off autocommit explicitly:

Connection conn = DriverManager.getConnection(jdbcUrl, properties);
try {
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("INSERT INTO orders (order_id, total) VALUES (1001, 500.00)");
    stmt.executeUpdate("UPDATE customers SET last_order = CURRENT_DATE() WHERE customer_id = 5001");
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
} finally {
    conn.close();
}

The Databricks SQL Connector for Python follows the same pattern with `autocommit=False` on the connection:

from databricks import sql

with sql.connect(
    server_hostname="dbc-a1b2345c-d6e7.cloud.databricks.com",
    http_path="sql/1.0/warehouses/abc123def456",
    access_token="your-access-token",
    autocommit=False
) as connection:
    with connection.cursor() as cursor:
        cursor.execute("INSERT INTO orders (order_id, total) VALUES (1001, 500.00)")
        cursor.execute("UPDATE customers SET last_order = CURRENT_DATE() WHERE customer_id = 5001")
        connection.commit()

Neither example above retries on conflict, and under optimistic concurrency, a commit failing because another transaction got there first is not an edge case, it is the expected cost of allowing concurrency without locks. A client built on this feature needs retry logic as a first-class concern, not an afterthought:

from databricks import sql
import time

def run_with_retry(connection_factory, statements, max_attempts=3):
    for attempt in range(1, max_attempts + 1):
        with connection_factory() as connection:
            with connection.cursor() as cursor:
                try:
                    for statement in statements:
                        cursor.execute(statement)
                    connection.commit()
                    return
                except Exception as exc:
                    connection.rollback()
                    if attempt == max_attempts:
                        raise
                    # Back off before retrying against fresh data,
                    # the conflict was detected at commit time, so the
                    # transaction must be re-run from the start, not resumed.
                    time.sleep(0.5 * attempt)

My take: treat that retry wrapper as mandatory infrastructure, not optional polish, for any application code that writes through an interactive transaction under real concurrency. A transaction that silently never retries will look correct in every single-user test and then fail intermittently the day two users actually collide, which is exactly the kind of bug that is hard to reproduce on demand and easy to misdiagnose as something else.

7. What this doesn't solve

The scope has real edges that matter before redesigning a pipeline around this feature:

• DDL operations, `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, must run outside any transaction.
• Metadata operations that enumerate objects, `SHOW TABLES`, `SHOW DATABASES`, `DatabaseMetaData` calls over JDBC, ODBC catalog functions, and queries against system tables, are not supported inside a transaction on any protocol.
• Time travel cannot be used inside a transaction.
• A single transaction can read from or write to up to 100 tables combined, and read from up to 100 views, with up to 100 intermediate commits per table.
• Every transaction rolls back automatically after 48 hours total duration. Interactive transactions additionally roll back after 10 minutes of inactivity, and still require an explicit `ROLLBACK` afterward to clear the session's transaction state if the session itself is still connected.
• Row-level concurrency for `MERGE` is not available on AWS GovCloud or on single-user clusters, where `MERGE` falls back to table-level concurrency regardless of which transaction mode you use.
• Reading a non-transactional external source, Parquet, Avro, CSV, JSON, or a federated JDBC table, inside a transaction requires the explicit `allow_nontransactional_read` hint, and that read is not repeatable: concurrent changes to the source during the transaction can produce an inconsistent read.

That last point deserves its own code, because the failure mode if you skip the hint is a hard error, not a silent gap:

BEGIN TRANSACTION;
INSERT INTO transactional_table
SELECT col1, col2
FROM external_json_table
WITH (allow_nontransactional_read = true);
COMMIT;

8. My perspective on this

The detail I keep coming back to is that non-interactive and interactive transactions are not two ways to write the same thing, they are two different concurrency models wearing similar syntax. Row-level conflict detection under `BEGIN ATOMIC` and table-level detection under `BEGIN TRANSACTION` will behave identically in a demo with one writer and completely differently under real concurrent load. Picking between them by which syntax feels more familiar, rather than by which conflict granularity the workload actually needs, is the single most likely way to end up confused about why a transaction "randomly" fails in production when it never failed in testing.

I would also flag the metadata-conflict scenario as the one most teams forget to test for. Write-write and phantom-read conflicts are intuitive, two things touching the same data. A schema change landing mid-transaction and invalidating an in-flight commit is a rarer trigger, but it is exactly the kind of event a migration or a parallel DDL job can cause without anyone connecting it to a transaction failing somewhere else at the same time.

And the 10-minute idle timeout on interactive transactions is worth building defensively around rather than discovering in an incident: any interactive session that pauses for user input, a manual approval step, a human reading a validation result before deciding whether to commit, needs either a much shorter human-review window than 10 minutes or a design that does not leave a transaction open while waiting on that human at all.

Conclusion

Multi-table, multi-statement transactions do not make Azure Databricks a warehouse, they remove the specific reason a workload needed a separate warehouse system just to get atomicity across two related writes. The two modes, the four conflict scenarios, and the retry logic a real client needs to add on top of the SDK examples are the parts most likely to matter the first time this runs under genuine concurrency rather than in an isolated test. Build the retry path in from the start, choose the transaction mode based on the conflict granularity your workload actually needs, and treat the limitations list as a design constraint, not fine print to revisit only after something breaks.

Official references

Microsoft Learn, "Transactions - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/transactions/)
Microsoft Learn, "Transaction modes - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/transactions/transaction-modes)
Microsoft Learn, "Tutorial: Coordinate transactions across tables - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/transactions/tutorial)
Microsoft Learn, "Catalog commits - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/tables/features/catalog-commits)
Microsoft Learn, "Isolation levels and write conflicts - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/optimizations/isolation/)
Microsoft Learn, "Row-level concurrency - Azure Databricks" (learn.microsoft.com/en-us/azure/databricks/optimizations/isolation/row-level-concurrency)