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. ROLLBACKalso undoesTRUNCATEin 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.