Transaction Rollback - SQL Programming

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.

                            delta
                            delta = 4 (default)delta = 2delta = 6
                        

                            transaction_rollback.sql
                        
Replay: real traced execution (multi-file project)

                            CREATE TABLE inventory (sku TEXT, stock INTEGER);
INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);
BEGIN;
WITH params(delta) AS (VALUES (4)) 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 (2)) 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 (6)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';
ROLLBACK;
SELECT sku, stock FROM inventory ORDER BY sku;

                        
1tables ← 1 row1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);⋯values this step1 rowtables
2inventory ← 2 rows1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;⋯values this step2 rowsinventory
3inventory ← 2 rows⋯2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;4WITH params(delta) AS (VALUES (4)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';⋯values this step2 rowsinventory
4inventory ← 2 rows⋯3BEGIN;4WITH params(delta) AS (VALUES (4)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;⋯values this step2 rowsinventory
5inventory ← 2 rows⋯4WITH params(delta) AS (VALUES (4)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsinventory
6result ← 2 rows⋯5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);⋯values this step1 rowtables
2inventory ← 2 rows1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;⋯values this step2 rowsinventory
3inventory ← 2 rows⋯2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;4WITH params(delta) AS (VALUES (2)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';⋯values this step2 rowsinventory
4inventory ← 2 rows⋯3BEGIN;4WITH params(delta) AS (VALUES (2)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;⋯values this step2 rowsinventory
5inventory ← 2 rows⋯4WITH params(delta) AS (VALUES (2)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsinventory
6result ← 2 rows⋯5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);⋯values this step1 rowtables
2inventory ← 2 rows1CREATE TABLE inventory (sku TEXT, stock INTEGER);2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;⋯values this step2 rowsinventory
3inventory ← 2 rows⋯2INSERT INTO inventory VALUES ('pen', 12), ('pad', 7);3BEGIN;4WITH params(delta) AS (VALUES (6)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';⋯values this step2 rowsinventory
4inventory ← 2 rows⋯3BEGIN;4WITH params(delta) AS (VALUES (6)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;⋯values this step2 rowsinventory
5inventory ← 2 rows⋯4WITH params(delta) AS (VALUES (6)) UPDATE inventory SET stock = stock - (SELECT delta FROM params) WHERE sku = 'pen';5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsinventory
6result ← 2 rows⋯5ROLLBACK;6SELECT sku, stock FROM inventory ORDER BY sku;values this step2 rowsresult

Follow the Rollback

The table starts with pen stock 12 and pad stock 7.
The default delta is 4.
Inside the transaction, the script subtracts 4 from pen.
ROLLBACK cancels that change.
The final table is back to pad 7 and pen 12. | row | before transaction | after rollback | | --- | --- | --- | | pad | 7 | 7 | | pen | 12 | 12 |

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.

Exercise: transaction_rollback.sql

Reproduce the final rows pad 7 and pen 12, then use the pinned delta variants 2 and 6 to predict that rollback still leaves pad 7 and pen 12.