如何避免这种情况下的死锁

Question

我有一个innoDB表，多个连接可能会在其中插入数据，而每10秒运行一次的MySql事件会删除一些以前插入的记录。

但我遇到了僵局。我怎样才能避开它们？

此代码负责将记录逐行插入表中。

sql = "INSERT INTO ex_result (Result_ID, ParentResult_ID, StepNumber, Name, Type, DB_ID, Session_ID, Status, TotalTime, FunctionCall, FunctionResult, ToolTime, PluginName, Screenshot_FID, IsNegative, ContinueOnError, WantSnapshot, Message, ResultCode, Output, InputArguments) VALUES (@Result_ID, @ParentResult_ID, @StepNumber, @Name, @Type, @DB_ID, @Session_ID, @Status, @TotalTime, @FunctionCall, @FunctionResult, @ToolTime, @PluginName, @Screenshot_FID, @IsNegative, @ContinueOnError, @WantSnapshot, @Message, @ResultCode, @Output, @InputArguments)"

事件的代码为：

DELIMITER //
CREATE EVENT EVENT_RESULT_CLEANER
ON SCHEDULE
EVERY 10 second
COMMENT 'Clean all delted results'
DO
BEGIN
DROP TEMPORARY TABLE IF EXISTS `Temp_Result_Purge`;
CREATE TEMPORARY TABLE `Temp_Result_Purge` (
`Result_ID` VARCHAR(63) NOT NULL,
PRIMARY KEY (`Result_ID`))
ENGINE = MEMORY;
INSERT INTO Temp_Result_Purge(Result_ID)
(
    SELECT t1.result_id
    FROM ex_result AS t1
    INNER JOIN ex_session as t2
    ON t1.session_id=t2.session_id
    WHERE NOT EXISTS(SELECT t3.result_id FROM ex_result as t3 WHERE t3.parentresult_id=t1.result_id)
    AND t2.DeletedOn IS NOT NULL
    LIMIT 2000
);
DELETE t1 FROM `ex_result` AS t1 INNER JOIN
`Temp_Result_Purge` AS t2 ON t1.Result_ID=t2.Result_ID;
DROP TEMPORARY TABLE `Temp_Result_Purge`;
END//
DELIMITER ;

Answer 1

首先我有一些讨厌的事情要说，然后我会找到一个可能的解决方案。

"不要排队，只要排队就行。"——MySQL并不是一个好的排队引擎。

添加BEGIN。。。COMMIT（如前所述）。开始。。。COMMIT也需要围绕其他代码。

请添加代码以测试死锁。然后重播开始。。。提交。不能避免所有死锁，所以要为它们做好计划。

将LIMIT降低到10。然后让清洗机持续循环，而不是每10秒醒来一次。（如果你要说"这会让事情变得更糟"，那就继续读下去；我会给你一个可能效果更好的变体。）

使用LEFT JOIN ... WHERE ... IS NULL而不是NOT EXISTS ( SELECT ... )

不要一遍又一遍地重新制作桌子；仅CCD_ 3。或者，更好的是，直接删除，而不经过tmp表。然而，这引出了另一个问题。。。

查询必须经过多少行才能找到LIMIT行？请记住，SELECT正在干扰INSERT。如果它通常必须扫描一百万行才能找到2000行来删除，那么我们需要想办法让这些行更容易找到。为此，我们需要更多关于您的应用程序和表格大小的详细信息。或者思考一下。。。

礼貌地扫描一百万行只找到几行的一种技术是，通常使用PRIMARY KEY一次遍历表1000行。注意：这是表中的1000行，而不是可以删除的1000行。在每个1000之后，删除您找到的（如果有的话），然后转到下一个1000。当你走到桌子的尽头时，重新开始。有关如何进行分块的详细信息，请访问此处：http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Answer 2

至少，您需要在事件代码中启动一个事务。

这不是一开始就简单地完成的。。。结束，请参阅http://dev.mysql.com/doc/refman/5.6/en/commit.html

在所有存储的程序（存储过程和功能，触发器和事件），解析器将BEGIN[WORK]视为开始。。。END块。在此开始交易使用START TRANSACTION替换上下文。

此外，如果你每10秒运行一次，那就考虑一下改变你的体系结构。关系数据库不适合使用寿命短的数据。另一种选择可能是消息队列。

Answer 3

在我看来，最好的解决方案是使用软删除

只需将已删除对象的状态设置为已删除。如果记录量真的很大，而且你根本不想存储历史数据，你可以每晚或每周进行一次有计划的数据库清除

缺点之一是必须通过添加一个新条件