Analysis of MySQL InnoDB Locking Mechanisms and Deadlock Scenarios

The purpose of the experiment is to analyze the locking behavior of MySQL (InnoDB) operations, deconstruct deadlock scenarios, and assist in diagnosing the causes of deadlocks for more efficient resolution.

Theory : Transactions are the unit of SQL execution and require locks to guarantee ACID properties. InnoDB primarily uses row‑level locks (record locks) that are placed on index entries. Locks are classified as shared (S) or exclusive (X), with intention locks (IS, IX) representing the intent to acquire row‑level locks. Indexes include primary (clustered) and secondary (non‑clustered) indexes, each influencing lock granularity.

Experimental Environment : MySQL 5.6.16‑64.2 Percona Server, isolation level REPEATABLE READ, storage engine InnoDB. The test table is created as follows:

CREATE TABLE `test_lock` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `key_uniq` varchar(100) NOT NULL DEFAULT '',
  `name_index` varchar(100) NOT NULL DEFAULT '',
  `code_index` varchar(100) NOT NULL DEFAULT '',
  `no_index` varchar(100) NOT NULL DEFAULT '',
  `int_index` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key_uniq` (`key_uniq`),
  KEY `idx_code` (`code_index`),
  KEY `idx_name` (`name_index`),
  KEY `idx_int` (`int_index`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4;

Test data are inserted with a single INSERT INTO `test_lock` VALUES ... statement.

Methodology : Within a transaction, a sequence of SQL statements is executed, then the transaction ID is obtained via

SELECT trx_id FROM information_schema.INNODB_TRX WHERE trx_mysql_thread_id = CONNECTION_ID()\G

, the lock state is inspected with SHOW ENGINE INNODB STATUS\G, and finally the transaction is rolled back to avoid data changes. The steps are summarized in a table of hints (begin, execute SQL, check trx_id, show status, rollback).

Experiments and Findings :

1. Consistent non‑locking read : Simple SELECT statements do not acquire locks, relying on MVCC to read snapshot data.

2. Consistent locking read : SELECT ... LOCK IN SHARE MODE acquires shared (S) locks, while SELECT ... FOR UPDATE acquires exclusive (X) locks.

3. Update by primary key : An UPDATE on the primary key generates an intention‑exclusive (IX) lock on the table and a row lock on the primary index entry.

4. Update by unique key : In addition to the primary‑key row lock, a lock is placed on the secondary unique index entry, demonstrating that unique secondary keys also incur row locks.

5. Update by non‑existent primary key : A gap lock ( lock_mode X locks gap before rec) is created on the interval surrounding the missing key to prevent phantom inserts.

6. Update by secondary key : Next‑key locks are applied to both the primary index entries that match the secondary key and the secondary index itself, locking a range such as (beijing_cit, kkk).

7. Update by non‑indexed attribute : The statement degrades to a full‑table scan on the clustered index, resulting in a next‑key lock on every scanned row (effectively a table‑level lock).

8. Delete : Deleting a row locks the corresponding primary index entry similarly to an update.

9. Insert : Inserting a row acquires an intention‑exclusive lock on the table and a row lock on the newly inserted record; gap locks may appear when concurrent transactions attempt conflicting inserts.

10. Attribute‑specific updates : Updating a column that has an index also acquires a row lock on the index entry.

These experiments illustrate how lock granularity depends on the presence of indexes, the type of DML operation, and whether the target rows exist.

Conclusion : SHOW ENGINE INNODB STATUS\G is an essential tool for diagnosing lock contention and deadlocks. Practical experimentation, combined with a solid understanding of transaction isolation and lock compatibility, enables developers to design queries that minimize blocking and avoid complex deadlock scenarios.

References : MySQL Technical Internals – InnoDB Storage Engine; High Performance MySQL; Deep Understanding of MySQL Core Technologies; MySQL Official Wiki; Percona Wiki.