Transaction modes

Preview

Transactions that write to Unity Catalog managed Delta tables are in Public Preview.

Transactions that write to Unity Catalog managed Iceberg tables are in Private Preview. To join this preview, submit the managed Iceberg tables preview enrollment form.

Transactions support two modes: non-interactive and interactive. This page covers when to use each mode and includes implementation examples.

For requirements and an overview of transactions, see Transactions. For hands-on practice with both modes, see Tutorial: Coordinate transactions across tables.

note

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

Non-interactive transactions

Non-interactive transactions use SQL scripting with the ATOMIC keyword. The ATOMIC compound statement block runs all statements as a single atomic unit. All succeed together or all fail together.

Supported compute: Any SQL warehouse, serverless compute, or cluster running Databricks Runtime 18.0 and above.

Supported syntax: Supports SQL, Scala spark.sql blocks, and PySpark spark.sql blocks.

note

You can use non-interactive transactions within Structured Streaming's forEachBatch by calling spark.sql("BEGIN ATOMIC ... END;"). However, Structured Streaming checkpoints do not advance transactionally.

Syntax

SQL

BEGIN ATOMIC
  statement1;
  statement2;
  statement3;
END;

Databricks automatically commits all changes if all statements succeed. If any statement fails, Databricks automatically rolls back all changes.

Use in SQL editor

Run non-interactive transactions directly in the SQL Editor. Select the entire ATOMIC compound statement block and run it as a single statement:

SQL

BEGIN ATOMIC
  DELETE FROM staging_sales WHERE load_date < current_date() - INTERVAL 7 DAYS;

  INSERT INTO staging_sales
  SELECT * FROM raw_sales WHERE load_date = current_date();

  MERGE INTO sales AS target
  USING staging_sales AS source
  ON target.sale_id = source.sale_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;
END;

Use in notebooks

Run non-interactive transactions in notebooks using SQL cells or programmatic APIs.

SQL

SQL

BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;

Python

Python

spark.sql("""
BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;
""")

Scala

Scala

spark.sql("""
BEGIN ATOMIC
  UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 2001;
  UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2002;
  INSERT INTO inventory_moves (from_product, to_product, quantity, move_date)
  VALUES (2001, 2002, 10, current_date());
END;
""")

Use in scheduled jobs

Non-interactive transactions work well in scheduled jobs because they automatically handle commit and rollback:

SQL

BEGIN ATOMIC
  -- Clear previous staging data
  DELETE FROM staging_daily_sales WHERE load_date = current_date();

  -- Load new data
  INSERT INTO staging_daily_sales
  SELECT sale_id, customer_id, amount, sale_date, current_date() as load_date
  FROM raw_sales
  WHERE sale_date = current_date() - INTERVAL 1 DAY;

  -- Validate row count (fails transaction if no data)
  IF (SELECT COUNT(*) FROM staging_daily_sales WHERE load_date = current_date()) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No sales data loaded for yesterday';
  END IF;

  -- Merge into production
  MERGE INTO daily_sales AS target
  USING staging_daily_sales AS source
  ON target.sale_id = source.sale_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;
END;

If any statement fails, including the assertion, the entire transaction rolls back automatically.

Use with JDBC

External clients can run non-interactive transactions.

JDBC

Java

String sql = """
    BEGIN ATOMIC
      INSERT INTO orders (order_id, total) VALUES (1001, 500.00);
      UPDATE customers SET last_order = CURRENT_DATE() WHERE customer_id = 5001;
    END;
    """;

Statement stmt = conn.createStatement();
stmt.execute(sql);

Use with Statement Execution API

Run non-interactive transactions using the Statement Execution API:

Python

import requests

sql = """
BEGIN ATOMIC
  INSERT INTO sales (sale_id, amount) VALUES (3001, 750.00);
  UPDATE daily_totals SET total = total + 750.00 WHERE sale_date = CURRENT_DATE();
END;
"""

response = requests.post(
    f"{workspace_url}/api/2.0/sql/statements",
    headers={"Authorization": f"Bearer {token}"},
    json={
        "warehouse_id": warehouse_id,
        "statement": sql,
        "wait_timeout": "30s"
    }
)

ETL patterns

The following patterns demonstrate common ETL workflows using non-interactive transactions.

Staging and validation pattern

This pattern loads data into a staging area, validates data quality, and merges validated records into production tables:

SQL

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

  -- Validate data quality
  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 validated data
  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 *;

  -- Update metadata
  UPDATE etl_metadata
  SET last_load_date = current_date(),
      rows_processed = (SELECT COUNT(*) FROM staging_customers)
  WHERE table_name = 'customers';
END;

Dimension and fact table pattern

This pattern updates dimension tables before loading fact tables to maintain referential integrity:

SQL

BEGIN ATOMIC
  -- Update dimension tables first
  MERGE INTO dim_products AS target
  USING staging_products AS source
  ON target.product_id = source.product_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  MERGE INTO dim_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 *;

  -- Then load fact table with foreign key references
  INSERT INTO fact_sales
  SELECT s.sale_id, p.product_key, c.customer_key, s.sale_amount, s.sale_date
  FROM staging_sales s
  JOIN dim_products p ON s.product_id = p.product_id
  JOIN dim_customers c ON s.customer_id = c.customer_id;
END;

Error handling

When a statement fails within a BEGIN ATOMIC ... END; block, Databricks rolls back all changes and returns an error message.

Debugging tips:

1. Review the error message to identify which statement failed.
2. Test statements individually outside the transaction block.
3. Add validation checks using SIGNAL to fail with custom error messages.
4. Query transaction history for additional context.

Interactive transactions

Interactive transactions give you explicit control over transaction boundaries. You manually begin a transaction, run statements, and explicitly commit or roll back.

Supported compute: SQL warehouses only.

Supported syntax: SQL only.

Syntax

SQL

BEGIN TRANSACTION;

statement1;
statement2;

COMMIT;
-- or: ROLLBACK;

Validate before committing

Use interactive transactions to validate results before committing:

SQL

BEGIN TRANSACTION;

-- Load staging data
INSERT INTO staging_customers
SELECT * FROM external_customers
WHERE load_date = current_date();

-- Validate and commit or rollback
BEGIN
  DECLARE duplicate_count INT;
  SET duplicate_count = (
    SELECT COUNT(*) FROM (
      SELECT customer_id, COUNT(*) as cnt
      FROM staging_customers
      WHERE load_date = current_date()
      GROUP BY customer_id
      HAVING COUNT(*) > 1
    )
  );

  IF duplicate_count > 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate customers found in staging data';
  ELSE
    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 *;
    COMMIT;
  END IF;
END;

Explicit rollback

Roll back a transaction when validation fails or business logic requires discarding changes:

SQL

BEGIN TRANSACTION;

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

-- Check if quantity would go negative
BEGIN
  DECLARE new_quantity INT;
  SET new_quantity = (SELECT quantity FROM inventory WHERE product_id = 2001);

  IF new_quantity < 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient inventory for product 2001';
  ELSE
    COMMIT;
  END IF;
END;

Use with JDBC

The JDBC driver supports running DML statements using executeUpdate() within transactions. For a list of supported DML statements, see Supported operations.

JDBC clients use interactive transactions by disabling auto-commit mode:

Java

Connection conn = DriverManager.getConnection(jdbcUrl, properties);

try {
    conn.setAutoCommit(false);  // Start transaction mode
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("INSERT INTO accounts (account_id, balance) VALUES (1001, 5000)");
    stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001");

    conn.commit();  // Commit the transaction

} catch (SQLException e) {
    conn.rollback();  // Roll back on error
    throw e;
} finally {
    conn.close();
}

Unsupported JDBC operations

The following JDBC operations are not supported within interactive transactions:

Category	Not supported
Catalog or schema switching	Connection.setCatalog() and Connection.setSchema()
Session configuration changes	Connection.setClientInfo() for session-level properties such as TIMEZONE and ANSI_MODE
All DatabaseMetaData (all protocols)	All DatabaseMetaData.* methods
PreparedStatement metadata	PreparedStatement.getMetaData()
Stored procedures	CALL procedure_name()

Use with ODBC

The ODBC driver supports running DML statements using SQLExecute() and SQLExecDirect() within transactions. For a list of supported DML statements, see Supported operations.

ODBC clients can use interactive transactions with the Databricks ODBC driver using standard ODBC transaction management functions.

Unsupported ODBC operations

The following ODBC operations are not supported within interactive transactions:

Category	Not supported
All catalog functions	SQLTables, SQLColumns, SQLStatistics, SQLSpecialColumns, SQLPrimaryKeys, SQLForeignKeys, SQLTablePrivileges, SQLColumnPrivileges, SQLProcedures, SQLProcedureColumns
Setting connection attributes	Catalog switching, isolation level changes, and access mode changes using SQLSetConnectAttr()
SQL translation	SQLNativeSql

Use with the Databricks SQL Connector for Python

The Databricks SQL Connector for Python supports running DML statements using cursor.execute() within transactions. For a list of supported DML statements, see Supported operations.

Python applications can use interactive transactions with the Databricks SQL Connector for Python by setting autocommit=False:

Python

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 accounts (account_id, balance) VALUES (1001, 5000)")
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001")
        connection.commit()

Unsupported Python connector operations

The following Python connector operations are not supported within interactive transactions:

Category	Not supported
All metadata	cursor.catalogs(), cursor.schemas(), cursor.tables(), cursor.columns()

Driver limitations for interactive transactions

The following limitations apply to all drivers when using interactive transactions.

Metadata operations are not supported inside interactive transactions. The following operations might fail within a transaction regardless of the driver or protocol:

Driver/Protocol	Type	Methods
JDBC	DatabaseMetaData	getCatalogs(), getSchemas(), getTables(), getColumns(), getTypeInfo()
ODBC	Catalog functions	SQLTables, SQLColumns, SQLGetTypeInfo
Python connector	Metadata methods	cursor.catalogs(), cursor.schemas(), cursor.tables(), cursor.columns()
SQL	Metadata commands	SHOW TABLES, SHOW DATABASES, DESCRIBE TABLE, USE CATALOG, USE SCHEMA
SQL	information_schema	SELECT queries against information_schema tables

Run all metadata operations outside of transactions.

warning

Running transactions on multiple threads on a single driver connection object leads to undefined behavior. Run only one transaction at a time on each connection object.

Isolation behavior

Uncommitted changes in an interactive transaction are only visible to your session. Other sessions see the table state as it was before your transaction began.

note

Interactive transactions use more conservative conflict detection than non-interactive transactions and can conflict at the table level (except for unconditional appends). For row-level conflict detection, use non-interactive transactions (BEGIN ATOMIC ... END;).

1. To verify isolation, create the sample table if it doesn't exist:

SQL

CREATE TABLE IF NOT EXISTS sample_accounts (
  id INT,
  account_name STRING,
  balance DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

1. In that same session, start a transaction and make a change:

   SQL

   BEGIN TRANSACTION;
   INSERT INTO sample_accounts VALUES (10, 'Test', 100.00);

2. In a separate SQL Editor tab or notebook session (not a new cell in the same notebook), query the table:

   SQL

   -- Run this in the SECOND session
   SELECT * FROM sample_accounts WHERE id = 10;

   This returns 0 rows because the uncommitted change is not visible outside your first session.

3. Return to your first session and commit:

   SQL

   COMMIT;

4. Query from the second session again:

   SQL

   -- Run this in the SECOND session
   SELECT * FROM sample_accounts WHERE id = 10;

   The row is visible because the transaction has been committed.

This isolation prevents other users from reading data that might be rolled back.

Choose a transaction mode

Scenario	Recommended mode
Scheduled ETL jobs	Non-interactive—automatic commit or rollback simplifies error handling
Fixed statement sequences	Non-interactive—simpler syntax, no manual commit needed
Data validation before commit	Interactive—inspect results and decide whether to commit
JDBC applications needing manual control	Interactive—standard database transaction patterns

Next steps

• Tutorial: Coordinate transactions across tables
• Transactions
• Catalog-managed commits
• Isolation levels and write conflicts

Related SQL reference

• ATOMIC compound statement (non-interactive transactions): Run multiple SQL statements as a single atomic transaction with automatic commit and rollback.
• BEGIN TRANSACTION (interactive transactions): Begin an interactive transaction with manual commit and rollback control.
• COMMIT: Commit an interactive transaction and make all changes permanent.
• ROLLBACK: Roll back an interactive transaction and discard all changes.