是否有一种高性能的原子方法可以从表中"dequeue"并查看它是否为空？

Question

我有一个表来存储从多个进程访问的SQS队列的状态。它现在的工作方式是将队列中的所有消息都添加到表中。每次队列中的消息出队时，它也会从表中删除。当表/队列变为空时，需要运行一些终结操作。目前的实施方式是：

-- dequeue (2 statements so that we can use the primary index for the delete - was seeing a lot of deadlocking otherwise)
BEGIN;
SELECT id FROM messages WHERE messageKey = 'message_key' LIMIT 1 FOR UPDATE;
DELETE FROM messages WHERE id = 'auto_inc_id';
COMMIT;
-- check if it was the last one (shared lock so we know that nobody is currently deleting a message - select count(*) was also causing deadlocks due to locking so many rows)
SELECT * FROM messages LIMIT 1 LOCK IN SHARE MODE;
-- perform finalization if no records returned

不幸的是，这些语句之间似乎引起了一些争论，这导致了应用程序的缓慢——是否有一种明显的方法可以做到这一点，它仍然是原子的，但需要更少的语句或事务？

这张表很简单，但如果有人好奇，我会把它包括在内

CREATE TABLE IF NOT EXISTS `messages` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`messageKey` VARCHAR(256) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Answer 1

BEGIN;
SELECT @id := id FROM ... FOR UPDATE;
IF (@id IS NOT NULL) THEN
DELETE ... WHERE id = @id;
END IF;
COMMIT;

然后继续使用@id。

逻辑中有一个缺陷：如果服务器在您完成"使用"@id之前崩溃，则无法恢复。

如果你想讨论修复这个缺陷，首先回答这个问题：如果你"使用"@id两次，会造成伤害吗？