MySQL Transactions Explained: COMMIT, ROLLBACK & ACID Properties

MySQL

Data is one of the most valuable assets in any application. Whether you're building a banking system, an e-commerce platform, or an inventory management application, ensuring that data remains accurate and consistent is critical.

Imagine transferring money from one bank account to another. If the amount is deducted from one account but not added to the other because of a system failure, the data becomes inconsistent.

This is exactly the kind of problem MySQL Transactions are designed to solve.

A transaction groups multiple SQL operations into a single unit of work. Either all operations succeed together, or all of them fail together. This guarantees that your database always remains in a valid state.

In this guide, we'll learn everything about MySQL transactions, including COMMIT, ROLLBACK, SAVEPOINT, ACID properties, isolation levels, and best practices.

What Is a Transaction?

A transaction is a sequence of one or more SQL statements executed as a single logical unit.

A transaction ensures that:

Every operation succeeds.
Or none of them are applied.

This concept is known as Atomicity, one of the core ACID principles.

For example, transferring ₹5,000 from one bank account to another requires two operations:

Deduct ₹5,000 from Account A.
Add ₹5,000 to Account B.

If either operation fails, the entire transaction should be canceled.

Why Are Transactions Important?

Without transactions, partial updates can leave your database in an inconsistent state.

Imagine an online shopping application.

The checkout process performs multiple operations:

Create an order.
Reduce product inventory.
Process payment.
Generate an invoice.
Send a confirmation email.

If inventory is reduced but payment fails, the application should undo every previous change.

Transactions make this possible.

Benefits include:

Prevents partial updates
Maintains data consistency
Handles unexpected errors safely
Supports concurrent users
Improves application reliability

Starting a Transaction

A transaction begins with:

START TRANSACTION;

After this statement, MySQL waits until you either commit or roll back the changes.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

At this point, the change exists only inside the current transaction.

COMMIT

A COMMIT permanently saves all changes made during the transaction.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;

COMMIT;

After COMMIT:

Data is permanently saved.
Other users can see the changes.
The transaction ends.

When Should You Use COMMIT?

Use COMMIT only after every operation has completed successfully.

Common examples include:

Money transfer
Order placement
Inventory update
Payroll processing
Subscription renewal

ROLLBACK

ROLLBACK cancels every change made during the current transaction.

Example:

START TRANSACTION;

UPDATE products
SET stock = stock - 5
WHERE id = 100;

ROLLBACK;

The stock value returns to its original state.

It is as if the UPDATE never happened.

Common Use Cases

Rollback is useful when:

Payment fails
Validation fails
A server error occurs
Business rules are violated
A required record is missing

SAVEPOINT

Sometimes you don't want to undo the entire transaction.

Instead, you may want to return to a specific point.

This is where SAVEPOINT becomes useful.

Example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

SAVEPOINT transfer_started;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

ROLLBACK TO transfer_started;

COMMIT;

Only the changes after the savepoint are rolled back.

Everything before the savepoint remains intact.

Why Use SAVEPOINT?

Useful in:

Large workflows
Batch imports
Financial applications
Multi-step forms
Inventory processing

Understanding ACID Properties

Every transaction follows four important principles known as ACID.

Atomicity

Atomicity means "all or nothing."

Either every SQL statement succeeds or none of them are applied.

Example:

Deduct money ✔
Add money ✖

Result:

Everything is rolled back.

Consistency

A transaction must always move the database from one valid state to another.

Example:

Before transfer:

Account A = ₹20,000

Account B = ₹10,000

Total = ₹30,000

After transfer:

Account A = ₹15,000

Account B = ₹15,000

Total still = ₹30,000

The database remains consistent.

Isolation

Multiple users may perform transactions simultaneously.

Isolation ensures that one transaction doesn't interfere with another.

Example:

User A updates stock.

User B should not see incomplete changes until User A commits.

Durability

Once COMMIT is executed, changes are permanent.

Even if the server crashes immediately afterward, committed data remains safe.

Transaction Isolation Levels

MySQL provides different isolation levels to balance consistency and performance.

READ UNCOMMITTED

Transactions can see uncommitted changes made by other transactions.

Possible issue:

Dirty Reads.

READ COMMITTED

Only committed data is visible.

This prevents dirty reads.

REPEATABLE READ

The same query returns the same results throughout the transaction.

This is MySQL's default isolation level.

SERIALIZABLE

The strictest isolation level.

Transactions execute one after another.

It provides maximum consistency but can reduce performance.

Real-World Banking Example

Suppose a customer transfers ₹10,000.

Correct workflow:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 10000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 10000
WHERE id = 2;

COMMIT;

If the second UPDATE fails:

ROLLBACK;

The deduction is canceled automatically.

Both accounts remain accurate.

E-Commerce Example

During checkout:

Create Order
Reduce Inventory
Record Payment
Generate Invoice

If payment fails:

ROLLBACK;

No order is created.

Inventory remains unchanged.

Customers are not charged incorrectly.

Error Handling with Transactions

Applications should always handle transaction failures gracefully.

Example workflow:

Start Transaction

↓

Execute Queries

↓

Success?

↓

Yes → COMMIT

No → ROLLBACK

This pattern ensures data integrity.

Common Transaction Mistakes

Forgetting COMMIT

Without COMMIT, changes remain unconfirmed and may eventually be discarded.

Ignoring Errors

Always check whether every SQL statement executed successfully before committing.

Long Transactions

Avoid keeping transactions open for too long.

Long-running transactions:

Lock rows
Reduce concurrency
Slow other users

Mixing Business Logic with Transactions

Perform validations before starting the transaction whenever possible.

This keeps transactions short and efficient.

Best Practices

Keep transactions as short as possible.
Always handle exceptions.
Use ROLLBACK whenever an error occurs.
Commit only after all operations succeed.
Avoid unnecessary locks.
Use SAVEPOINT for complex workflows.
Choose the appropriate isolation level.
Test concurrency in production-like environments.

When Should You Use Transactions?

Transactions are essential for:

Banking applications
Online shopping
Payment gateways
Inventory management
Payroll systems
Booking platforms
Hospital management systems
ERP software

Any workflow involving multiple related database operations should use transactions.

Final Thoughts

Transactions are one of the most important features of MySQL because they ensure that your data remains accurate, consistent, and reliable even when errors occur. By grouping multiple operations into a single unit of work, transactions eliminate the risk of partial updates that could otherwise corrupt your database.

Understanding COMMIT, ROLLBACK, SAVEPOINT, and the ACID properties is essential for every developer working with MySQL. These concepts become even more important as your application grows and multiple users perform operations simultaneously.

Whenever you're implementing financial transactions, inventory updates, order processing, or any workflow involving multiple database operations, transactions should be your first choice. Combined with proper error handling and appropriate isolation levels, they form the foundation of secure and dependable database applications.