常规错误: 1205锁定等待超时；请尝试重新启动事务

Question

我正在开发一个与Laravel + MySQL在线考试评估的应用程序。许多用户同时提交他们的响应(在几分钟内)，因此数据库服务器上有很重的负载。

在许多并发提交的过程中，我得到了几个General error: 1205 Lock wait timeout exceeded; try restarting transaction。

我启用了慢查询日志，在我的日志中看到以下条目

update `form_responses` set `photo_url` = 'https://example.com/ae252b371effc7cb11dbcbbb18602026.jpg', `form_responses`.`updated_at` = '2020-09-26 11:39:17' where `id` = 32407;

据我所知，这只是一个简单的行更新，其中包含一个被查询的主列(这意味着索引应该可以工作)。

但我不能理解的是--为什么Innodb不使用行级锁并在这一特定行上提供锁。是否有一些特定的Innodb值我应该增加这个值，以防万一？

Answer 1

InnoDB 是使用行级锁定的。

问题是，应用程序或对象关系管理以错误的方式使用它，没有为MySQL提供关于要做什么的足够信息，从而导致死锁。

最简单的--我得说，不是最有效的！--克服这个问题的方法可能是，如果您发现所有的更新都发生在同一小组方法中，那么可以求助于咨询锁定。

例如，你会发现：

LATEST DETECTED DEADLOCK
------------------------
200915 11:31:03
*** (1) TRANSACTION:
TRANSACTION 22B8A76B9, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 11161737, OS thread handle 0x7fd06d708700, query id ... UPDATE table SET ... WHERE Dat_UsrId = '11254' && Dat_CreUte='...' && Dat_Tip = 'C'

*** (2) TRANSACTION:
TRANSACTION 22B8A76B4, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
2616 lock struct(s), heap size 244152, 7262 row lock(s)
MySQL thread id 11161676, OS thread handle 0x7fd06ca97700, query id ... UPDATE table SET ... WHERE ...

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 866959 page no 46 n bits 1616 index `NewIndex2` of table `schema`.`table` trx id 22B8A76B4 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 866959 page no 68831 n bits 72 index `PRIMARY` of table `schema`.`table` trx id 22B8A76B4 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)
------------

注意，这里的逻辑错误出现在事务2的查询中。这是一个非常强烈的提示：

2616 lock struct(s), heap size 244152, 7262 row lock(s)

...the WHERE需要锁定7262行(!)，从应用程序逻辑的角度来看，这是完全没有意义的(有关“间隔”的解释，请参阅this )。

要做到这一点，最有效的方法是让所有涉及的方法都输入一个显式事务：

SET autocommit = 0;
UPDATE form_responses ... WHERE id = ...;
COMMIT WORK;