Mysql锁等待超时错误

Question

我在我的应用程序中有这样一个场景:事务下的简单INSERT语句阻塞MySQL中不同行上的DELETE语句，最后，DELETE语句会话超时与锁定超时错误。

我试图用下面的简单情景来解释这种情况。注意，在列中添加索引会有所帮助，但是，如果删除的WHERE子句中有2列，则仍然会看到锁等待超时。另外，请注意，在我启动事务之后，我需要进行很少的其他处理，因此事务需要很少分钟才能提交或回滚。很难相信，即使我删除与我试图插入的不同的记录，MySQL也会阻止它。

第1场会议：-

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;

 +-----------------------+----------------+------------------------+
 | @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
 +-----------------------+----------------+------------------------+
 | READ-COMMITTED        | READ-COMMITTED | READ-COMMITTED         |
 +-----------------------+----------------+------------------------+ 

1 row in set (0.02 sec)` 

mysql> create table testtab(col1 int, col2 int);
Query OK, 0 rows affected (0.53 sec) 
mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec) 
mysql> INSERT INTO testtab values(1,1); 
Query OK, 1 row affected (0.00 sec) 
mysql> INSERT INTO testtab values(2,2); 
Query OK, 1 row affected (0.00 sec) 

第2场会议：-

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation; 
+-----------------------+----------------+------------------------+ 
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation | 
+-----------------------+----------------+------------------------+ 
| READ-COMMITTED        | READ-COMMITTED | READ-COMMITTED         | 
+-----------------------+----------------+------------------------+
 1 row in set (0.00 sec) 
mysql> DELETE FROM testtab where col1=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  

有什么建议吗？

Answer 1

这是InnoDB锁定工作方式的结果。引用手册的话，

锁定读取、更新或删除通常设置在SQL语句处理过程中扫描的每个索引记录上的记录锁。语句中是否存在排除行的条件并不重要。InnoDB不记得确切的位置条件，但只知道被扫描的索引范围。

同样值得注意的是

如果您没有适合您的语句的索引，并且MySQL必须扫描整个表以处理该语句，则表的每一行都会被锁定，这反过来会阻塞其他用户对该表的所有插入。创建好的索引非常重要，这样您的查询就不会不必要地扫描许多行。

因此，在您的示例中，两个insert向表中添加了两个锁定行。因为没有适当的索引，所以delete现在将扫描整个表。它必须锁定它遇到的每一行，但要做到这一点，它必须等待insert创建的锁被解除(这不是及时发生的)。

在col1上添加索引将解决这一问题，因为delete只需考虑(并锁定)使用该索引可以找到的col1 = 3行，因此它将不与其他事务插入的行重叠。

如果你很难为你的“两列哪里条件”找到一个好的索引，你应该添加更多关于它的细节，也许有一个指数适合这种情况。

当然，这并不能防止每一种情况(例如，如果您实际上想要修改同一行)，因此通常最好保持您的事务尽可能短，准备等待那么长的时间和/或准备在需要时重复事务。

顺便说一句，这并不是对InnoDB锁的完整描述，只是解释您的情况的部分。