Imagine you are transferring money from one bank account to another. The amount needs to be deducted from your account and added to the other person’s account. Now imagine the system crashes right after the deduction but before the addition happens. Your money has simply vanished — deducted from your account, never received by anyone.

Quick Answer: SQL & MySQL training at Aptech Learning Center, Mahanagar Extension, Kapoorthala, Lucknow, runs in offline classroom and live online batches across Uttar Pradesh. Indicative fee: ₹8,000 to ₹20,000, with a flat 10% festive discount on admission and placement assistance included.

This is exactly the kind of problem that MySQL transactions exist to prevent. If you have ever wondered what transactions actually are, why COMMIT and ROLLBACK matter, or what people mean when they talk about “ACID properties,” this tutorial explains all of it in plain language with practical examples you can run yourself.

What is a MySQL transaction?

A transaction is a group of one or more SQL statements that are executed together as a single unit. Either all of the statements succeed and the changes are saved permanently, or if anything goes wrong, none of the statements take effect at all.

Going back to the bank transfer example — the deduction and the addition are wrapped inside one transaction. If both succeed, the transaction is committed and the changes become permanent. If anything fails in between, the transaction is rolled back and the database returns to exactly how it was before the transaction started. The money is never lost, and it is never duplicated.

This concept is critical anywhere multiple related changes need to happen together — e-commerce orders that deduct stock and create an invoice, airline bookings that reserve a seat and charge payment, or any system where partial completion would cause real damage.

Why do you need transactions in MySQL?

Without transactions, every SQL statement you run executes and commits immediately on its own. This is called auto-commit mode, and it is MySQL’s default behaviour. For simple, independent operations, that is fine. But for operations that depend on each other, auto-commit mode is dangerous.

Consider this without a transaction:

sql

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
-- System crashes here
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 102;

If the system crashes after the first statement, account 101 has lost 5000 rupees and account 102 never received it. The data is now inconsistent, and there is no automatic way to fix it.

Now look at the same operation wrapped in a transaction:

sql

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 102;

COMMIT;

If anything fails before COMMIT runs, you can issue a ROLLBACK and both updates are undone completely. The database returns to its original, consistent state.

The core transaction commands

MySQL gives you a small set of commands to control transactions. Once you understand these, you understand the entire mechanism.

COMMIT — making changes permanent

COMMIT finalises all the changes made since the transaction started. Once a transaction is committed, the changes are permanent and visible to all other users and connections to the database.

sql

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (5, 1500.00);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 22;

COMMIT;

Both the new order and the updated inventory are now permanently saved together.

ROLLBACK — undoing everything

ROLLBACK cancels every change made since the transaction began. This is your safety net when something goes wrong mid-process.

sql

START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 101;

-- Check if balance went negative (insufficient funds)
-- If it did, cancel everything

ROLLBACK;

After ROLLBACK, it is as if the UPDATE statement never ran at all.

SAVEPOINT — partial rollbacks

Sometimes you do not want to undo an entire transaction, just a portion of it. SAVEPOINT lets you create a checkpoint and roll back to that exact point without losing everything that happened before it.

sql

START TRANSACTION;

UPDATE accounts SET balance = balance - 2000 WHERE account_id = 101;
SAVEPOINT after_deduction;

UPDATE accounts SET balance = balance + 2000 WHERE account_id = 999;
-- This account does not exist, this update fails or is wrong

ROLLBACK TO SAVEPOINT after_deduction;
-- The deduction from account 101 stays, only the bad update is undone

COMMIT;

ACID properties explained simply

ACID is an acronym that describes the four properties every reliable transaction must guarantee. Understanding ACID is what separates someone who can write COMMIT and ROLLBACK from someone who actually understands why transactions exist.

Atomicity — all or nothing

Atomicity means a transaction is treated as a single, indivisible unit. If any part of the transaction fails, the entire transaction fails and nothing is saved. There is no scenario where half of a transaction’s changes are applied and the other half are not.

Consistency — data stays valid

Consistency means a transaction can only bring the database from one valid state to another valid state. If a rule says account balances cannot go negative, no transaction is allowed to violate that rule, even temporarily during execution.

Isolation — transactions don’t step on each other

Isolation ensures that when multiple transactions run at the same time, they do not see each other’s incomplete, uncommitted changes. Without isolation, two customers could both see one seat as “available” at the same moment and both successfully book it — a serious real-world bug. MySQL handles this through isolation levels, covered next.

Durability — committed means permanent

Durability guarantees that once a transaction is committed, the changes are permanently stored, even if the server crashes immediately afterward. MySQL achieves this by writing committed data to disk, not just keeping it in memory.

Understanding isolation levels in MySQL

Isolation levels control exactly how much one transaction can see of another transaction’s in-progress changes. MySQL’s default storage engine, InnoDB, supports four standard isolation levels.

You can check and set the isolation level like this:

sql

SELECT @@transaction_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL uses REPEATABLE READ by default, which offers strong protection against most concurrency issues while maintaining good performance for typical applications.

A complete real-world transaction example

Here is a full, practical example — processing an order that must update three related tables together, with proper error handling logic built in.

sql

START TRANSACTION;

-- Step 1: Reduce product stock
UPDATE products 
SET stock_quantity = stock_quantity - 2 
WHERE product_id = 45 AND stock_quantity >= 2;

-- Step 2: Create the order record
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (12, 45, 2, NOW());

-- Step 3: Record the payment
INSERT INTO payments (order_id, amount, status)
VALUES (LAST_INSERT_ID(), 2400.00, 'completed');

-- If all three steps succeeded, save everything
COMMIT;

-- If stock was insufficient or any step failed, you would instead run:
-- ROLLBACK;

This pattern — checking conditions, making related updates, then committing only if everything is valid — is exactly how real e-commerce, banking, and booking systems prevent data corruption.

Why learn transactions at Aptech Learning Lucknow

Transactions are one of those topics that sound simple in theory but trip up developers in real projects when they are not understood properly. A missing COMMIT, a ROLLBACK placed in the wrong spot, or an unclear understanding of isolation levels can cause subtle bugs that are extremely difficult to trace later.

Aptech Learning Lucknow’s MySQL course covers transactions as part of the complete curriculum, with hands-on practice on real multi-table scenarios — not just isolated textbook examples. You learn how transactions fit into actual application logic, how to handle errors properly, and how to think about data consistency the way a working database professional does.

Visit aptechlearninglko.com to explore the full MySQL course and enrollment details.

Frequently Asked Questions

Conclusion

Transactions are not an advanced, optional topic you can skip — they are fundamental to building any application where data accuracy actually matters. Once you understand COMMIT, ROLLBACK, SAVEPOINT, and the four ACID properties behind them, you start writing SQL with a much deeper awareness of what can go wrong and how to prevent it.

The concepts here are simple once explained properly, but applying them correctly in real, multi-step database operations takes practice. That practical experience is exactly what a structured course builds.

For complete, hands-on MySQL training covering transactions and every other core topic, visit aptechlearninglko.com and start learning with proper guidance.

Ready for structured training? Explore our SQL & MySQL course in Lucknow for syllabus, batches and counselling — call +91 63861 19566.