Tutorial: Coordinate transactions across tables

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.

In this tutorial, you use both transaction modes to coordinate updates across multiple statements and tables on Databricks: non-interactive (BEGIN ATOMIC), which commits automatically, and interactive (BEGIN TRANSACTION), which gives you explicit control. The tutorial also demonstrates how to use transactions with stored procedures and SQL Scripting.

Requirements

Environment: Access to a Databricks workspace.
Compute: Supported compute types vary by transaction mode:
- A classic or serverless SQL warehouse supports both transaction modes.
- Serverless compute supports non-interactive transactions only.
- Classic clusters running Databricks Runtime 18.0 or above support non-interactive transactions only.
Privileges: CREATE TABLE in a Unity Catalog schema.

Set up sample tables

All tables written to in a multi-statement, multi-table transaction must:

Be Unity Catalog managed tables (Delta or Iceberg)
Have Catalog commits enabled

Create two sample tables in the SQL Editor or a notebook:

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

-- Transaction records
CREATE TABLE IF NOT EXISTS sample_transactions (
  id INT,
  account_id INT,
  transaction_type STRING,
  amount DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES (
  'delta.feature.catalogManaged' = 'supported'
);

note

To enable transactions on an existing table, run:

SQL
ALTER TABLE <table_name> SET TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

Insert sample data into both tables:

SQL
INSERT INTO sample_accounts VALUES
  (1, 'Alice', 1000.00),
  (2, 'Bob', 500.00);

INSERT INTO sample_transactions VALUES
  (1, 1, 'deposit', 100.00);

Verify the setup:

SQL
SELECT * FROM sample_accounts;
SELECT * FROM sample_transactions;

Output:

sample_accounts:
id	account_name	balance
1	  Alice	        1000.00
2	  Bob	          500.00

sample_transactions:
id	account_id	transaction_type	amount
1	           1	         deposit	100.00

Non-interactive transactions

Non-interactive transactions use BEGIN ATOMIC ... END; syntax. All statements run as a single atomic unit. If every statement succeeds, Databricks commits automatically. If any statement fails, Databricks rolls back all changes automatically. For detailed syntax and usage patterns, see non-interactive transactions.

Run a successful transaction

Update both tables atomically:

SQL
BEGIN ATOMIC
  -- Update Alice's account balance
  UPDATE sample_accounts
  SET balance = balance + 100.00
  WHERE id = 1;

  -- Record the deposit transaction
  INSERT INTO sample_transactions
  VALUES (2, 1, 'deposit', 100.00);
END;

Verify Alice's balance is now 1100.00:

SQL
SELECT * FROM sample_accounts WHERE id = 1;

Verify two transaction records now exist:

SQL
SELECT * FROM sample_transactions;

Both the balance update and the transaction record were created together. If either statement had failed, neither change would have committed, and Databricks would have terminated the transaction without side effects.

Use SIGNAL to fail a transaction on a condition

You can use SIGNAL inside a BEGIN ATOMIC ... END; block to fail the transaction when a user-defined condition is not met. This example inserts an account with a negative balance, then uses SIGNAL to fail the transaction if the balance check fails:

SQL
BEGIN ATOMIC
  INSERT INTO sample_accounts VALUES (3, 'Charlie', -50.00);

  IF (SELECT balance FROM sample_accounts WHERE id = 3) < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Account balance cannot be negative';
  END IF;
END;

The SIGNAL raises an error, which causes the entire transaction to roll back automatically. This returns zero rows because the insert was rolled back:

SQL
SELECT * FROM sample_accounts WHERE id = 3;

See automatic rollback on failure

Run a transaction where the first statement is valid but the second references a table that does not exist:

SQL
BEGIN ATOMIC
  -- Valid
  INSERT INTO sample_accounts VALUES (4, 'David', 300.00);
  -- Invalid
  INSERT INTO non_existent_table VALUES (1, 2, 3);
END;

The transaction fails with an error. This returns 0 rows because the entire transaction was rolled back:

SQL
SELECT * FROM sample_accounts WHERE id = 4;

Even though the first INSERT statement was valid, it was rolled back because the second statement failed. This demonstrates the all-or-nothing guarantee of transactions.

Interactive transactions

Interactive transactions give you explicit control over when to commit or roll back. Use BEGIN TRANSACTION to start, then COMMIT to save changes or ROLLBACK to discard them.

Commit changes

Start a transaction:

SQL
BEGIN TRANSACTION;

Make changes (not yet committed):

SQL
INSERT INTO sample_accounts VALUES (5, 'Eve', 850.00);
UPDATE sample_accounts SET balance = balance + 50.00 WHERE id = 2;

Commit to make changes permanent:

SQL
COMMIT;

Verify Eve's account is now visible:

SQL
SELECT * FROM sample_accounts WHERE id = 5;

Verify Bob's balance is now 550.00:

SQL
SELECT * FROM sample_accounts WHERE id = 2;

Roll back changes

Start a new transaction:

SQL
BEGIN TRANSACTION;

Make a change:

SQL
INSERT INTO sample_accounts VALUES (6, 'Frank', 600.00);

Verify the change is visible in your session (the row is not visible to other sessions until committed):

SQL
SELECT * FROM sample_accounts WHERE id = 6;

Roll back to discard the change:

SQL
ROLLBACK;

This returns zero rows because the insert was rolled back:

SQL
SELECT * FROM sample_accounts WHERE id = 6;

Use with stored procedures and SQL Scripting

You can combine transactions with stored procedures to create reusable transaction logic. This pattern is useful for complex operations that you run frequently.

Create the tables with catalog commits enabled

SQL
CREATE SCHEMA IF NOT EXISTS main.retail;

CREATE TABLE IF NOT EXISTS main.retail.orders (
  order_id STRING,
  customer_id STRING,
  amount DECIMAL(18,2)
) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

CREATE TABLE IF NOT EXISTS main.retail.orders_staging (
  order_id STRING,
  customer_id STRING,
  amount DECIMAL(18,2),
  batch_id STRING
) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

CREATE TABLE IF NOT EXISTS main.retail.total_sales (
  customer_id STRING,
  total_amount DECIMAL(18,2)
) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

Define the stored procedure

SQL
CREATE OR REPLACE PROCEDURE main.retail.apply_order(
    IN  p_order_id      STRING,
    IN  p_customer_id   STRING,
    IN  p_order_amount  DECIMAL(18,2)
)
LANGUAGE SQL
SQL SECURITY INVOKER
MODIFIES SQL DATA
AS
BEGIN
    -- Insert the order
    INSERT INTO main.retail.orders (order_id, customer_id, amount)
    VALUES (p_order_id, p_customer_id, p_order_amount);

    -- Update total sales per customer
    MERGE INTO main.retail.total_sales AS t
    USING (
        SELECT
          p_customer_id  AS customer_id,
          p_order_amount AS order_amount
    ) s
      ON t.customer_id = s.customer_id
    WHEN MATCHED THEN
      UPDATE SET t.total_amount = t.total_amount + s.order_amount
    WHEN NOT MATCHED THEN
      INSERT (customer_id, total_amount)
      VALUES (s.customer_id, s.order_amount);
END;

Define the transaction

SQL
BEGIN ATOMIC
    -- Staging batch id for this transaction
    DECLARE new_order_id STRING DEFAULT uuid();
    DECLARE v_batch_id STRING DEFAULT uuid();

    -- 1) Stage incoming customer and order rows
    INSERT INTO main.retail.orders_staging (order_id, customer_id, amount, batch_id)
    VALUES (new_order_id, 'CUST_123', 249.99, v_batch_id);

    -- 2) Drive final writes from staging to production via stored procedure
    FOR o AS
      SELECT
        order_id,
        customer_id,
        amount
      FROM main.retail.orders_staging
      WHERE batch_id = v_batch_id
    DO
        CALL main.retail.apply_order(
          o.order_id,
          o.customer_id,
          o.amount
        );
    END FOR;

    -- 3) Clean up processed staging rows
    DELETE FROM main.retail.orders_staging
    WHERE batch_id = v_batch_id;
END; -- 4) Commit the transaction

If any part of the transaction fails, Databricks rolls back all changes automatically.

Clean up

Remove the sample tables:

SQL
DROP TABLE IF EXISTS sample_accounts;
DROP TABLE IF EXISTS sample_transactions;

DROP TABLE IF EXISTS main.retail.orders;
DROP TABLE IF EXISTS main.retail.orders_staging;
DROP TABLE IF EXISTS main.retail.total_sales;

Next steps

Transactions: Overview of transaction support.
Transaction modes: Detailed syntax and patterns for both modes.
Catalog commits: Enable transaction support on your tables.
Use transactions from different clients: Run transactions from JDBC, ODBC, and Python applications.

Requirements​

Set up sample tables​

Non-interactive transactions​

Run a successful transaction​

Use SIGNAL to fail a transaction on a condition​

See automatic rollback on failure​

Interactive transactions​

Commit changes​

Roll back changes​

Use with stored procedures and SQL Scripting​

Clean up​

Next steps​

Related SQL reference​