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. 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 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 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 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 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 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, 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. Isolation Level Dirty Read Possible? Non-Repeatable Read Possible? Use Case READ UNCOMMITTED Yes Yes Rarely used, lowest safety READ COMMITTED No Yes Common in many applications REPEATABLE READ No No MySQL’s default, strong safety SERIALIZABLE No No Highest safety, slowest performance You can

Explore More