ATOMIC compound statement

Applies to: Databricks SQL Databricks Runtime 18.0 and above

Implements a SQL Script block that can contain a sequence of SQL statements, control-of-flow statements, local variable declarations, and exception handlers. When marked as ATOMIC, the block runs as a transactional unit where all statements succeed together or fail together.

Syntax

BEGIN ATOMIC
  statement1;
  statement2;
  ...
END;

Parameters

None. The ATOMIC keyword modifies the compound statement behavior.

Description

Marks the compound statement as an atomic transaction block. All statements within the block run as a single transactional unit—either all statements succeed together, or all changes are rolled back if any statement fails. Azure Databricks automatically commits changes when the block completes successfully, or rolls back all changes if any statement fails.

BEGIN ATOMIC ... END blocks can be nested within other atomic blocks. The inner block is flattened into the outer transaction—all statements run as part of a single larger transaction. This is not a nested transaction. An atomic block cannot contain a non-atomic BEGIN ... END block.

This is a non-interactive transaction—you don't need to run COMMIT or ROLLBACK manually.

Requirements

• All tables written to in a multi-statement, multi-table transaction must:
  • Be Unity Catalog managed tables (Delta or Iceberg)
  • Have Catalog-managed commits enabled
• Use a SQL warehouse, serverless compute, or a cluster running Databricks Runtime 18.0 and above.
• You must have appropriate permissions on objects modified within the transaction. Privileges are checked when each statement runs.

Examples

The following examples demonstrate common transaction patterns using BEGIN ATOMIC ... END;.

Coordinate updates across multiple tables

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;

Validate data before committing

BEGIN ATOMIC
  INSERT INTO staging_customers
  SELECT * FROM external_source WHERE ingest_date = current_date();

  IF (SELECT COUNT(*) FROM staging_customers WHERE email NOT LIKE '%@%') > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email addresses found';
  END IF;

  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;

Related articles

• BEGIN TRANSACTION: Start an interactive transaction with manual commit/rollback control
• COMMIT: Commit an interactive transaction
• ROLLBACK: Roll back an interactive transaction
• Compound statement: BEGIN ... END blocks without the ATOMIC keyword
• Transactions: Overview of transaction support
• Transaction modes: Detailed patterns and examples for non-interactive and interactive transactions