Share via

COMMIT

Applies to: check marked yes Databricks SQL

Commits the current interactive transaction, making all changes across all modified tables permanent. For requirements and usage patterns for interactive transactions, see Interactive transactions.

Syntax

COMMIT [ TRANSACTION | WORK ]

Parameters

This statement has no parameters.

Notes

  • If there is no active transaction, executing COMMIT results in a NO_ACTIVE_TRANSACTION error.
  • If COMMIT fails, the transaction is aborted and you must explicitly run ROLLBACK to reset the transaction state before beginning a new transaction. Common reasons for COMMIT failures include write conflicts with other concurrent transactions.
  • To execute this statement, you must have an active transaction and appropriate permissions on all the objects read or modified within the transaction.

Examples

The following examples demonstrate how to commit interactive transactions.

Basic 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');

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

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

-- Commit makes changes visible to other transactions
COMMIT;

Handle commit failure

If COMMIT fails due to conflicts or other errors, you must explicitly roll back. Run each statement separately.

BEGIN TRANSACTION;

-- Make changes
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Attempt to commit
COMMIT;
-- If COMMIT fails (e.g., due to write conflict),
-- the transaction is aborted

-- You must explicitly roll back before starting a new transaction
ROLLBACK;
  • Last updated on