SQL Transactions (TCL)
Control the reliable and atomic execution of DML commands using TCL (Transaction Control Language) commands like COMMIT and ROLLBACK.
🏦 Introduction: The Bank Transfer Analogy
A **Transaction** is a fundamental concept in database management. It represents a single, logical unit of work, typically involving multiple steps.
Consider a bank transfer:
1. Debit Account A.
2. Credit Account B.
If step 1 succeeds but the system fails during step 2, the money is lost! A transaction ensures that **both steps either succeed entirely or fail entirely**.
🎯 Topic 1: Transaction Control Language (TCL)
**TCL** (Transaction Control Language) consists of commands used to manage transactions, giving you explicit control over when changes become permanent.
📝 The 3 Core TCL Commands:
START/BEGIN TRANSACTION: Marks the beginning of a transaction.COMMIT: Saves all changes made during the transaction permanently to the database.ROLLBACK: Undoes all changes since the transaction started, restoring the database to its previous state.
💻 Example: Basic Transaction
START TRANSACTION;UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;-- If an error occurs here, the previous 2 lines are undone by running:-- ROLLBACK;COMMIT; -- Saves the changes permanently
🔬 Topic 2: The ACID Properties (Database Guarantees)
To ensure data integrity and reliability, every transaction must meet the four guarantees known as **ACID**.
The ACID Test:
- A - Atomicity: All or nothing. The entire transfer succeeds or fails.
- C - Consistency: The transaction must move the database from one valid state to another (it must respect constraints).
- I - Isolation: Multiple concurrent transactions should not affect each other (like two people using the ATM at the same time).
- D - Durability: Once committed, changes are permanent, even if the system crashes.
📍 Topic 3: SAVEPOINT and Nested Rollbacks
Sometimes you need to undo only part of a long transaction. **SAVEPOINT** allows you to set a temporary marker within a transaction.
💻 Example: Rolling back to a SAVEPOINT
START TRANSACTION;UPDATE Accounts SET Balance = 500 WHERE ID = 1;SAVEPOINT Before_Risky_Operation;DELETE FROM Audit_Logs; -- Risky Step-- If Audit Logs deletion fails or is wrong:ROLLBACK TO Before_Risky_Operation; -- Undoes only the DELETE statementCOMMIT;
**Note:** You can still only `COMMIT` or `ROLLBACK` the entire transaction. You cannot `COMMIT` a `SAVEPOINT`.
📚 Module Summary
- Transaction: A single, logical unit of work (All or Nothing).
- COMMIT: Saves changes permanently.
- ROLLBACK: Undoes changes since the transaction started.
- ACID Properties: Guarantees (Atomicity, Consistency, Isolation, Durability) for reliability.
- SAVEPOINT: Creates a temporary marker within a transaction for partial rollback.
🤔 Interview Q&A
Tap on the questions below to reveal the answers.