MYSQL:超过了锁等待超时；尝试重新启动事务

Question

事实

我有一个具有tableA

• I表名的
• 数据库，它同时运行多个aws批处理，其中每个批处理进程与tableA通信：
  • 首先向表
  • 添加多个行，然后删除多行到table

如果我运行一个批处理进程，则每个批处理都会处理它自己的不同行集--

• sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1205, 'Lock wait timeout exceeded; try restarting transaction')

• --没有出现问题。

• 它与aws批处理无关，因为当我尝试在本地执行时也会出现同样的问题。

其他信息

• SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation, @@session.transaction_isolation; ==> repeatable-read, repeatable-read, repeatable-read
• show variables like 'innodb_lock_wait_timeout' ==> 50

问题

我可以看到一些解决方案，建议将innodb_lock_wait_timeout设置为更高的值，这可能会消除错误。但我的理解是，如果我将innodb_lock_wait_timeout设置为更高的值，则会发生的情况是，每个事务只需等待其他事务完成。这意味着这些进程不会并行运行，因为每个进程都将等待对方。

我想要的是在不等待目前正在发生的其他事务(插入或删除)的情况下发生这些过程。

有什么建议吗？

Answer 1

并行运行多个批处理负载过程是很困难的。

加快批处理中使用的删除查询。对它们运行EXPLAIN以确保它们具有所需的索引，然后添加所需的索引。

在每个会话中运行批处理之前，尝试使用SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;。如果每个批处理处理自己的一组不同的行，这可能(或不)允许更多的并行性。

尝试缩小批处理的大小(行数)。使用事务批处理的性能原因是避免对每一行执行代价高昂的提交。与对10000行批处理一样，100行批处理可以获得大部分性能好处。

尝试将每个传入批处理加载到事务外的临时表中。然后使用事务中的临时表来进行更新。类似这样的代码，显然比您需要的要简单得多。

 CREATE TEMPORARY TABLE batchrows;
 INSERT INTO batchrows (col,col,col) VALUES(a,b,c);
 INSERT INTO batchrows (col,col,col) VALUES(d,e,f);
 INSERT INTO batchrows (col,col,col) VALUES(g,h,i);
 BEGIN TRANSACTION;
     INSERT INTO maintable SELECT * FROM batchrows;
     DELETE FROM maintable WHERE col IN (SELECT whatever FROM batchrows); /* ??? */
 COMMIT;
 DROP TEMPORARY TABLE batchrows;

这么做的目的是什么？缩短事务锁的持有时间。

最后:不要尝试并行进行批处理加载。有时，数据的完整性只要求您一个接一个地处理批处理。实际上，这就是您的系统中现在发生的情况:每一批必须等待上一批完成。

Answer 2

一般来说，可重复读取对于生产来说不是一个很好的默认。它锁上了它接触过的所有行。这将创建许多不必要的锁。更改为已提交的读取将减少significantly.锁

在进行其他调优之前，我建议您启用

1. 日志来查看锁是什么。

集innodb_status_output_locks = on集innodb_status_output = on

如果这个锁能被解除，那将是一个巨大的性能提升。

1. 我不建议增加innodb_lock_wait_timeout。如果锁持有时间超过50秒，批处理作业将不会很快。

在我之前经历过的更糟糕的场景中，如果数据库被其他应用程序共享，比如app serer和长时间等待超时可能占用所有连接。这将导致应用程序服务器无法处理新请求。