Play Store Application link – SQL in 18 steps – App on Google Play

Introduction

Imagine you’re booking a flight ticket online. You select a seat, make a payment, and expect confirmation. What if the system crashes after deducting your money but before confirming your seat? SQL transactions prevent such issues by ensuring database operations are atomic (all or nothing).

A transaction is a sequence of operations that must be executed completely or not at all to maintain data integrity.

1. ACID Properties – The Backbone of Transactions

A database transaction follows ACID principles:

• Atomicity – A transaction is all or nothing (either fully completes or fully rolls back).
• Consistency – The database remains in a valid state before and after a transaction.
• Isolation – Multiple transactions don’t interfere with each other.
• Durability – Once a transaction is committed, changes are permanent even if the system crashes.

Example Scenario:

Suppose a user transfers ₹5,000 from Account A to Account B:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 5000 WHERE account_id = 1;  -- Debit A
UPDATE Accounts SET balance = balance + 5000 WHERE account_id = 2;  -- Credit B
COMMIT;

If any step fails, the whole transaction must rollback to prevent money loss.

2. COMMIT, ROLLBACK, and SAVEPOINT

• COMMIT: Saves all changes permanently.
• ROLLBACK: Cancels all changes in the transaction.
• SAVEPOINT: Creates a checkpoint to partially rollback.

Example Scenario:

START TRANSACTION;
UPDATE Orders SET status = 'Processing' WHERE order_id = 101;
SAVEPOINT sp1;
UPDATE Payments SET status = 'Failed' WHERE payment_id = 501;
ROLLBACK TO sp1;  -- Undo only payment update, keep order update
COMMIT;

3. Isolation Levels – Controlling Concurrent Transactions

Isolation levels define how transactions interact when running at the same time.

Example Scenario (MySQL – InnoDB Engine):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
UPDATE Products SET stock = stock - 1 WHERE product_id = 10;
COMMIT;

SERIALIZABLE ensures no other transaction modifies the same Products table while this runs.

4. Locks and Deadlocks – Preventing Conflicts

When multiple users modify the same data, databases lock rows or tables to prevent conflicts.

Example Scenario:

Two users trying to book the last train seat at the same time!

SELECT * FROM Seats WHERE seat_id = 1 FOR UPDATE;  -- Locks row until commit

Deadlocks happen when two transactions wait for each other’s lock, causing a freeze. Databases automatically detect and resolve deadlocks.

5. MySQL (InnoDB) vs Oracle Transactions

Conclusion

Transactions are crucial for financial applications, e-commerce, and critical systems where data integrity matters. Using ACID properties, isolation levels, and proper locking, you can ensure safe and consistent database operations.