Isolation controls concurrent visibility
Isolation determines how concurrent transactions observe committed and uncommitted changes. InnoDB supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. MySQL commonly uses REPEATABLE READ by default, but application code should verify the effective setting rather than relying on an assumption. Session and transaction-level configuration can affect behavior, and server defaults can differ across versions or managed environments.
The standard isolation levels describe classes of anomalies, but exact MySQL behavior also depends on InnoDB's MVCC implementation, read type, index access, and statement timing. Ordinary consistent reads and locking reads are not interchangeable. A transaction that performs several reads may see a consistent snapshot, while another statement deliberately requests locks to protect rows it intends to update. Learn the specific behavior of the deployed version before depending on subtle concurrency guarantees.
SELECT @@transaction_isolation;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;The first query reports the session isolation level. SET TRANSACTION applies a level to a following transaction in the current session; exact scope syntax should be checked for the target release. Set the level before starting the transaction and verify connection-pool behavior so the setting does not leak to another request.
MVCC and consistent reads
InnoDB uses multiversion concurrency control (MVCC) to let many consistent reads proceed without taking exclusive locks on every row they read. A transaction reads versions according to its snapshot and isolation rules. This can improve concurrency, but long-running transactions can retain old versions and delay purge. A report that runs for a long time may therefore affect storage and cleanup even if it is read-only. Keep snapshots bounded and consider reporting replicas or analytical systems for heavy workloads.
Locking reads and writes
A locking read such as SELECT ... FOR UPDATE requests locks for rows it reads, allowing a transaction to inspect and then modify them with less risk of another writer changing those records in between. The lock lasts until commit or rollback. The exact rows or ranges locked depend on the isolation level, index, search condition, and execution plan. A broad predicate or missing index may lock more than the developer expects, so make predicates selective and examine workload behavior.
START TRANSACTION;
SELECT quantity
FROM inventory
WHERE product_id = 42
FOR UPDATE;
-- Validate the quantity, update it, then COMMIT or ROLLBACK.The code illustrates a locking read on a product row. The application must keep the subsequent validation and update in the same transaction and on the same connection. For simple invariants, one guarded UPDATE may be shorter and safer than a separate read followed by a write. Select the pattern that preserves the invariant and handle affected-row counts correctly.
Indexes, deadlocks, and lock waits
Indexes influence which records MySQL examines while locating rows to read or update. A suitable index can reduce search work and lock footprint, but an index does not guarantee that only one record is locked under every isolation level. Query plans and predicate ranges matter. Use EXPLAIN to understand access paths and inspect InnoDB lock diagnostics when sessions wait unexpectedly. See index design and EXPLAIN.
Two transactions can acquire locks in conflicting orders and deadlock. InnoDB detects the cycle and rolls back one transaction. Keep transactions short, access rows in a consistent order, and retry recognized deadlock failures only when the complete unit is safe to replay. Do not disable detection or retry every timeout without diagnosing the cause. See deadlock handling.
Choose isolation for correctness
Do not lower isolation solely for speed without identifying which anomalies the application can tolerate. A report may tolerate some staleness; an inventory reservation or financial transfer may require stronger coordination. Application invariants can sometimes be protected with unique constraints, conditional updates, or explicit locking rather than a global isolation change. Discuss the consistency requirement, test concurrent operations, and measure contention at realistic load.
Concurrency testing
Single-session tests do not reveal isolation anomalies or lock order problems. Use two or more sessions to reproduce concurrent updates, verify blocking and commit visibility, and test rollback behavior. Capture the effective isolation level, statements, indexes, and transaction boundaries. Include cases where the row is absent, values change between reads, a lock wait times out, or a deadlock victim is chosen. Tests should verify the business invariant, not only the expected SQL result for a single execution.
Isolation and locking are tools for preserving correctness under concurrency. Choose them according to the invariant, keep transactions short, and validate behavior with multiple sessions on the target MySQL version. Continue with transactions, deadlocks, and InnoDB.