MySQL Transactions, Isolation Levels, Concurrency Challenges, and MVCC Explained

Transactions

1.1 Transaction Basics

A transaction is a sequence of operations defined by the user in a concurrent connection scenario. It functions as an indivisible unit: either all operations execute successfully, or none do. In MySQL, transactions group multiple SQL statements to run as a single logical batch.

  • Purpose: Transactions transition the database from one consistent state to another, ensuring the system remains in a valid, complete state at all times.
  • Composition: A transaction can consist of a single simple SQL statement or a complex set of interdependent statements.
  • Key Behavior: When committing a transaction, MySQL guarantees that either all modifications are persisted to disk, or none are. In InnoDB, even individual SQL statements are treated as implicit transactions.

1.2 Transaction Control Statements

-- Explicitly start a transaction
BEGIN;
-- Commit the transaction, making all database changes permanent
COMMIT;
-- Roll back the transaction, undoing all uncommitted modifications
ROLLBACK;
-- Create a savepoint within the transaction (multiple savepoints allowed)
SAVEPOINT sp_1;
-- Remove an existing savepoint
RELEASE SAVEPOINT sp_1;
-- Roll back to a specific savepoint
ROLLBACK TO SAVEPOINT sp_1;

1.3 ACID Properties

Atomicity (A)

A transaction is an indivisible execution unit. Either all operations within the transaction are applied, or none are. If an error occurs during execution, the database uses undo logs to revert to the initial state before the transaction started. Undo logs store historical versions of data rows, enabling logical rollbacks (e.g., reversing an INSERT with a DELETE, or an UPDATE with a counter-update). Additionally, undo logs support Multi-Version Concurrency Control (MVCC) by maintaining row version history.

Consistency (C)

Transactions ensure the database transitions between valid states that adhere to all integrity constraints (e.g., primary keys, foreign keys, unique constraints). Consistency is enforced jointly by atomicity, isolation, and durability. For example, a funds transfer between two accounts must deduct and add amounts in a way that preserves the total balance of the system.

Isolation (I)

Isolation prevents concurrent transactions from interfering with each other. It mitigates issues like dirty reads, non-repeatable reads, and phantom reads by balancing concurrency performance with data integrity. InnoDB implements isolation using two core mechanisms:

  • MVCC: Enables non-locking reads by serving historical row versions, eliminating read-write blocking.
  • Locks: Used for concurrent data modification operations. InnoDB supports three lock granularities: table-level, page-level, and row-level locks on clustered index B+ trees.

Durability (D)

Once a transaction commits, all modifications are permanently saved, evenif the database or server crashes. This is achieved via redo logs, which record physical changes to database pages (e.g., which page to update, offset within the page, and new data). Redo logs are written to disk sequentially at commit time, and during recovery, the database replays these logs to restore committed changes.

Isolation Levels

The ISO/ANSI SQL standard defines four transaction isolation levels to balance concurrency and data consistency. MySQL InnoDB defaults to the Repeatable Read level.

2.1 Isolation Level Categories

2.1.1 Read Uncommitted

The lowest isolation level: a transaction can read uncommitted changes from other transactions. This means modifications from an in-progress transaction are visible to concurrrent transactions before being committed.

  • Concurrency Issues: Prone to dirty reads, non-repeatable reads, and phantom reads.
  • Locking Behavior: Reads do not acquire locks; writes automatically acquire exclusive locks, which are released only when the transaction commits or rolls back.

2.1.2 Read Committed

A transaction can only read changes that have been committed by other transactions. Modifications are visible to concurrent transactions only after the originating transaction commits.

  • Concurrency Issues: Eliminates dirty reads, but non-repeatable reads and phantom reads may still occur.
  • Locking Behavior: Uses MVCC to provide consistent non-locking reads. Reads fetch the latest committed historical snapshot of data, and writes acquire exclusive locks.

2.1.3 Repeatable Read

Once a transaction starts, all reads within the transaction return the same data, consistent with the state at transaction initiation. This prevents non-repeatable reads.

  • Concurrency Issues: Eliminates dirty reads and non-repeatable reads. While InnoDB's implementation minimizes phantom reads, they can still occur in specific scenarios.
  • Locking Behavior: Uses MVCC to fetch the data state as it existed when the transaction started. Writes acquire exclusive locks.

2.1.4 Serializable

The highest isolation level: transactions are executed sequentially. When a read-write conflict occurs, the later transaction waits until the earlier one completes.

  • Concurrency Issues: Eliminates all concurrency-related issues (dirty reads, non-repeatable reads, phantom reads).
  • Locking Behavior: Reads acquire shared locks, writes acquire exclusive locks. This ensures strict serial execution but can lead to significant performance overhead in high-concurrency environments.

2.2 Isolation Level Configuration & Lock Commands

-- Set session-level isolation level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Set global isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current session isolation level
SELECT @@session.transaction_isolation;
-- Check global isolation level
SELECT @@global.transaction_isolation;

-- Acquire shared lock for read operations
SELECT balance FROM user_balance WHERE username = 'Alice' LOCK IN SHARE MODE;
-- Acquire exclusive lock for read operations (prevents other writes/locks)
SELECT balance FROM user_balance WHERE username = 'Alice' FOR UPDATE;

-- View active lock information
SELECT * FROM information_schema.innodb_locks;

2.3 Concurrency Issue Test Scripts

-- Clean up and create test table
DROP TABLE IF EXISTS user_balance;
CREATE TABLE user_balance (
    user_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    balance INT(11) NOT NULL DEFAULT 0,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert test data
INSERT INTO user_balance (username, balance) 
VALUES ('Alice', 2000), ('Bob', 2000), ('Charlie', 2000), ('Diana', 2000);

-- Test 1: Dirty Read (Read Uncommitted level)
-- Transaction 1 (uncommitted update)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE user_balance SET balance = balance - 500 WHERE username = 'Alice';
-- Transaction 2 (reads uncommitted change)
-- SELECT balance FROM user_balance WHERE username = 'Alice'; -- Returns 1500 before commit
-- Rollback Transaction 1 to undo change
ROLLBACK;

-- Test 2: Non-Repeatable Read (Read Committed level)
-- Transaction 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM user_balance WHERE username = 'Bob'; -- Returns 2000
-- Transaction 2
UPDATE user_balance SET balance = balance + 300 WHERE username = 'Bob';
COMMIT;
-- Transaction 1 reads again
SELECT balance FROM user_balance WHERE username = 'Bob'; -- Returns 2300 (different from first read)
COMMIT;

-- Test3: Phantom Read (Repeatable Read level)
-- Transaction1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM user_balance WHERE balance >= 2000; -- Returns 4 initially
-- Transaction2
INSERT INTO user_balance (username, balance) VALUES ('Eve', 2000);
COMMIT;
-- Transaction1 reads again (InnoDB prevents phantom read here with gap locks)
SELECT COUNT(*) FROM user_balance WHERE balance >= 2000; -- Still returns4 in Repeatable Read
COMMIT;

-- Test4: Lost Update (Concurrency write conflict)
-- Transaction1
BEGIN;
SELECT balance FROM user_balance WHERE username = 'Charlie'; -- Reads 2000
-- Transaction2
BEGIN;
SELECT balance FROM user_balance WHERE username = 'Charlie'; -- Reads2000
UPDATE user_balance SET balance = 2000 + 100 WHERE username = 'Charlie'; -- Sets to2100
COMMIT;
-- Transaction1 commits outdated update
UPDATE user_balance SET balance = 2000 - 100 WHERE username = 'Charlie'; -- Sets to1900 (overwrites Transaction2's change)
COMMIT;

Multi-Version Concurrency Control (MVCC)

MVCC is a concurrency control mechanism that enables non-locking read operations, eliminating read-write blocking (only write-write operations block each other). This significantly improves database throughput in high-concurrency environments. InnoDB uses MVCC for the Read Committed and Repeatable Read isolation levels, but with key differences in how snapshot data is fetched:

  • Read Committed: Fetches the latest committed version of a row for each read operation.
  • Repeatable Read: Fetches the row version that existed when the transaction started, using the same snapshot for all reads in the transaction.

3.1 Read View

A Read View is a snapshot of active transactions generated when a transaction performs a snapshot read. It determines which row versions are visible to the transaction. The core components of a Read View are:

  • active_trx_ids: List of IDs for transactions that were active (started but not committed) when the Read View was created.
  • lowest_active_trx_id: The smallest transaction ID in active_trx_ids.
  • next_trx_id: The ID that will be assigned to the next new transaction.
  • current_trx_id: The ID of the transaction that created the Read View.

The timing of Read View creation differs between isolation levels:

  • Read Committed: A new Read View is created for each SELECT statement in the transaction, which can lead to non-repeatable reads.
  • Repeatable Read: A single Read View is created when the transaction starts, ensuring consistent reads throughout the transaction.

3.2 Current Read vs Snapshot Read

Current Read

A current read fetches the most recent committed version of data. It requires acquiring locks to ensure data consistency, as other transactions may be modifying the same rows. Examples of current read operations:

-- Shared lock read
SELECT balance FROM user_balance WHERE username = 'Alice' LOCK IN SHARE MODE;
-- Exclusive lock read
SELECT balance FROM user_balance WHERE username = 'Alice' FOR UPDATE;
-- Data modification operations
INSERT INTO user_balance (username, balance) VALUES ('Frank', 1500);
UPDATE user_balance SET balance = balance + 200 WHERE username = 'Bob';
DELETE FROM user_balance WHERE username = 'Charlie';

Snapshot Read

A snapshot read fetches a historical version of data (from a consistent snapshot) without acquiring locks. This allows reads to proceed without blocking writes and vice versa. All ordinary SELECT statements are snapshot reads in InnoDB (when using MVCC-enabled isolation levels):

-- Snapshot read example
SELECT username, balance FROM user_balance WHERE balance > 1000;

3.3 Clustered Index Hidden Columns

InnoDB adds two hidden columns to every clustered index record to support MVCC:

  • transaction_id: Stores the ID of the transaction that last modified the row.
  • version_pointer: A pointer to the previous version of the row in the undo log chain. This allows traversal of historical row versions for rollback or snapshot reads.

3.4 Transaction Visibility Rules

A transaction can only see row versions that meet the following criteria (using the Read View components):

  1. If the row's transaction_id < lowest_active_trx_id: The row was modified by a transaction that committed before the Read View was created, so it is visible.
  2. If the row's transaction_id >= next_trx_id: The row was modified by a transaction that started after the Read View was created, so it is not visible.
  3. If lowest_active_trx_id <= transaction_id < next_trx_id: Check if transaction_id is in active_trx_ids. If yes: the modifying transaction was active when the Read View was created, so the row is not visible. If no: the modifying transaction committed before the Read View was created, so the row is visible. Additionally, a transaction can always see its own modifications, even if they are uncommitted.

Redo Log

The redo log is a physical log that ensures transaction durability. It records the exact physical changes made to database pages (e.g., which page to update, the offset in the page, and the new data values).

  • When a transaction commits, all redo log entries for the transaction are written to disk sequentially (sequential writes are much faster than random writes).
  • If the database crashes before changes are written to the main data files, the redo log is replayed during recovery to restore all committed transactions, ensuring no data loss.

Undo Log

The undo log is a logical log that supports atomicity and MVCC:

  • Atomicity: For each modification in a transaction, the undo log records the reverse operation (e.g., an INSERT is reversed with a DELETE, an UPDATE with the original value). This allows the database to roll back uncommitted transactions to their initial state.
  • MVCC: The undo log stores historical row versions, which are used by snapshot reads to fetch consistent data without blocking writes. Undo logs are stored in the InnoDB shared tablespace and are automatically purged when no longer needed by active transactions.

Tags: MySQL Database Transactions isolation levels Concurrency Control MVCC

Posted on Wed, 26 Aug 2026 16:01:08 +0000 by savingc

Hot Tags

©2026 Freaks City