💼 What is a Transaction in a Database?
A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) that are executed as a single logical unit of work.
A transaction must either complete fully or have no effect at all — there is no in-between.
🔒 Transactions follow the ACID properties:
| Property | Meaning |
|---|---|
| A – Atomicity | All operations succeed or none do — it’s all-or-nothing |
| C – Consistency | The database remains in a valid state before and after the transaction |
| I – Isolation | Transactions are isolated from each other while running |
| D – Durability | Once committed, the changes are permanent (even after crash) |
🛠️ Example:
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
✅ If both updates succeed: COMMIT makes them permanent
❌ If any fails: ROLLBACK undoes all changes
🔄 Commands:
| Command | Description |
|---|---|
BEGIN / START TRANSACTION | Starts a new transaction |
COMMIT | Saves all changes |
ROLLBACK | Cancels all changes since BEGIN |
🔍 Why Use Transactions?
- To transfer money safely between accounts
- To batch multiple operations that must succeed together
- To maintain data integrity in case of errors or crashes