MySQL Indexes, Lock Types, Transaction Isolation Levels, and Deadlock Analysis

1. MySQL Index Lookup

This section introduces MySQL index and table data structures to help understand later locking issues.

MySQL indexes and table data are independent. An index lookup can only retrieve indexed columns and the primary key (clustered index). If a SELECT does not include indexed columns, MySQL uses the primary key to perform a back‑table lookup, returning rows ordered by the clustered index. If the SELECT contains only indexed columns and the clustered index, no back‑table lookup occurs, and the result is already ordered.

Even without an explicit primary key, MySQL creates a clustered index on a non‑null column.

Example table (name and age are indexed, id is the primary key):

CREATE TABLE `test_user_info` (
  `id` bigint(20) NOT NULL COMMENT 'Primary Key ID',
  `name` varchar(255) DEFAULT NULL COMMENT 'User Name',
  `age` int(11) DEFAULT NULL COMMENT 'Age',
  `salary` decimal(15,2) DEFAULT '100.00' COMMENT 'Bonus',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User Info Table';

Covering query example (no back‑table lookup):

SELECT name, id FROM my_test.test_user_info WHERE name IN ('zhangsan','lisi'); All needed values are in the index, and the result is ordered.

Back‑table query example (requires additional lookup):

SELECT name, id, age FROM dongfeng_batch.test_user_info WHERE name IN ('zhangsan','lisi'); Because the index does not contain the age column, MySQL first finds the primary key via the name index, then performs a back‑table lookup to fetch age, with the final result ordered by the primary key.

2. MySQL InnoDB Transaction Isolation Levels

Show current isolation level:

SHOW VARIABLES LIKE 'transaction_isolation';

The default isolation level for InnoDB is REPEATABLE‑READ .

Four isolation levels supported by InnoDB:

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

READ UNCOMMITTED

Yes

Yes

Yes

READ COMMITTED

No

Yes

Yes

REPEATABLE READ

No

No

Possible

SERIALIZABLE

No

No

No

Higher isolation levels provide stronger consistency but reduce performance.

3. MySQL Lock Classification

MySQL lock family (1):

3.1 Lock Granularity

(1) Table lock – locks the whole table; supported by all storage engines, low overhead, but highest contention.

(2) Row lock – also called record lock; only InnoDB supports it; fine‑grained, higher overhead, highest concurrency, but can cause deadlocks.

(3) Page lock – locks an entire data page; supported by BDB; medium granularity.

Engine support matrix:

Engine

Row Lock

Table Lock

Page Lock

MyISAM

Supported

InnoDB

Supported

Supported

BDB

Supported

Supported

3.1.1 Table‑level Lock Types

Table read lock (shared, S) and table write lock (exclusive, X).

Metadata lock (MDL) – automatically acquired for DDL and DML statements.

Auto‑increment lock (AUTO‑INC) – used when inserting rows into tables with an AUTO_INCREMENT column.

Intention locks (IS, IX) – indicate a transaction’s intention to acquire row‑level locks. Intention Shared Lock (IS) – transaction intends to acquire shared row locks. Intention Exclusive Lock (IX) – transaction intends to acquire exclusive row locks.

3.1.1.1 Table Read (S) and Write (X) Locks

MyISAM uses table locks for both reads and writes. InnoDB rarely uses explicit table S/X locks; they appear when executing DDL statements such as ALTER TABLE, DROP TABLE, etc., and are implemented via metadata locks.

In SERIALIZABLE isolation, every SELECT acquires a read lock.

3.1.1.2 Intention Locks

Only InnoDB supports intention locks. Before acquiring a row lock, a transaction must first obtain the corresponding intention lock on the table (IS for shared row locks, IX for exclusive row locks). This mechanism allows multiple transactions to acquire row locks concurrently without conflicting at the table level.

3.1.1.3 Auto‑Increment Locks

InnoDB uses a special table‑level AUTO‑INC lock when inserting rows into a table with an AUTO_INCREMENT column. The lock ensures that generated values are unique and sequential within a statement. The lock mode can be controlled by the innodb_autoinc_lock_mode variable (0 = traditional, 1 = consecutive, 2 = interleaved).

3.1.1.4 Metadata Locks

MDL protects the consistency of DDL operations. A SELECT acquires an MDL read lock; an ALTER acquires an MDL write lock. Read locks are compatible with each other, while write locks are exclusive.

3.1.1.5 Table‑level Lock Compatibility

IS

IX

AUTO‑INC

S

X

IS

Compatible

Compatible

Compatible

Compatible

Incompatible

IX

Compatible

Compatible

Compatible

Incompatible

Incompatible

AUTO‑INC

Compatible

Compatible

Incompatible

Incompatible

Incompatible

S

Compatible

Incompatible

Incompatible

Compatible

Incompatible

X

Incompatible

Incompatible

Incompatible

Incompatible

Incompatible

3.1.2 Row‑level Lock Types

InnoDB supports row‑level locks, which are implemented at the storage engine layer.

Advantages: fine granularity, low contention. Disadvantages: higher overhead, possible deadlocks.

3.1.2.1 Record Locks

Record locks can be shared (S) or exclusive (X). They always lock the index record; if the table lacks a suitable index, InnoDB creates a hidden clustered index for locking.

Internal lock type name: LOCK_REC_NOT_GAP .

3.1.2.2 Gap Locks

Gap locks exist only under REPEATABLE‑READ and prevent phantom reads by locking the gaps between index records. They block INSERTs into the locked gap.

3.1.2.3 Next‑Key Locks

Next‑key locks combine a record lock with a gap lock, protecting both the existing row and the gap before it. In InnoDB they are the default lock type for range scans under REPEATABLE‑READ.

3.1.2.4 Insert Intention Locks

These are gap locks placed before an INSERT to signal the intention to insert into a specific gap. Multiple transactions can hold insert intention locks on the same gap without blocking each other.

3.1.2.5 Predicate Locks for Spatial Indexes

Spatial indexes use row‑level predicate locks to protect rows that satisfy spatial predicates.

4. MySQL Lock Analysis (Using performance_schema.data_locks)

The article uses MySQL 8.0.35’s performance_schema.data_locks view to examine lock information. For MySQL 5.7.30 the INNODB_LOCKS table is less intuitive.

To enable lock monitoring on 5.7:

# Enable InnoDB lock output
SET GLOBAL innodb_status_output_locks = ON;

# Verify
SHOW GLOBAL VARIABLES LIKE 'innodb_status_output_locks';

All subsequent examples assume the default REPEATABLE‑READ isolation level.

Scenario 1 – Unique Index Equality Query, Row Not Found

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE id = 3;

Result: Table‑level IX intention lock + primary‑key gap lock covering (-∞,12). The gap lock prevents inserts of ids < 12 until the transaction commits.

Scenario 2 – Unique Index Equality Query, Row Exists

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE id = 12;

Result: Table‑level IX lock + primary‑key record lock on id = 12 (LOCK_MODE = X,REC_NOT_GAP). Other sessions can insert rows with id = 5 without blocking.

Scenario 3 – Unique Index >= Range, Row Exists

Statement: UPDATE test_user_info SET salary = salary + 100 WHERE id >= 59;

Result: Record lock on id = 59, next‑key lock on (59,68] and on (68,+∞]. Inserts into id = 68 are blocked, but inserts with id = 46 succeed.

Scenario 4 – Unique Index >= Range, Row Not Found

Statement: UPDATE test_user_info SET salary = salary + 100 WHERE id >= 58;

Result: Table‑level IX lock, next‑key locks covering (47,59] and (59,68] and (68,+∞]. Insert of id = 48 is blocked, id = 46 is allowed.

Scenario 5 – Unique Index <= Range, Row Exists

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE id <= 35;

Result: Table‑level IX lock, next‑key locks covering (-∞,12] and (12,35]. No separate row lock on id = 35 because the next‑key lock already includes it.

Scenario 6 – Unique Index <= Range, Row Not Found

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE id < 35;

Result: Table‑level IX lock, record lock on id = 12 (gap lock on (12,35] because the upper bound is not part of the result set).

Scenario 7 – Unique Index >= and <= Range

Statement: UPDATE test_user_info SET salary = salary + 100 WHERE id >= 12 AND id <= 38;

Result: Record lock on id = 12, next‑key lock on (12,35], gap lock on (35,47).

Scenario 8 – Non‑Unique Index Equality Query, Row Not Found

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE age = 15;

Result: Table‑level IX lock, gap lock on the age index covering the range (13,47) to (25,68). Inserts that fall inside the locked gap (e.g., age = 13, id = 48) are blocked.

Scenario 9 – Non‑Unique Index Equality Query, Row Exists

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE age = 13;

Result: Next‑key lock on age index entries (13,47), record lock on the corresponding primary‑key row (id = 47), and gap lock on the surrounding range.

Scenario 10 – Non‑Unique Index >= Range, Row Exists

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE age >= 43;

Result: Multiple next‑key locks on the age index and record locks on the primary‑key rows that satisfy the condition.

Scenario 11 – Non‑Unique Index >= Range, Row Not Found

Similar to Scenario 10 but the lower bound value does not exist; the lock range starts from the next higher existing value.

Scenario 12 – Non‑Unique Index <= Range, Row Exists

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE age <= 13;

Result: Next‑key locks covering (-∞,12] and (12,13], plus record locks on the matching primary‑key rows (ids = 12, 47, 68).

Scenario 13 – Non‑Unique Index <= Range, Row Not Found

Same lock pattern as Scenario 12; the extra record lock on id = 68 appears due to the way InnoDB expands the lock range.

Scenario 14 – Non‑Unique Index Between Range

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE age >= 13 AND age <= 44;

Result: A series of next‑key locks spanning the age index intervals (13,47), (25,68), (43,59) and corresponding record locks on primary‑key rows (ids = 35, 47, 59, 68).

Scenario 15 – Non‑Indexed Column Query

Statement: UPDATE my_test.test_user_info SET salary = salary + 100 WHERE salary = 100;

Result: Full‑table scan leads to next‑key locks covering the entire primary‑key range, effectively acting like a table‑level X lock; all concurrent DML operations are blocked.

5. InnoDB Deadlock

Deadlocks cannot occur with MyISAM because it uses table locks. InnoDB deadlocks arise from row‑level lock cycles.

Four conditions for a deadlock:

Mutual exclusion – a resource can be held by only one transaction.

Hold‑and‑wait – a transaction holds a lock while waiting for another.

No preemption – locks cannot be forcibly taken away.

Circular wait – a cycle of transactions each waiting for the next.

Prevention strategies:

Acquire locks in a consistent order.

Prefer primary‑key or unique‑index updates.

5.1 Table‑level Deadlock Example

Two sessions each lock a different table via non‑indexed updates, then attempt to lock the other table, forming a deadlock. Avoid by standardising update order.

5.2 Row‑level Deadlock Example

Session A updates rows id = 1 then id = 12; Session B updates rows id = 12 then id = 1. The opposite lock order creates a cycle. Solution: sort rows by primary key before updating.

Another example shows deadlock when updating rows based on non‑unique indexes (e.g., name = 'zhangsan' vs. age = 13). The lock order differs because the index scans return rows in different primary‑key orders.

Deadlock information can be obtained with SHOW ENGINE INNODB STATUS; .

5.3 Metadata Lock (DDL) Scenarios

MySQL supports three DDL algorithms: COPY, INPLACE, and (since 8.0) INSTANT. COPY always blocks DML; INPLACE may be online but still acquires metadata locks.

During an ALTER that requires a metadata write lock, any ongoing DML holding a metadata read lock will block the DDL. New DML arriving while the DDL is waiting will also be blocked because read and write metadata locks are mutually exclusive.

5.3.1 Adding an Index

Session A runs a SELECT (holds MDL read lock). Session B attempts ALTER TABLE … ADD INDEX … (needs MDL write lock) and blocks. Session C’s SELECT also blocks until Session A commits. Once Session A commits, Session C proceeds, but Session B still waits for the write lock.

5.3.2 Adding a Column

Similar behavior: any DML holding a metadata read lock blocks the ALTER, and the ALTER blocks subsequent DML. After the original DML commits, the ALTER and all pending DML resume.

5.4 pt‑online‑schema‑change

The Percona Toolkit tool pt-online-schema-change performs online schema changes by creating a new table, applying the alteration, copying data while synchronising inserts/updates/deletes via triggers, then swapping the tables atomically. This avoids long‑running metadata locks and reduces impact on production workloads.

6. Recommendations

Operations on non‑unique indexes often acquire gap or next‑key locks, which can lock many rows and increase deadlock risk. Prefer updates that use primary‑key or unique‑index lookups. When updating via a secondary index, first fetch the primary keys, then issue the update based on those keys.

Schedule DDL (ADD INDEX, ADD COLUMN, etc.) during low‑traffic periods or use tools like pt-online-schema-change to minimise blocking.

7. References

MySQL 8.0 Documentation – InnoDB Locking: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks

MySQL 8.0 Optimisation – Lock Granularity: https://blog.csdn.net/ChinaYangJu/article/details/127939056

Deep Dive into MySQL Next‑Key Locks: https://blog.csdn.net/Bb15070047748/article/details/131766686

Row, Next‑Key, and Gap Locks under REPEATABLE‑READ: https://blog.csdn.net/qq_35572020/article/details/127629464

pt‑online‑schema‑change Overview: https://blog.csdn.net/weixin_44352521/article/details/108558026