🌱BeginnerData Manipulation
Transactions: BEGIN, COMMIT, ROLLBACK
Group SQL statements into atomic units with BEGIN, COMMIT, and ROLLBACK.
Overview
A transaction is a group of SQL statements that are treated as a single indivisible unit: either all succeed (COMMIT) or all fail (ROLLBACK). This is the "A" in ACID Atomicity. Without transactions, if your application crashes halfway through a bank transfer (debit customer A, credit customer B), you could end up with money debited but not credited.
PostgreSQL runs in "autocommit" mode by default each individual statement is its own transaction. Start an explicit transaction with BEGIN; to group multiple statements. End with COMMIT to save or ROLLBACK to undo. Savepoints let you create partial rollback points within a transaction.
Key Points
- •BEGIN (or START TRANSACTION) starts a transaction
- •COMMIT saves all changes permanently
- •ROLLBACK undoes all changes since BEGIN
- •In autocommit mode, each statement is its own transaction
- •SAVEPOINT name create a partial rollback point
- •ROLLBACK TO SAVEPOINT name undo to savepoint without losing earlier work
Examples
Basic transaction
BEGIN;
-- Debit customer account
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Credit receiving account
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- Check everything looks right
SELECT id, balance FROM accounts WHERE id IN (1, 2);
-- If everything is correct:
COMMIT;
-- If something went wrong:
-- ROLLBACK;Savepoints for partial rollback
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (999, 1); -- might fail
-- Rollback only the failed item insert, keep the order
ROLLBACK TO SAVEPOINT after_order;
-- Continue with correct data
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
COMMIT;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4