Transaction Commit - SQL Programming

A transaction groups related changes. COMMIT makes the group visible as the final database state.

Program

Play the script to move a selected amount between two accounts and commit the transfer.

                            amount
                            amount = 5 (default)amount = 3amount = 8
                        

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

                            CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);
BEGIN;
WITH params(amount) AS (VALUES (5)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');
COMMIT;
SELECT name, balance FROM accounts ORDER BY name;
CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);
BEGIN;
WITH params(amount) AS (VALUES (3)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');
COMMIT;
SELECT name, balance FROM accounts ORDER BY name;
CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);
BEGIN;
WITH params(amount) AS (VALUES (8)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');
COMMIT;
SELECT name, balance FROM accounts ORDER BY name;

                        
1tables ← 1 row1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);⋯values this step1 rowtables
2accounts ← 2 rows1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;⋯values this step2 rowsaccounts
3accounts ← 2 rows⋯2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;4WITH params(amount) AS (VALUES (5)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');⋯values this step2 rowsaccounts
4accounts ← 2 rows⋯3BEGIN;4WITH params(amount) AS (VALUES (5)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;⋯values this step2 rowsaccounts
5accounts ← 2 rows⋯4WITH params(amount) AS (VALUES (5)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsaccounts
6result ← 2 rows⋯5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);⋯values this step1 rowtables
2accounts ← 2 rows1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;⋯values this step2 rowsaccounts
3accounts ← 2 rows⋯2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;4WITH params(amount) AS (VALUES (3)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');⋯values this step2 rowsaccounts
4accounts ← 2 rows⋯3BEGIN;4WITH params(amount) AS (VALUES (3)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;⋯values this step2 rowsaccounts
5accounts ← 2 rows⋯4WITH params(amount) AS (VALUES (3)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsaccounts
6result ← 2 rows⋯5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);⋯values this step1 rowtables
2accounts ← 2 rows1CREATE TABLE accounts (name TEXT, balance INTEGER);2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;⋯values this step2 rowsaccounts
3accounts ← 2 rows⋯2INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);3BEGIN;4WITH params(amount) AS (VALUES (8)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');⋯values this step2 rowsaccounts
4accounts ← 2 rows⋯3BEGIN;4WITH params(amount) AS (VALUES (8)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;⋯values this step2 rowsaccounts
5accounts ← 2 rows⋯4WITH params(amount) AS (VALUES (8)) UPDATE accounts SET balance = CASE name WHEN 'Ada' THEN balance - (SELECT amount FROM params) WHEN 'Lin' THEN balance + (SELECT amount FROM params) END WHERE name IN ('Ada', 'Lin');5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsaccounts
6result ← 2 rows⋯5COMMIT;6SELECT name, balance FROM accounts ORDER BY name;values this step2 rowsresult

Follow the Transfer

The table starts with Ada 20 and Lin 10.
The default transfer amount is 5.
Inside the transaction, Ada loses 5.
Lin gains the same 5.
COMMIT keeps the final balances: Ada 15, Lin 15. | account | start | change | final | | --- | --- | --- | --- | | Ada | 20 | -5 | 15 | | Lin | 10 | +5 | 15 |

BEGIN `BEGIN` starts an explicit transaction.

atomic group Both balance changes are treated as one unit of work.

COMMIT `COMMIT` keeps the transaction's changes.

Exercise: transaction_commit.sql

Reproduce the final rows Ada 15 and Lin 15, then use the pinned amount variants 3 and 8 to predict Ada 17/Lin 13 and Ada 12/Lin 18.