Share via

ROLLBACK

Applies to: check marked yes Databricks SQL

Rolls back the current transaction, discarding all changes made since the beginning of the transaction. For requirements and usage patterns for interactive transactions, see Interactive transactions.

Syntax

ROLLBACK [ TRANSACTION | WORK ]

Parameters

This statement has no parameters.

Notes

  • If there is no active transaction, executing ROLLBACK does not result in an error.
  • If an operation within a transaction results in an error, the transaction enters an aborted state. Subsequent read and write operations are rejected with an error until the transaction is explicitly rolled back by the user.
  • To execute this statement, you must have an active transaction and appropriate permissions on all the objects that have been read or modified within the transaction.

Examples

The following examples demonstrate how to roll back interactive transactions.

Basic rollback

Run each statement in a separate cell:

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;

Handle execution errors

When a statement within a transaction fails, you must explicitly roll back before starting a new transaction. Run each statement in a separate cell:

BEGIN TRANSACTION;

-- A query that causes a division by zero error
SELECT 1/0;

-- Throws error asking user to roll back the transaction
SELECT 1;

-- After error, user must roll back the transaction
-- as it cannot be committed
ROLLBACK TRANSACTION;

Handle parser errors

Parser errors also require explicit rollback. Run each statement in a separate cell:

BEGIN TRANSACTION;

-- A query that causes a parsing error
SELLLLLLECT 1;

-- Throws error asking user to roll back the transaction
SELECT 1;

-- After error, user must roll back the transaction
-- as it cannot be committed
ROLLBACK TRANSACTION;

Conditional rollback

You can use ROLLBACK for conditional logic based on business rules. Run each statement in a separate cell:

BEGIN TRANSACTION;

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

-- Check a condition and rollback if not met
BEGIN
  DECLARE total_balance INT;
  SET total_balance = (SELECT SUM(balance) FROM accounts);

  IF total_balance < 0 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;

Roll back after failed commit

If a COMMIT fails, you must explicitly roll back. Run each statement in a separate cell:

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