Transactions in SQL

What Is a Transaction?

A Transaction is a logical unit of work that contains one or more SQL statements.
All statements inside a transaction must either:

• Execute successfully together, or
• Fail completely and leave the database unchanged

This ensures data accuracy, safety, and reliability.

---

Real-World Example

Bank Transfer Scenario

• Debit ₹100 from Account A
• Credit ₹100 to Account B

Both operations must succeed together.
If one fails, the transaction must roll back.

---

ACID Properties of Transactions

Transactions follow ACID, which guarantees reliability.

---

1. Atomicity (All or Nothing)

• Either all statements execute successfully
• Or none of them execute

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

If second update fails → first update is undone.

---

2. Consistency (Valid State)

• Database moves from one valid state to another
• Constraints, rules, and relationships are preserved

Example:

• Balance cannot become negative
• Foreign keys must remain valid

---

3. Isolation (No Interference)

• Multiple transactions can run at the same time
• Each transaction behaves as if it is running alone

Prevents:

• Dirty reads
• Non-repeatable reads
• Phantom reads

---

4. Durability (Permanent Changes)

• Once committed, changes remain even after:
  • Power failure
  • Crash
  • Restart

---

Basic Transaction Syntax

START TRANSACTION;
-- or
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

Explanation

• START TRANSACTION begins the transaction
• SQL statements execute
• COMMIT permanently saves changes

---

ROLLBACK (Undo Changes)

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Something went wrong
ROLLBACK;

Explanation

• ROLLBACK cancels all changes in the transaction
• Database returns to previous state

---

SAVEPOINT (Partial Rollback)

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT step1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Undo only second update
ROLLBACK TO step1;

COMMIT;

Use Case

• Complex multi-step transactions
• Error recovery without full rollback

---

Transaction Control Commands (TCL)

Command	Purpose
START TRANSACTION / BEGIN	Start transaction
COMMIT	Save changes
ROLLBACK	Undo changes
SAVEPOINT	Create rollback point
ROLLBACK TO	Rollback to savepoint
RELEASE SAVEPOINT	Remove savepoint

---

Auto-Commit Mode

By default, many databases run in auto-commit mode.

SET autocommit = 0; -- Disable
SET autocommit = 1; -- Enable

Auto-Commit ON

• Every SQL statement is committed automatically

Auto-Commit OFF

• Changes saved only after COMMIT

---

Transaction Isolation Levels

Isolation level defines how much one transaction can see of another.

---

1. READ UNCOMMITTED (Lowest)

• Can read uncommitted data (dirty reads)
• Fast but unsafe

---

2. READ COMMITTED

• Reads only committed data
• Prevents dirty reads

---

3. REPEATABLE READ (Default in MySQL)

• Same row gives same value in transaction
• Prevents dirty & non-repeatable reads

---

4. SERIALIZABLE (Highest)

• Transactions run one after another
• Slowest but safest

---

Setting Isolation Level

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

Common Transaction Problems

Dirty Read

Reading data that is not committed yet.

Non-Repeatable Read

Same query returns different results in same transaction.

Phantom Read

New rows appear in repeated queries.

---

Transactions and Locks

• Transactions use locks to protect data
• Row-level locks (preferred)
• Table-level locks (less efficient)

---

Transactions with DML vs DDL

Operation	Transaction Support
INSERT	Yes
UPDATE	Yes
DELETE	Yes
SELECT	No
CREATE TABLE	Often auto-commit
DROP TABLE	Often auto-commit

⚠️ Many DDL commands implicitly commit.

---

Transactions in Real Applications

• Online payments
• Job application processing
• Inventory management
• Order placement
• Payroll systems

---

Best Practices

• Keep transactions short
• Avoid user input inside transaction
• Always handle errors
• Use rollback on failure
• Avoid long-running transactions
• Use proper isolation level

---

Common Mistakes

• Forgetting COMMIT
• Updating without transaction
• Long transactions causing locks
• Using high isolation unnecessarily
• Assuming DDL can rollback

---

Interview Questions on Transactions

1. What is a transaction?
2. Explain ACID properties
3. Difference between COMMIT and ROLLBACK
4. What is SAVEPOINT?
5. What are isolation levels?
6. What is dirty read?
7. Can DDL be rolled back?