MySQL Transactions Tutorial | COMMIT, ROLLBACK & ACID Properties Explained

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. Table of Contents Toggle What is a MySQL transaction? Why do you need transactions in MySQL? The core transaction commands ACID properties explained simply Understanding isolation levels in MySQL A complete real-world transaction example Why learn transactions at Aptech Learning Lucknow Frequently Asked Questions Conclusion 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. Command What It Does START TRANSACTION or BEGIN Marks the beginning of a transaction COMMIT Saves all changes made during the transaction permanently ROLLBACK Undoes all changes made during the transaction SAVEPOINT name Creates a checkpoint within a transaction you can roll back to ROLLBACK TO SAVEPOINT name Undoes changes back to a specific savepoint, not the entire transaction RELEASE SAVEPOINT name Removes a savepoint without rolling back 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. Property What It Means Simple Example Atomicity All statements in a transaction succeed together, or none do Bank transfer either fully completes or doesn’t happen at all Consistency The database moves from one valid state to another valid state Total money in the bank stays the same before and after transfer Isolation Concurrent transactions do not interfere with each other Two people booking the last movie seat don’t both succeed Durability Once committed, changes survive even a system crash After COMMIT, your data is safely written to disk permanently Atomicity — all or nothing Atomicity means a transaction is treated as a single,

Explore More