Transactions and Isolation Concepts

Transaction Commit

Make Changes Durable

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.

transaction_commit.sql 
CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 20), ('Lin', 10);
BEGIN;
WITH params(amount) AS (VALUES ()) 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 ()) 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 ()) 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;
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.