Transactions: All or Nothing

A transaction is a group of statements that succeed or fail together. Either all the changes are saved, or none of them are.

Think of a bank transfer. Taking money out of one account and adding it to another must both happen. If only one of them happens, the money is lost.


Syntax

BEGIN;
-- one or more statements
COMMIT;     -- save all the changes

Or, to undo everything since BEGIN:

BEGIN;
-- one or more statements
ROLLBACK;   -- cancel all the changes

Example 1: Save Changes with COMMIT

BEGIN;
UPDATE employees SET department = 'IT'
WHERE name = 'Lucy';
UPDATE employees SET department = 'Sales'
WHERE name = 'Mike';
COMMIT;

SELECT name, department FROM employees
WHERE name IN ('Lucy', 'Mike');

Output:

BEGIN
UPDATE 1
UPDATE 1
COMMIT
 name | department 
------+------------
 Lucy | IT
 Mike | Sales
(2 rows)

Both updates were saved together when COMMIT ran.


Example 2: Undo Changes with ROLLBACK

BEGIN;
DELETE FROM employees;
SELECT COUNT(*) FROM employees;
ROLLBACK;

SELECT COUNT(*) FROM employees;

Output:

BEGIN
DELETE 20
 count 
-------
     0
(1 row)

ROLLBACK
 count 
-------
    20
(1 row)

Inside the transaction, the table looked empty. ROLLBACK cancelled the delete, so all 20 employees are back.


Tip

  • Without BEGIN, PostgreSQL saves each statement as soon as it runs.
  • ROLLBACK also undoes TRUNCATE in PostgreSQL.
  • If a statement inside a transaction fails, PostgreSQL will not save any of the changes in that transaction.

In the exercise, change two employees in one transaction.