MySQL Transactions: Guide to Usage, Isolation Levels & Fixes

1. What is a MySQL transaction?

Definition and Necessity of Transactions

What are the “ACID properties” of a transaction?

• Atomicity (atomicity) All operations within a transaction are either all successful or all failed. If an error occurs partway through, all operations are canceled.
• Consistency (consistency) It guarantees that the database integrity is always maintained before and after the transaction. For example, it ensures that inventory counts never become negative.
• Isolation (isolation) Even when multiple transactions run concurrently, they must be processed so that they do not interfere with each other. This allows stable processing without being affected by other transactions.
• Durability (durability) Once a transaction is deemed “successful” and committed, the changes are guaranteed to be permanently stored in the database. They will not be lost even in the event of a power failure.

Benefits of Using Transactions in MySQL

• Ability to revert data to its previous state on error (ROLLBACK)
• Manage multi-step operations as a single logical unit
• Maintain consistency even during system failures

2. Basic Transaction Operations in MySQL

Starting, Committing, and Rolling Back Transactions

• START TRANSACTION or BEGIN: Start a transaction
• COMMIT: Commit and save changes
• ROLLBACK: Roll back changes to the original state

Example of a basic flow:

START TRANSACTION;

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

COMMIT;

ROLLBACK;

How to Configure Autocommit and Differences in Behavior

Check the current setting:

SELECT @@autocommit;

How to disable autocommit:

SET autocommit = 0;

Example: Safely Executing Multiple UPDATEs

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
INSERT INTO sales (product_id, quantity, sale_date) VALUES (10, 1, NOW());

COMMIT;

3. Isolation Levels and Their Impact

What is Isolation Level? Comparison of 4 Types

Phenomena That May Occur at Each Isolation Level

1. Dirty Read

• Reading data that another transaction has not yet committed.
• Preventable level: READ COMMITTED or higher

1. Non-Repeatable Read

• The data changes due to another transaction when the same query is executed multiple times.
• Preventable level: REPEATABLE READ or higher

1. Phantom Read

• When new rows are added or removed by another transaction, causing the result set of a query with the same condition to change.
• Preventable level: SERIALIZABLE only

How to Set Isolation Levels and Example Execution

Session-level Setting (Common Usage)

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Check the Current Isolation Level

SELECT @@transaction_isolation;

Example: Difference Between REPEATABLE READ and READ COMMITTED

-- Session A
START TRANSACTION;
SELECT * FROM products WHERE id = 10;

-- Session B
UPDATE products SET stock = stock - 1 WHERE id = 10;
COMMIT;

-- Session A
SELECT * FROM products WHERE id = 10; -- No change under REPEATABLE READ

4. Transaction Practical Scenarios

Examples of Use in Inventory Management and E‑commerce Sites

Example: Decreasing inventory and recording order history in a single transaction

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 101 AND stock > 0;
INSERT INTO orders (product_id, quantity, order_date) VALUES (101, 1, NOW());

COMMIT;

Designing Transactions for Bank Transfer Processing

• Debit account A
• Credit the same amount to account B

Example: Transfer Processing

START TRANSACTION;

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

COMMIT;

Code Examples of Using Transactions with Laravel and PHP

Transaction Handling in Laravel

DB::transaction(function () {
    DB::table('accounts')->where('id', 1)->decrement('balance', 10000);
    DB::table('accounts')->where('id', 2)->increment('balance', 10000);
});

Example: Manual Transaction with try‑catch

DB::beginTransaction();

try {
    // processing logic
    DB::commit();
} catch (Exception $e) {
    DB::rollBack();
    // log output, notifications, etc.
}

5. Pitfalls to Watch Out For and Performance Strategies

Operations That Cannot Be Rolled Back (DDL)

• CREATE TABLE
• ALTER TABLE
• DROP TABLE

Causes of Deadlocks and How to Avoid Them

Deadlock Example (Simplified)

• Transaction A locks row 1 and waits for the lock on row 2
• Transaction B locks row 2 and waits for the lock on row 1

Avoidance Strategies

• Standardize lock ordering When updating rows in the same table, design the code to always access them in the same order.
• Keep transaction duration short Exclude unnecessary work and issue COMMIT or ROLLBACK as soon as possible.
• Limit the number of rows accessed Use precise WHERE clauses to narrow the target and avoid locking the entire table.

Checklist When Transactions Feel Slow

• Are indexes set appropriately? Ensure columns used in WHERE clauses or JOIN conditions have indexes.
• Is the isolation level too high? Verify you aren’t using an unnecessarily high isolation level (e.g., SERIALIZABLE).
• Is autocommit still enabled during processing? Check that you are managing transactions explicitly.
• Are transactions being held for too long? A long interval between start and COMMIT can cause lock contention.
• Are InnoDB buffer pool and log sizes appropriate? Consider tuning the server settings relative to your data volume.

6. Tips Not Covered in Other Articles

How to Check Running Transactions

SHOW ENGINE INNODB STATUSG

• List of running transactions
• Transactions waiting for locks
• Deadlock history

How to Analyze Behavior from SQL and Slow Query Logs

• General log: records all SQL statements
• Slow query log: records only queries that took a long time to execute

Example of Enabling Slow Query Log (my.cnf)

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Experimenting with Transaction Behavior Across Multiple Sessions

Experiment Example: Verifying Behavior with REPEATABLE READ

• Session A

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM products WHERE id = 1;
-- Keep result

• Session B

UPDATE products SET name = 'Updated product name' WHERE id = 1;
COMMIT;

• Session A

SELECT * FROM products WHERE id = 1;
-- Change is not visible yet (because of REPEATABLE READ)
COMMIT;

7. Frequently Asked Questions (FAQ)

Q1. Are there cases where transactions cannot be used in MySQL?

SHOW TABLE STATUS WHERE Name = 'table_name';

Q2. Is it true that using transactions slows down processing?

• Holding a transaction open for a long time
• Using an unnecessarily high isolation level
• Insufficient index design leading to a wide lock scope

Q3. Does simply disabling autocommit create a transaction?

Q4. What should you do when an error occurs during a transaction?

Example (PHP + PDO)

try {
    $pdo->beginTransaction();

    // SQL operation
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    // Record error log, etc.
}

8. Summary

Transactions are the key to increasing reliability

Correct control and understanding are crucial

• Master the basic operations from START TRANSACTION to COMMIT / ROLLBACK
• Understand the difference between autocommit and explicit transaction management
• Adjust isolation levels to suit your goals, balancing performance and consistency

Knowing practical scenarios and tips makes you strong on the job