What is a transaction?
- SQL course
- Intermediate
- Lesson 100/115
- 4 min
Treat several operations as one unit.
What is a transaction?
A transaction is one or more operations treated as a single unit. All or nothing.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;A money transfer is one unit.
What is a transaction? — details
A transaction either fully succeeds or does not happen. On error it is rolled back with ROLLBACK.
For example, in a money transfer, debiting one account and crediting another is one transaction; if the second step fails, the first is undone and no money is lost.
Practice What is a transaction?
Start a transaction: write two UPDATEs with BEGIN and COMMIT.
Starter code
-- write transactionSample solution
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Expected output
Transaction tamamlandı
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What is the transaction principle?
- No going back
- Partial success
- All or nothing Correct answer
- Automatic saving
Explanation: A transaction is all or nothing.
2 Add BEGIN
Question
____;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
Answer
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;Explanation: BEGIN starts a transaction; changes up to COMMIT form a single unit.
3 What is a transaction: match the terms
| Term | Meaning |
|---|---|
transaction |
transaction |
BEGIN |
start |
COMMIT |
commit |
ROLLBACK |
rollback |