Transactions (BEGIN, COMMIT, ROLLBACK)
Overview
A transaction groups multiple SQL statements into a single, all-or-nothing logical unit of work. You start with BEGIN. If all commands succeed, you COMMIT to save them permanently. If something fails, you ROLLBACK to undo all changes made since BEGIN, leaving the database exactly as it was.
Without transactions, a failure halfway through a complex operation (like deducting money from one account but failing to add it to another) leaves the database in an inconsistent, corrupt state.
Where used: checkout processes, inventory management, bulk data imports
Why learn this
- You can guarantee data integrity even when hardware or network failures occur.
- You can safely test potentially destructive queries by wrapping them in a transaction and rolling back.
Query walkthrough
BEGIN;
UPDATE employees SET salary = salary + 5000 WHERE id = 7;
UPDATE employees SET salary = salary - 5000 WHERE id = 6;
COMMIT;Focus: Both updates act as a single unit. Either both employees have their salaries adjusted, or neither do.
Common mistakes
- Forgetting to COMMIT: If you issue a BEGIN but never COMMIT, your changes remain invisible to other users and will eventually be discarded when your session ends.
- Catching errors but not rolling back: In Postgres, if a query inside a transaction causes an error, the entire transaction becomes aborted. You MUST issue a ROLLBACK before you can run new queries in that session.
Glossary
- inconsistent
- A state where data does not match or make sense anymore, like money leaving one account but not arriving in another.
- destructive
- Having the potential to cause significant damage or permanent loss of data.
- aborted
- A process that is stopped early before it can finish, usually because an error occurred.
Recall questions
- What command makes the changes in a transaction permanent?
- What happens if a transaction is rolled back?
Understanding checks
What rows does the final SELECT query return?
price: 49.99
The update was wrapped in a transaction that was rolled back, so the price reverted to its original value of 49.99.
What rows does the final SELECT query return?
(No rows)
The transaction was committed successfully, making both deletions permanent.
Practice tasks
Rollback a mistake
You accidentally deleted all customers! Add the command needed to undo the deletion before it becomes permanent.
Challenge
Complete the transfer
Write a transaction that starts a block, updates the status of order id 3 to 'cancelled', then commits the changes.
Continue learning
Previous: UPDATE, DELETE & Returning