MySQL InnoDB死锁对于两个简单的插入查询 MySQL InnoDB在处理并发事务时可能会出现死锁情况。尤其是在涉及到多个表的复杂查询或频繁的数据更新时，死锁问题更容易发生。死锁指的是两个或多个事务相互等待对方释放锁资源，从而导致无法进行进一步的操作。这种情况下，MySQL将会选择其中一个事务作为牺牲品，回滚该事务并释放相关资源，从而解除死锁。对于两个简单的插入查询，死锁问题通常不太常见。但如果在同一个事务中同时执行多个插入操作，并且涉及到共享资源（如同一张表的不同行），那么就有可能发生死锁。为了避免死锁问题，可以采取以下几种措施： 1. 合理规划事务的执行顺序，尽量减少并发操作。 2. 使用合适的索引，以提高查询效率，减少锁竞争。 3. 设置适当的超时时间和重试机制，以便在发生死锁时能够及时处理。总之，虽然MySQL InnoDB在处理并发事务时可能会出现死锁问题，但通过合理的规划和优化，我们可以降低死锁发生的概率，并能够及时处理和解决这些问题。

Question

MySQL InnoDB死锁对于两个简单的插入查询 MySQL InnoDB在处理并发事务时可能会出现死锁情况。尤其是在涉及到多个表的复杂查询或频繁的数据更新时，死锁问题更容易发生。死锁指的是两个或多个事务相互等待对方释放锁资源，从而导致无法进行进一步的操作。这种情况下，MySQL将会选择其中一个事务作为牺牲品，回滚该事务并释放相关资源，从而解除死锁。对于两个简单的插入查询，死锁问题通常不太常见。但如果在同一个事务中同时执行多个插入操作，并且涉及到共享资源（如同一张表的不同行），那么就有可能发生死锁。为了避免死锁问题，可以采取以下几种措施： 1. 合理规划事务的执行顺序，尽量减少并发操作。 2. 使用合适的索引，以提高查询效率，减少锁竞争。 3. 设置适当的超时时间和重试机制，以便在发生死锁时能够及时处理。总之，虽然MySQL InnoDB在处理并发事务时可能会出现死锁问题，但通过合理的规划和优化，我们可以降低死锁发生的概率，并能够及时处理和解决这些问题。

14

我对这两个插入查询遇到了死锁。

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

这是 InnoDB 状态：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-12-23 15:47:11 1f4c
*** (1) TRANSACTION:
TRANSACTION 19896526, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17988, OS thread handle 0x17bc, query id 5701353 localhost 127.0.0.1 root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,  nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 19896542, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17979, OS thread handle 0x1f4c, query id 5701360 localhost 127.0.0.1    root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,   nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of    table `db`.`playerclub` trx id 19896542 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

这个表上唯一的外键是"account_id"。有什么想法吗？编辑：这是我的PlayerClub信息：

CREATE TABLE `PlayerClub` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modifiedBy` bigint(20) DEFAULT NULL,
  `timeCreated` datetime NOT NULL,
  `account_id` bigint(20) DEFAULT NULL,
  `currentClubId` bigint(20) DEFAULT NULL,
  `endingLevelPosition` int(11) NOT NULL,
  `nextClubId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  KEY `FK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  CONSTRAINT `FK_cagoa3q409gsukj51ltiokjoh` FOREIGN KEY (`account_id`) REFERENCES   `PlayerAccount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

- Urbanleg

在陷入僵局之前，你在每次交易中还做了什么其他的事情？ - Michael - sqlbot

如果您在每次插入之后提交并尝试呢？请告诉我们。 - Nawaz Sohail

显示创建表PlayerClub。通常与索引相关。 - Jehad Keriaki

1个回答

- RolandoMySQLDBA · Accepted Answer

以下是事实

这里是两个插入语句

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

这是来自你的SHOW ENGINE INNODB STATUS\G的两行内容。

RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X

观察

你正在使用两个不同的账户ID进行插入操作：561和563。

它们是唯一的，不应该有问题，对吗？错了！！！

由于InnoDB的聚集索引，仍然可能发生死锁。为什么呢？

回顾一下你的两个插入操作。id上的PRIMARY KEY没有指定。它必须是自动生成的。除了主键之外的任何键（唯一或非唯一）都会附加到主键上。

请注意MySQL文档中关于次要索引和主键如何交织在一起的说明：

除了聚集索引之外的所有索引都被称为次要索引。在InnoDB中，每个次要索引中的记录都包含行的主键列，以及为次要索引指定的列。InnoDB使用这个主键值来在聚集索引中搜索行。

如果主键很长，次要索引会占用更多的空间，所以拥有一个短的主键是有优势的。

尽管您正在插入帐户ID 561和563，但底层是将 561-（id）和 563-（id）插入 UK_cagoa3q409gsukj51ltiokjoh 索引。由于辅助索引必须等待自动生成“ id”列，因此 PRIMARY KEY 成为瓶颈。建议：您有一个具有两个候选键的表 - 在 id 上的 PRIMARY KEY - 在 UK_cagoa3q409gsukj51ltiokjoh 上的 UNIQUE KEY 由于两者都是BIGINT，因此可以通过摆脱 id 来提高性能并拥有更小的 PlayerClub 表，同时仍然保持唯一性，避免了这种死锁情况。