Transactions (BEGIN/COMMIT/ROLLBACK)
A transaction groups statements so they all succeed together or are all undone.
In this page:
Syntax
BEGIN;
-- statements
COMMIT;
BEGIN;
-- statements
ROLLBACK;Transactions (BEGIN/COMMIT/ROLLBACK)
BEGIN starts a transaction, COMMIT makes its changes permanent and ROLLBACK discards them. PostgreSQL provides ACID guarantees, and DDL statements are transactional too.
SAVEPOINT allows partial rollbacks. Isolation levels (read committed by default, repeatable read, serializable) control what concurrent transactions see.
Note:
Every statement outside BEGIN is its own automatic transaction.
Example: Transactions (BEGIN/COMMIT/ROLLBACK)
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER);
INSERT INTO accounts VALUES (1, 'Ada', 100), (2, 'Bob', 50);
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT owner, balance FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
ROLLBACK;
SELECT owner, balance FROM accounts ORDER BY id;
-- Output:
-- owner | balance
-- Ada | 70
-- Bob | 80
-- owner | balance
-- Ada | 70
-- Bob | 80Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Leaving transactions open for a long time
- Forgetting to roll back after an error
- Assuming autocommit works inside BEGIN
Chapter Summary
- BEGIN, COMMIT, ROLLBACK
- All or nothing
- ACID guarantees
- Isolation levels control concurrency
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: