mysql - MySQL InnoDB 中的异步事务？

Question

我想实现多个数据库事务的并行处理，这些事务在短时间内仅锁定几行。例如，每次用户打开页面时，我们都会执行此查询:

START TRANSACTION;
SELECT * FROM table_1 WHERE worktime < UNIX_TIMESTAMP() FOR UPDATE;
...WORK...
...UPDATE...
COMMIT;

在多用户环境中，这种行锁定会在每次执行 select 语句时导致死锁。目前，我将使用第二个表来存储锁定的 ID 来解决该问题:

START TRANSACTION;
LOCK TABLE table_1 WRITE, table_locks WRITE;
SELECT id FROM table_1 WHERE worktime < UNIX_TIMESTAMP() AND id NOT IN table_locks;
...insert locked Ids into Table "table_locks"...
...this prevents other calls to read from this table...
UNLOCK TABLES;
COMMIT;

...Perform calculations and Updates...

DELETE FROM table_locks WHERE id = ...

此方法的问题是，如果通过将行的 ID 存储在 table_locks 表中“锁定”行后出现问题，则该行将永远不会再更新。当然，我可以设置超时在一段时间后自动释放此类锁，但这对我来说似乎做得不正确。但有没有可能像:

SELECT * FROM table_1 WHERE worktime < UNIX_TIMESTAMP() AND NOT LOCKED BY OTHER TRANSACTION FOR UPDATE

？

Answer 1

您可以标记 session 要完成的行:

UPDATE table_1 
SET marked_by_connection_id = CONNECTION_ID(), 
    marked_time = NOW()
WHERE worktime < UNIX_TIMESTAMP() AND marked_by_connection_id IS NULL;

然后您可以随意处理具有您的连接 ID 的任何行，因为知道另一个 session 不会尝试声明它们:

SELECT * FROM table_1 WHERE marked_by_connection_id = CONNECTION_ID();
. . .

不需要锁定或非自动提交事务。

在 session 结束时，取消标记您已标记的所有行:

UPDATE table_1 SET marked_by_connection_id = NULL 
WHERE marked_by_connection_id = CONNECTION_ID();

或者您的应用程序可以在处理各个行时取消标记它们。

但也许您的 session 在取消标记这些行之前就终止了。因此有些行被标记，但从未被处理。运行一个 cron 作业来清除此类废弃的标记行，从而允许其他工作人员重新处理它们，尽管有点晚了。

UPDATE table_1 SET marked_by_connection_id = NULL 
WHERE marked_time < NOW() - INTERVAL 30 MINUTE;