Chia sẻ qua

BEGIN TRANSACTION

Applies to: check marked yes Databricks SQL

Begins a new interactive transaction that groups multiple SQL statements into a single unit of work that can be committed or rolled back.

As an alternative to interactive transactions, you can define non-interactive transactions using the BEGIN ATOMIC ... END; syntax. See ATOMIC compound statement.

Syntax

BEGIN { TRANSACTION | WORK }

Parameters

This statement has no parameters.

Notes

  • If an interactive transaction is already active, executing BEGIN TRANSACTION again results in a TRANSACTION_NOT_SUPPORTED.NESTED_TRANSACTION error. Interactive transactions do not support nesting.
  • BEGIN TRANSACTION and BEGIN WORK are equivalent syntax alternatives.

Examples

The following examples demonstrate common interactive transaction patterns.

Basic cross-table transaction

Run each statement separately.

BEGIN TRANSACTION;

-- Insert a new record
INSERT INTO my_table VALUES (1, 'test');

-- Update records in another table
UPDATE another_table
SET value = 'updated'
WHERE id = 5;

-- Commit all changes in both tables atomically
COMMIT TRANSACTION;

Read-your-own-writes

Run each statement separately.

BEGIN TRANSACTION;

-- Insert new data
INSERT INTO customers VALUES (101, 'New Customer');

-- Query can see the inserted data within the same transaction
SELECT * FROM customers WHERE id = 101;
-- Returns the newly inserted row even though it's not yet committed

-- Update the newly inserted data
UPDATE customers SET name = 'Updated Customer Name' WHERE id = 101;

-- Query sees the updated value
SELECT name FROM customers WHERE id = 101;
-- Returns 'Updated Customer Name'

-- Commit makes changes visible to other transactions
COMMIT TRANSACTION;

Transaction with rollback

Run each statement separately.

BEGIN TRANSACTION;

-- Attempt an insert operation
INSERT INTO my_table VALUES (1, 'incorrect-value');

-- After discovering the mistake, rollback the transaction
-- (no changes are actually made to the tables)
ROLLBACK TRANSACTION;

Visibility of uncommitted changes

In this example, changes within a transaction are not visible to other sessions until the transaction is committed. Run each statement separately.

Session 1:

BEGIN TRANSACTION;

-- Insert new data
INSERT INTO products VALUES (999, 'New Product', 29.99);

-- At this point, Session 2 cannot see this data
-- You can see your own changes
SELECT * FROM products WHERE id = 999;
-- Returns the new product

Session 2 (concurrent):

-- This query does not see the uncommitted data from Session 1
SELECT * FROM products WHERE id = 999;
-- Returns no rows

Session 1 (continued):

-- Now commit the transaction
COMMIT TRANSACTION;

Session 2 (after commit):

-- Now the query can see the committed data
SELECT * FROM products WHERE id = 999;
-- Returns the new product

Visibility of concurrent changes

In this example, concurrent changes made outside the transaction are not visible to the transaction. Azure Databricks captures a consistent snapshot of each table at first access, and all subsequent reads of that table use this snapshot (repeatable read). Run each statement separately.

Session 1 (start a transaction and read the table):

BEGIN TRANSACTION;

-- Read the table; captures a snapshot of orders at this point
SELECT COUNT(*) FROM orders;
-- Returns: 1

Session 2 (concurrent session adds a row):

INSERT INTO orders VALUES (2, 'Product B', 75.00);
COMMIT;

Session 1 (continued, re-read the table):

-- Still reads from the original snapshot; the new row is not visible
SELECT COUNT(*) FROM orders;
-- Returns: 1 (unchanged, even though Session 2 committed a new row)

COMMIT;

This guarantees consistent reads throughout the transaction, regardless of concurrent modifications.

  • Last updated on