Transactions and Isolation Concepts

Transaction Rollback

Cancel a Group

ROLLBACK discards changes made since the transaction began. This protects the database from partial work.

Program

Play the script to apply a selected inventory change, then roll it back.

transaction_rollback.sql 
CREATE TABLE inventory (sku TEXT, stock INTEGER);
INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);
BEGIN;
WITH params(delta) AS (VALUES ()) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';
ROLLBACK;
SELECT sku, stock FROM inventory ORDER BY sku;

CREATE TABLE inventory (sku TEXT, stock INTEGER);
INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);
BEGIN;
WITH params(delta) AS (VALUES ()) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';
ROLLBACK;
SELECT sku, stock FROM inventory ORDER BY sku;

CREATE TABLE inventory (sku TEXT, stock INTEGER);
INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);
BEGIN;
WITH params(delta) AS (VALUES ()) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';
ROLLBACK;
SELECT sku, stock FROM inventory ORDER BY sku;
ROLLBACK `ROLLBACK` returns the database to the state before `BEGIN`.
partial work A failed group can be canceled instead of partly saved.
deterministic result The final `SELECT` shows the original stock.