在MySQL InnoDB中，以下查询获得哪些锁？

Question

我正在尝试调查我的应用程序中的死锁问题。我的桌子看起来像这样。

CREATE TABLE `requests` (
  `req_id` bigint(20) NOT NULL auto_increment,
  `status` varchar(255) default NULL,
  `process_id` varchar(200) default NULL,
  PRIMARY KEY  (`req_id`),
  KEY `status_idx` USING BTREE (`status`),
  KEY `pk_idx_requests` USING BTREE (`req_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 服务(多线程)在此表上发出insert语句。
• 多个客户端按照两个单独事务的顺序执行以下查询。 update请求设置process_id=‘’+ hostName +‘其中状态=’接收‘，process_id为空顺序，由req_id asc限制100“ 从process_id=‘’+ hostName +‘where status=’Received‘的请求中选择*； 更新请求设置状态=“处理”，其中req_id='xyz‘

第三个查询中的Req_id是从第二个查询中检索的req in列表。

但在客户端，我们有时会看到以下异常。

Deadlock found when trying to get lock; try restarting transaction
org.hibernate.exception.LockAcquisitionException: could not execute native bulk manipulation query

上面的查询会导致死锁吗?如果是，我们如何解决？此外，是否有办法在当地复制这个问题？

这是“显示innodb状态”的输出

LATEST DETECTED DEADLOCK
------------------------
120507  6:03:21
*** (1) TRANSACTION:
TRANSACTION 115627, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 25 row lock(s)
MySQL thread id 432399, OS thread handle 0x419e4940, query id 4111695 * * * Searching rows for update
update requests set process_id='**' where status='Received' and process_id is null order by req_id asc limit 100
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 3797 n bits 136 index `PRIMARY` of table `db`.`requests` trx id 115627 lock_mode X locks rec but not gap waiting
Record lock, heap no 67 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 115626, ACTIVE 1 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 432403, OS thread handle 0x41c19940, query id 4111694 * * *  Updating
update requests set status='Processing', process_id='**' where req_id=3026296
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 3797 n bits 136 index `PRIMARY` of table `db`.`requests` trx id 115626 lock_mode X locks rec but not gap
Record lock, heap no 67 PHYSICAL RECORD: n_fields 27; compact format; info bits 0

Answer 1

一些背景

MySQL在第一次访问记录时会对UPDATE语句使用写锁。它不提升锁从读到写。It 基于当前索引的锁。

在UPDATE语句中，MySQL最有可能使用status列上的索引，因此MySQL锁定了status =‘接收’的所有记录。

请注意，无论何时锁定不止一个唯一记录(使用唯一索引，例如主键)，都会锁定一个空白(或范围)。

对单个记录的更新仍然需要一个下一个键锁，这意味着它锁定了所选记录和索引中的下一个记录。

同一索引上的两个更新(都带有下一个键)锁不会冲突(它们总是按照相同的顺序锁定)。但是，由于您的范围锁定是针对辅助索引的，因此它可能会死锁。

，这里是正在发生的场景

假设您有req_ids 1和2的两个记录。

您的第一个事务对状态索引进行更新，需要同时锁定记录1和2，但它不能保证与主键的顺序相同，因此它锁定记录2，并即将锁定记录1。

第二个事务锁在req_id索引上，需要更新记录1，它会立即锁定记录1，但也需要在记录2上执行下一个键锁。

这两项交易现在陷入僵局。事务1需要锁定记录1，事务2需要锁定记录2。

解决方案

为了避免在您的情况下死锁，您可以使用LOCK TABLES显式地锁定整个表，或者在事务失败时只重试一次。MySQL将检测死锁，其中一个事务将被回滚。

MySQL确实为帮助您处理死锁提供了一些说明。

似乎还应该删除冗余密钥pk_idx_requests，因为主键已经包含了该列。

Answer 2

是的，这些查询可能导致死锁。事实上，正如在MySQL文档中提到的，即使在只插入或删除单个行的事务中，您也可以获得死锁。请参阅如何使用死锁

您可以尝试索引process_id，以尝试加速查询/更新。