SQL Transactions

Learn COMMIT, ROLLBACK, SAVEPOINT, and ACID properties with real-world examples.

SQL Intermediate 60 min

🧠 Introduction: SQL Transactions

A SQL Transaction is a sequence of one or more SQL operations executed as a single logical unit of work. Transactions ensure that either all operations succeed or none of them are applied.

Transactions are critical for maintaining data integrity, especially in applications involving money transfers, bookings, updates across multiple tables, and concurrent users.

📝 Basic Transaction Flow:


                START TRANSACTION;

                SQL Statements;

                COMMIT / ROLLBACK;

🎯 Topic 1: Transaction Control Commands (TCL)

Transaction Control Language (TCL) commands are used to manage transactions in SQL.

✅ TCL Commands:

• START TRANSACTION – Begins a transaction
• COMMIT – Permanently saves changes
• ROLLBACK – Reverts changes
• SAVEPOINT – Creates rollback checkpoints

💻 Example: COMMIT


                START TRANSACTION;

                INSERT INTO Employees VALUES (9,'Arjun','IT',65000,'2023-01-01');

                COMMIT;

🔐 Topic 2: ACID Properties

ACID properties define the reliability and consistency of transactions.

• Atomicity – All or nothing execution
• Consistency – Database remains valid
• Isolation – Transactions don’t interfere
• Durability – Data persists after commit

💻 Atomicity Example


                START TRANSACTION;

                UPDATE Employees SET Salary = Salary - 500 WHERE EmployeeID = 1;

                UPDATE Employees SET Salary = Salary + 500 WHERE EmployeeID = 2;

                COMMIT;

↩️ Topic 3: ROLLBACK

ROLLBACK undoes all changes made in the current transaction.

• Used when errors occur
• Prevents partial updates
• Ensures data safety

💻 Example: ROLLBACK


                START TRANSACTION;

                DELETE FROM Employees WHERE EmployeeID = 5;

                ROLLBACK;

🧷 Topic 4: SAVEPOINT

SAVEPOINT allows partial rollback within a transaction.

• Create checkpoints
• Rollback to specific point
• Useful in long transactions

💻 Example: SAVEPOINT


                START TRANSACTION;

                INSERT INTO Employees VALUES (10,'Neha','HR',58000,'2023-02-01');

                SAVEPOINT sp1;

                INSERT INTO Employees VALUES (11,'Kiran','IT',62000,'2023-03-01');

                ROLLBACK TO sp1;

                COMMIT;

🚦 Topic 5: Isolation Levels

Isolation levels control how transactions interact with each other.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

📚 Module Summary

Transactions group SQL statements
COMMIT saves changes
ROLLBACK undoes changes
SAVEPOINT allows partial rollback
ACID ensures data integrity

🤔 Interview Q&A

A transaction is a group of SQL statements executed as a single unit that follows ACID properties.

COMMIT permanently saves changes, while ROLLBACK cancels all changes in the transaction.

SAVEPOINT creates a checkpoint within a transaction to allow partial rollback.

Atomicity, Consistency, Isolation, and Durability ensure reliable transactions.