Conceptually:
Transaction starts ↓ Update 101 ↓ SAVEPOINT ↓ Update 102 ↓ ROLLBACK TO SAVEPOINT ↓ Update 101 remains, Update 102 is undone
Exact savepoint syntax varies by database.
8️⃣ Why Are Transactions Important?
Imagine an order-processing system.
Creating an order might require:
1. Create order
2. Create order items
3. Reduce inventory
4. Record payment
5. Update customer balance
If step 4 fails after steps 1–3 succeed, you could end up with inconsistent data.
A transaction can group these operations together.
BEGIN ↓ Create order ↓ Create order items ↓ Reduce inventory ↓ Record payment ↓ Update balance ↓ COMMIT
If a critical operation fails: ROLLBACK
This helps keep the system consistent.
9️⃣ The ACID Properties
Transactions are commonly explained using the ACID properties:
• A → Atomicity
• C → Consistency
• I → Isolation
• D → Durability
These are fundamental database concepts.
🔟 Atomicity
Atomicity means a transaction is treated as a logical unit.
Either the required transaction changes are committed, or the transaction can be rolled back.
Example:
• Transfer ₹1,000
• Debit account A + Credit account B
You don't want only one side of the transfer to succeed.
Conceptually:
• Both succeed → COMMIT
• Critical failure → ROLLBACK
1️⃣1️⃣ Consistency
Consistency means a successful transaction should leave the database in a state that satisfies its defined rules and constraints.
For example:
• Account balance must not violate business/database constraints
Suppose a database has:
CHECK (balance >= 0)
An operation that violates the constraint may fail rather than leaving the database in an invalid state.
1️⃣2️⃣ Isolation
Isolation deals with how concurrent transactions interact with each other.
Imagine Transaction A + Transaction B both accessing the same data at the same time.
The database needs rules governing what each transaction can see while the other is running.
This becomes especially important in:
• Banking
• Payments
• Order processing
• Inventory systems
• Financial systems
• High-volume applications
1️⃣3️⃣ Durability
Once a transaction has successfully committed, its changes are intended to survive subsequent failures according to the database's durability guarantees.
Conceptually:
COMMIT ↓ Data saved ↓ System failure ↓ Committed changes remain
Durability is supported by database mechanisms such as transaction logs and recovery systems.
1️⃣4️⃣ Transaction Example — Bank Transfer
Suppose:
• Account 101 → ₹50,000
• Account 202 → ₹30,000
• Transfer: ₹5,000
SQL:
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 202;
COMMIT;
After success:
• Account 101 → ₹45,000
• Account 202 → ₹35,000
• The total money remains: ₹80,000
1️⃣5️⃣ What If Something Fails?
Suppose the second update fails.
Without appropriate transaction handling:
• Account 101 ₹50,000 → ₹45,000
• Account 202 Still ₹30,000
The system has become inconsistent.
With a transaction:
BEGIN ↓ Debit Account 101 ↓ Credit Account 202 ❌ ↓ ROLLBACK
The debit can be rolled back along with the other uncommitted changes.
1️⃣6️⃣ Transactions with INSERT
Transactions aren't limited to UPDATE.
Example:
BEGIN;
INSERT INTO orders (order_id, customer_id, order_amount)
VALUES (1001, 101, 5000);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 501, 2);
COMMIT;
Both operations belong to the transaction.
1️⃣7️⃣ Transactions with DELETE
You can also use transactions before deleting records.
For example:
BEGIN;
DELETE FROM orders
WHERE order_date < DATE '2020-01-01';
Before committing, you can inspect the result.
If it isn't what you expected: