Understanding Transactions and Locks in MySQL: Concepts, Isolation Levels, and Practical Examples

Why Learn Transactions and Locks?

Transactions and locks are core techniques for guaranteeing data consistency and handling high‑concurrency scenarios in databases, preventing dirty data, resolving conflicts, and enabling the design of efficient and safe database applications.

Payment scenario: User A transfers money to User B; the operation must succeed or fail as a whole.

Inventory deduction: Hundreds of users attempt to buy a limited stock; the system must prevent overselling.

Report generation: Data should not change while a report is being calculated to ensure accuracy.

MySQL Example – Basic Transaction Operations

-- Start a transaction (two ways)
START TRANSACTION;  -- or BEGIN;

-- Insert an order record
INSERT INTO orders (user_id, amount) VALUES (1, 100);

-- Update account balance
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- Commit the transaction (make changes permanent)
COMMIT;

-- Rollback if an error occurs
ROLLBACK;

Note: MySQL's default isolation level is REPEATABLE READ . You can check it with:

SELECT @transaction_isolation;  -- Outputs: REPEATABLE-READ

Isolation Levels and Problem Reproduction

Scenario: Two sessions operate on the same row simultaneously to observe the behavior of different isolation levels.

-- Session 1: Transaction A modifies data but does not commit
START TRANSACTION;
UPDATE products SET stock = 5 WHERE id = 1;  -- Original stock 10

-- Session 2: Read under different isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT stock FROM products WHERE id = 1;  -- May read 5 (dirty read)
COMMIT;

Isolation level comparison table:

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

READ UNCOMMITTED

✔️

✔️

✔️

READ COMMITTED

✖️

✔️

✔️

REPEATABLE READ (default)

✖️

✖️

✔️

SERIALIZABLE

✖️

✖️

✖️

Lock Mechanisms in Practice

Pessimistic lock: Lock rows during the query to prevent other transactions from modifying them.

-- Lock product row for update when a user places an order
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- Acquire exclusive lock
IF stock >= 1 THEN
  UPDATE products SET stock = stock - 1 WHERE id = 1;
END IF;
COMMIT;

Optimistic lock: Use a version number or timestamp to detect conflicts, suitable for low‑contention scenarios.

-- Add a version column to the product table
ALTER TABLE products ADD version INT DEFAULT 0;

-- Update with version check
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version =
current_version
;

Deadlock Simulation and Handling

-- Session 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;  -- Locks user 1
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;  -- Waits for session 2

-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE user_id = 2;  -- Locks user 2
UPDATE accounts SET balance = balance + 200 WHERE user_id = 1;  -- Waits for session 1

-- Deadlock occurs; MySQL automatically rolls back one transaction
SHOW ENGINE INNODB STATUS;  -- View deadlock log

Tips to avoid deadlocks:

Access resources in a fixed order (e.g., sort by user ID).

Keep transaction duration short; avoid heavy logic inside transactions.

Set a reasonable lock wait timeout, e.g., SET innodb_lock_wait_timeout = 30;

Learning Effect Verification

Sample multiple‑choice questions are provided to test understanding of isolation levels, lock behavior, and optimistic‑lock implementation.

It is recommended to reproduce all examples in a local database and observe behavior under different parameters.

— End of article —