SQL Notes
Learn what SQL transactions are, why they are essential for data integrity, and how BEGIN, COMMIT, and ROLLBACK work together to keep your database consistent.
Imagine you are transferring money from one bank account to another. The database needs to subtract from Account A and add to Account B. What if the system crashes after subtracting but before adding? The money has vanished. This is the kind of disaster that transactions prevent.
A transaction is a group of SQL operations that must either ALL succeed together or ALL fail together. There is no middle ground. If any part of the transaction fails, everything rolls back to the state before the transaction started, as if nothing happened.
Transactions are not just a nice-to-have feature — they are absolutely essential for any application that handles money, inventory, user accounts, or any data where partial updates would create inconsistencies. Every banking system, every e-commerce platform, and every enterprise application relies on transactions to guarantee data integrity. Without them, databases would be unreliable and applications would produce corrupted data under any kind of stress or failure condition.
Why Are Transactions Important?
Without transactions, multi-step operations can leave your database in an inconsistent state:
Scenario 1: Bank Transfer Without Transaction
-- Step 1: Debit Account A (succeeds)
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1001;
-- System crashes here! Step 2 never executes.
-- Step 2: Credit Account B (never happens)
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountID = 1002;Result: 5000 rupees disappeared from the system. Account A lost money, Account B never received it.
Scenario 2: Bank Transfer WITH Transaction
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1001;
-- System crashes here!
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountID = 1002;
COMMIT;Result: The transaction was never committed, so the database automatically rolls back. Account A still has its original balance. No money was lost.
Transaction Lifecycle
Every transaction goes through these stages:
- BEGIN — Start the transaction
- Execute SQL statements — INSERT, UPDATE, DELETE operations
- Decision point — Did everything succeed?
- COMMIT — If yes, save all changes permanently
- ROLLBACK — If no, undo all changes
BEGIN TRANSACTION;
-- Execute operations
INSERT INTO Orders (CustomerID, Amount) VALUES (101, 15000);
UPDATE Inventory SET Stock = Stock - 1 WHERE ProductID = 5;
INSERT INTO OrderLog (OrderID, Action) VALUES (SCOPE_IDENTITY(), 'Created');
-- Everything worked? Save it.
COMMIT;Basic Transaction Syntax
SQL Server
BEGIN TRANSACTION;
-- SQL statements
COMMIT TRANSACTION;
-- Or to undo:
BEGIN TRANSACTION;
-- SQL statements
ROLLBACK TRANSACTION;MySQL
START TRANSACTION;
-- SQL statements
COMMIT;
-- Or to undo:
START TRANSACTION;
-- SQL statements
ROLLBACK;PostgreSQL
BEGIN;
-- SQL statements
COMMIT;
-- Or to undo:
BEGIN;
-- SQL statements
ROLLBACK;Real-World Example: E-Commerce Order Placement
When a customer places an order, multiple operations must happen atomically:
BEGIN TRANSACTION;
-- 1. Create the order record
INSERT INTO Orders (CustomerID, OrderDate, Status, TotalAmount)
VALUES (101, CURRENT_DATE, 'Confirmed', 25000);
-- 2. Add order items
INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
VALUES (SCOPE_IDENTITY(), 5, 2, 12500);
-- 3. Reduce inventory
UPDATE Products SET Stock = Stock - 2 WHERE ProductID = 5;
-- 4. Deduct from customer wallet
UPDATE Customers SET WalletBalance = WalletBalance - 25000 WHERE CustomerID = 101;
-- 5. Create payment record
INSERT INTO Payments (OrderID, Amount, PaymentDate, Method)
VALUES (SCOPE_IDENTITY(), 25000, CURRENT_DATE, 'Wallet');
COMMIT;If any step fails — maybe the product is out of stock, or the wallet has insufficient balance — the entire order is rolled back. No partial state.
Real-World Example: Student Course Registration
START TRANSACTION;
-- Check seat availability
SELECT SeatsAvailable INTO @seats FROM Courses WHERE CourseID = 'CS301' FOR UPDATE;
-- If no seats, rollback
IF @seats <= 0 THEN
ROLLBACK;
END IF;
-- Register the student
INSERT INTO Enrollments (StudentID, CourseID, EnrollDate)
VALUES (2001, 'CS301', CURRENT_DATE);
-- Reduce available seats
UPDATE Courses SET SeatsAvailable = SeatsAvailable - 1 WHERE CourseID = 'CS301';
-- Update student's enrolled course count
UPDATE Students SET CoursesEnrolled = CoursesEnrolled + 1 WHERE StudentID = 2001;
COMMIT;Auto-Commit Mode
By default, most databases run in auto-commit mode. This means every single SQL statement is automatically wrapped in its own transaction:
-- In auto-commit mode, this is automatically:
-- BEGIN; INSERT INTO...; COMMIT;
INSERT INTO Customers (Name, Email) VALUES ('Rahul', 'rahul@email.com');When you explicitly write BEGIN TRANSACTION, you are telling the database to turn off auto-commit temporarily and let you control when changes are saved.
When to Use Transactions
Use explicit transactions whenever:
- Multiple tables are being modified together (order + inventory + payment)
- Related rows must stay consistent (debit one account, credit another)
- Business rules require atomic operations (registration + seat allocation)
- You need the ability to undo on error (validate before committing)
You do NOT need explicit transactions for:
- Single SELECT queries (read-only)
- Single INSERT/UPDATE/DELETE statements (auto-commit handles these)
Transaction States
A transaction exists in one of these states:
| State | Description |
|---|---|
| Active | Transaction started, operations executing |
| Partially Committed | All operations done, waiting for COMMIT |
| Committed | Changes permanently saved |
| Failed | An error occurred |
| Aborted | All changes rolled back |
Error Handling with Transactions
SQL Server
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 10000 WHERE AccountID = 1001;
UPDATE Accounts SET Balance = Balance + 10000 WHERE AccountID = 1002;
COMMIT TRANSACTION;
PRINT 'Transfer successful';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transfer failed: ' + ERROR_MESSAGE();
END CATCH;MySQL
DELIMITER //
CREATE PROCEDURE SafeTransfer(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Transfer failed' AS Result;
END;
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - amount WHERE AccountID = from_acc;
UPDATE Accounts SET Balance = Balance + amount WHERE AccountID = to_acc;
COMMIT;
SELECT 'Transfer successful' AS Result;
END //
DELIMITER ;Common Mistakes to Avoid
Mistake 1: Forgetting to COMMIT. If you begin a transaction and never commit, other users cannot see your changes, and the transaction holds locks that block others.
Mistake 2: Not handling errors. Without error handling, a failed statement leaves the transaction in a broken state. Always include ROLLBACK in error handlers.
Mistake 3: Long-running transactions. Transactions hold locks on data. Long transactions block other users. Keep transactions as short as possible.
Mistake 4: Using transactions for read-only queries. SELECT queries do not modify data and usually do not need explicit transactions.
Best Practices
- Keep transactions short — hold locks for the minimum time necessary
- Always include error handling with ROLLBACK in the catch block
- Use transactions for related modifications that must succeed or fail together
- Avoid user interaction inside transactions — do not wait for user input while holding locks
- Test with concurrent users to verify your transactions handle contention correctly
- Commit or rollback explicitly — never leave transactions hanging
Transactions are the foundation of reliable database operations. They guarantee that your data stays consistent even when things go wrong — crashes, errors, or concurrent access. Every professional database application relies on transactions to maintain data integrity.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Introduction to SQL Transactions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, transactions, transaction, introduction
Related SQL Complete Guide Topics