MySQL Server Architecture, Locks, Transactions, Isolation Levels, MVCC, and Deadlock Management

MySQL Server Logical Architecture

Each client connection creates a thread on the MySQL server (managed internally by a thread pool). When a SELECT statement arrives, MySQL first checks the query cache; if not cached, it proceeds with parsing, optimization, and execution.

MySQL Concurrency Control – Shared Locks and Exclusive Locks

Shared Lock

Shared lock (read lock) allows multiple connections to read the same resource concurrently without interference.

Exclusive Lock

Exclusive lock (write lock) blocks other read or write locks, ensuring only one connection can write at a time.

Lock Strategy

Locks are expensive; a lock strategy balances thread safety and performance.

Table lock – the basic and lowest‑overhead lock that locks the entire table.

When a user performs a write, an exclusive lock may lock the whole table, blocking other reads/writes. When a user performs a read, a shared lock allows concurrent reads as long as no write lock is present.

DDL or DML statements that do not use an index (e.g., UPDATE table SET columnA='A' WHERE columnB='B' without an index on columnB) may cause a table lock; indexed queries use row locks.

Row lock – locks individual rows, providing higher concurrency at higher overhead.

Transactions

Transaction is a group of atomic SQL statements that are executed as a single unit; either all succeed or all are rolled back.

Example of a transfer transaction (Tim transfers 100 to Bill):

Check Tim's balance > 100

Deduct 100 from Tim

Add 100 to Bill

CREATE DATABASE IF NOT EXISTS employees;
USE employees;
CREATE TABLE `employees`.`account` (
  `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR(4),
  `p_money` DECIMAL(10,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);
INSERT INTO `employees`.`account` (`id`,`p_name`,`p_money`) VALUES ('1','tim','200');
INSERT INTO `employees`.`account` (`id`,`p_name`,`p_money`) VALUES ('2','bill','200');
START TRANSACTION;
SELECT p_money FROM account WHERE p_name="tim"; -- step1
UPDATE account SET p_money=p_money-100 WHERE p_name="tim"; -- step2
UPDATE account SET p_money=p_money+100 WHERE p_name="bill"; -- step3
COMMIT;

A robust transaction system must satisfy the ACID properties:

ACID

Atomicity – all operations succeed or all are rolled back.

Consistency – data moves from one consistent state to another.

Isolation – uncommitted changes are invisible to other sessions.

Durability – once committed, data persists even after a crash.

Isolation Levels

MySQL supports several isolation levels, each providing different guarantees.

READ UNCOMMITTED (Dirty Read)

Uncommitted changes are visible to other sessions, allowing dirty reads.

-- set global isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- Session B can see the uncommitted change, demonstrating a dirty read.

READ COMMITTED (No Dirty Read, Non‑Repeatable Read)

Changes become visible only after commit; prevents dirty reads but allows non‑repeatable reads.

-- set global isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Session A performs update and commits
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
COMMIT;
-- Session B sees the change only after commit.

REPEATABLE READ (Repeatable Read)

Within a transaction, repeated reads of the same query return identical results; prevents dirty and non‑repeatable reads, and uses next‑key locks to avoid phantom rows.

SERIALIZABLE (Serializable)

Strongest isolation; locks rows for every read and write, eliminating phantom reads but causing high contention.

Multi‑Version Concurrency Control (MVCC)

MVCC is a row‑level lock variant that provides non‑blocking reads by keeping multiple versions of rows.

Consistent read – reads a snapshot of data as of the transaction start (no locking).

Current read – reads the latest committed data and requires locking (e.g., SELECT … FOR UPDATE, LOCK IN SHARE MODE, INSERT, UPDATE, DELETE).

InnoDB implements MVCC by storing hidden columns with a row’s creation version and update version.

(row_create_version <= current_version) && (row_update_version IS NULL OR row_update_version > current_version)

Insert creates a row with the current version; Delete marks a row with the current version; Update creates a new version and marks the old row with the current version.

-- version handling example
INSERT ... (creates row with current version)
DELETE ... (marks row with current version)
UPDATE ... (creates new row with current version, updates old row's version)

Dirty Read vs Phantom Read vs Non‑Repeatable Read

Dirty read: reading uncommitted data from another transaction. Non‑repeatable read: the same query returns different results within a transaction because another transaction committed changes. Phantom read: a new row appears in a subsequent query because another transaction inserted it.

Preventing Phantom Reads in REPEATABLE READ

Use SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE to lock the range and prevent inserts that would cause phantom rows.

MySQL Deadlocks

A deadlock occurs when two sessions wait for each other's locks, forming a circular wait.

// Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
// Session B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;

Detection methods include the innodb_lock_wait_timeout timeout and the wait‑for‑graph algorithm. Mitigation strategies: access tables/rows in a fixed order, split large transactions, acquire all needed locks early, lower isolation level when possible, and add appropriate indexes.

Explicit vs Implicit Locks

Implicit locks are acquired automatically by InnoDB. Explicit locks are obtained with statements such as:

SELECT ... LOCK IN SHARE MODE;  -- shared lock
SELECT ... FOR UPDATE;          -- exclusive lock

Auto‑Commit

MySQL defaults to autocommit=1, meaning each statement is its own transaction. Setting autocommit=0 disables this behavior, requiring an explicit COMMIT to persist changes.

SHOW VARIABLES LIKE "autocommit";
SET autocommit=0; -- disable auto‑commit
SET autocommit=1; -- enable auto‑commit

Source: blog.csdn.net/lemon89/article/details/51477497