MySQL Transactions COMMIT, ROLLBACK, ACID: Tutorial, Examples, FAQs & Interview Tips

MySQL Transactions COMMIT, ROLLBACK, ACID

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

ACID Properties

A transaction is a sequence of SQL statements that are executed as a single unit of work. MySQL's InnoDB engine guarantees ACID properties:

Atomicity - All statements in a transaction succeed together, or all are rolled back. There's no partial success.
Consistency - A transaction brings the database from one valid state to another, respecting all constraints and rules.
Isolation - Concurrent transactions don't interfere with each other. Each transaction sees a consistent snapshot of the data.
Durability - Once a transaction is committed, the changes are permanent - even if the server crashes immediately after.

START TRANSACTION, COMMIT, and ROLLBACK

-- Place an order: insert order + items atomically
START TRANSACTION;

-- Step 1: Insert the order
INSERT INTO orders (customer_id, total, status)
VALUES (1, 0.00, 'pending');

-- Step 2: Get the new order ID
SET @order_id = LAST_INSERT_ID();

-- Step 3: Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (@order_id, 1, 1, 1299.99),
    (@order_id, 2, 2, 29.99);

-- Step 4: Update the order total
UPDATE orders
SET total = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = @order_id
)
WHERE order_id = @order_id;

-- Step 5: Reduce stock
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
UPDATE products SET stock = stock - 2 WHERE product_id = 2;

-- All steps succeeded - commit the transaction
COMMIT;

-- If any step fails, roll back all changes
-- ROLLBACK;

SAVEPOINT and Partial Rollback

A SAVEPOINT marks a point within a transaction that you can roll back to without rolling back the entire transaction. This is useful for complex transactions where you want to undo only part of the work.

SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

START TRANSACTION;

-- Insert a new customer
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Henry', 'Adams', 'henry@example.com', 1);

-- Create a savepoint after the customer insert
SAVEPOINT after_customer;

-- Try to insert an order for the new customer
INSERT INTO orders (customer_id, total, status)
VALUES (LAST_INSERT_ID(), 150.00, 'pending');

-- Something went wrong with the order - roll back to the savepoint
-- (the customer insert is preserved, only the order is undone)
ROLLBACK TO SAVEPOINT after_customer;

-- Release the savepoint (optional cleanup)
RELEASE SAVEPOINT after_customer;

-- Commit the customer insert
COMMIT;

Autocommit and Transaction Isolation Levels

By default, MySQL runs in autocommit mode - every statement is automatically committed. To use explicit transactions, either disable autocommit or use START TRANSACTION (which temporarily disables autocommit for that transaction).

Autocommit and Isolation Levels

-- Check autocommit setting
SHOW VARIABLES LIKE 'autocommit';

-- Disable autocommit for the session
SET autocommit = 0;

-- Now every statement requires an explicit COMMIT or ROLLBACK
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Iris', 'Clark', 'iris@example.com', 1);
COMMIT;  -- must commit manually

-- Re-enable autocommit
SET autocommit = 1;

Transaction Isolation Levels

-- Check current isolation level
SELECT @@transaction_isolation;

-- Set isolation level for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set global isolation level (affects all new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Isolation levels (from least to most strict):
-- READ UNCOMMITTED : can read uncommitted changes from other transactions (dirty reads)
-- READ COMMITTED   : only reads committed data; non-repeatable reads possible
-- REPEATABLE READ  : default in MySQL; same SELECT returns same data within a transaction
-- SERIALIZABLE     : strictest; transactions execute as if serial (no concurrency issues)

-- Example: use REPEATABLE READ for a financial report
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
    SELECT SUM(total) AS total_revenue FROM orders WHERE status = 'delivered';
    -- ... other report queries ...
    -- All SELECTs see the same snapshot of data
COMMIT;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as rollback after a mid-step failure, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is forgetting which operations must succeed or fail together. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: check committed rows in a second session.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving rollback after a mid-step failure.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: forgetting which operations must succeed or fail together.

Common Mistakes to Avoid

WRONG Forgetting which operations must succeed or fail together.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test rollback after a mid-step failure before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include rollback after a mid-step failure and record the difference.
Break the example by deliberately forgetting which operations must succeed or fail together, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test rollback after a mid-step failure. The main warning sign is forgetting which operations must succeed or fail together.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Transactions COMMIT, ROLLBACK, ACID: Tutorial, Examples, FAQs & Interview Tips