Analyzing MySQL InnoDB Deadlock with RC and RR Locks

The author, Gao Peng, presents a step‑by‑step analysis of a MySQL InnoDB deadlock that can occur under both RC and RR isolation levels.

First, a test database and table are created to reproduce the issue:

CREATE database deadlock_test;</code>
<code>use deadlock_test;</code>
<code>CREATE TABLE `push_token` (</code>
<code>  `id` bigint(20) NOT NULL AUTO_INCREMENT,</code>
<code>  `token` varchar(128) NOT NULL COMMENT 'push token',</code>
<code>  `app_id` varchar(128) DEFAULT NULL COMMENT 'appid',</code>
<code>  `deleted` tinyint(1) NOT NULL COMMENT '是否已删除 0：否 1：是',</code>
<code>  PRIMARY KEY (`id`),</code>
<code>  UNIQUE KEY `uk_token_appid` (`token`,`app_id`)</code>
<code>) ENGINE=InnoDB AUTO_INCREMENT=3384 DEFAULT CHARSET=utf8 COMMENT='pushtoken表';</code>
<code>insert into push_token (id, token, app_id, deleted) values(1,"token1",1,0);

To capture detailed lock information, the author uses a custom Percona‑Server build with the variables gaopeng_mdl_detail=OFF and innodb_gaopeng_row_lock_detail=ON enabled, which records most InnoDB lock records to the error log.

Step 1 (TRX ID 367661) : The transaction

BEGIN; UPDATE push_token SET deleted = 1 WHERE token='token1' AND app_id='1';

acquires X|LOCK_NOT_GAP locks on both the primary key and the unique index, as shown in the log excerpts.

Step 2 (TRX ID 367662) : The second transaction executes BEGIN; DELETE FROM push_token WHERE id IN (1);. It attempts to acquire an X|LOCK_NOT_GAP lock on the primary key but is blocked by the lock held by Step 1, which is evident from the “Trx(367662) is blocked!!!!!” log entry.

Step 3 (TRX ID 367663) : A third transaction runs

BEGIN; UPDATE push_token SET deleted = 1 WHERE token='token1' AND app_id='1';

. It acquires the X|LOCK_NOT_GAP lock on the unique index but is blocked when trying to lock the primary key, as indicated by its own “Trx(367663) is blocked!!!!!” message.

After Step 1 commits, the locks held by the first transaction are released, allowing Step 2 to obtain the primary‑key lock and Step 3 to obtain the unique‑key lock. However, each transaction then attempts to acquire the lock held by the other, creating a circular wait: Step 2 waits for the unique‑key lock, and Step 3 waits for the primary‑key lock, resulting in a deadlock.

The detailed InnoDB logs and accompanying diagrams illustrate how the X|LOCK_NOT_GAP locks on both the primary key and the unique key interact to produce the deadlock. The article concludes that by examining the lock logs, the deadlock can be identified and understood quickly, and it recommends Gao Peng’s book “深入理解MySQL主从原理 32讲” for deeper study of MySQL replication and locking.