Skip to main content
Last updated on

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.

SQL
BEGIN TRANSACTION;
SQL
-- Insert a new record
INSERT INTO my_table VALUES (1, 'test');
SQL
-- Update records in another table
UPDATE another_table
SET value = 'updated'
WHERE id = 5;
SQL
-- Commit all changes in both tables atomically
COMMIT TRANSACTION;

Read-your-own-writes​

Run each statement separately.

SQL
BEGIN TRANSACTION;
SQL
-- Insert new data
INSERT INTO customers VALUES (101, 'New Customer');
SQL
-- Update the newly inserted data
UPDATE customers SET name = 'Updated Customer Name' WHERE id = 101;
SQL
-- Query sees the updated value within the transaction
SELECT name FROM customers WHERE id = 101;
-- Returns 'Updated Customer Name'
SQL
-- 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.

SQL
BEGIN TRANSACTION;
SQL
-- Make changes
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SQL
-- Attempt to commit
COMMIT;
-- If COMMIT fails (e.g., due to write conflict),
-- the transaction is aborted
SQL
-- You must explicitly roll back before starting a new transaction
ROLLBACK;

Additional resources​

On this page