Innodb事务还是表锁？

Question

我有一个简单的表格，它是一个电子邮件队列。

CREATE TABLE `emails_queue_batch` (
  `eq_log_id` int(11) NOT NULL DEFAULT '0',
  `eq_to` varchar(120) CHARACTER SET utf8 DEFAULT NULL,
  `eq_bcc` varchar(80) CHARACTER SET utf8 DEFAULT '',
  `eq_from` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `eq_title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `eq_headers` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `eq_content` longtext CHARACTER SET utf8,
  `eq_sid` int(11) DEFAULT '0',
  `eq_type` int(11) DEFAULT '0' COMMENT 'email type',
  `eq_esp` int(11) DEFAULT '0',
  PRIMARY KEY (`eq_log_id`),
  KEY `email` (`eq_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

几个线程每次读取50行并删除这些行。

为了避免重复读取相同的行，我使用了以下方法：

    $db->query(" LOCK TABLE $table WRITE ");

    $query= "SELECT * FROM $table LIMIT  ".CHUNK_SIZE. " " ; 

    $emails2send=$db->get_results ($query);

    if (!empty ($emails2send)){

        // DELETE EMAIL 
        $eq_log_ids = array();
        foreach ($emails2send as $email) $eq_log_ids[]= $email->eq_log_id ;

        $query= "DELETE FROM $table WHERE eq_log_id IN ( ".implode(',', $eq_log_ids)." ) ";
        $db->query ($query);

        $db->query (" UNLOCK TABLES "); // unlock the table so other sessions can read next rows
        ........ code processing the read rows here .............
    } else { // if !empty emails2send
        // $emails2send is empty 
        $db->query (" UNLOCK TABLES; ");    
        $stop_running=true; // stop running
    }

同时有另外的线程在写入表格。 由于我不理解的原因，这种配置会导致读取和写入时出现死锁。

我的问题是： 使用锁定来确保我只读取每一行一次（并将其删除）是否是正确的解决方案？

还是应该将其作为事务处理，如果是的话，应该使用哪种类型的事务？我对事务处理没有经验。

- Nir

---

正确的解决方案是使用真正的消息队列（例如：RabbitMQ或ActiveMQ）。将事务性数据库用作事实上的队列始终会导致锁争用和死锁，除非您限制自己仅从数据库队列中读取一个线程。 - Bill Karwin

谢谢，我正在处理。 消息队列的一个问题是，如果出现问题，例如需要取消邮件批处理，则无法选择性地从队列中删除。 - Nir

在我以前的一份工作中，我们将任务保存在数据库中，但是“调度程序”进程会在任务准备好运行时提取它们，并将它们发布到 MQ 中。只要调度程序是单线程的，它就可以避免你所问的问题。然后，MQ 可以被多个工作线程读取。这样可以达到一个平衡——你可以在将任务发布到 MQ 之前在数据库中删除或修改任务。 - Bill Karwin

Answer 1

如果出现死锁，使用事务可能会更好。

首先，请尝试将批处理大小从50个减少到1个，看看情况是否有所改善。这很容易做到。如果您使用事务，这也是您想要做的。

其次，请尝试这种查询序列。

  START TRANSACTION;
  SELECT @id := table.eq_log_id, table.* FROM table LIMIT 1 FOR UPDATE;
  /* handle the item here */
  DELETE FROM table WHERE eq_log_id = @id;
  COMMIT;

只有在eq_log_id是唯一（或主键）时才有效。在您的php程序中循环运行此操作，直到SELECT操作不返回任何行。然后休眠一段时间，再次尝试。

更好的方法是在您的表中添加一个名为processed的时间戳，其默认值为空。然后，您可以更新它们的时间戳而不是删除行。这将为您提供故障排除的方法。

START TRANSACTION;
SELECT @id:=eq_log_id, * FROM table WHERE processed IS NULL LIMIT 1 FOR UPDATE;
/* handle the item here */
UPDATE table SET processed=NOW() WHERE eq_log_id = @id;
COMMIT;

您可以运行一个夜间批处理程序来清除所有过期的记录，像这样：

DELETE FROM table WHERE processed < CURDATE() - INTERVAL 1 DAY;

我提议这样做是因为在生产环境中，查看已发送消息的时间历史记录非常有帮助。

- O. Jones

Answer 2

方案A：

假设您可以在不到2秒的时间内处理N行数据。您的N值为50，这可能太大了。

BEGIN;
SELECT ... LIMIT 50  FOR UPDATE;
... process ...
... gather a list of ids to delete ...
DELETE ... WHERE id IN (...)
COMMIT;

抓取的越多，速度越快，但死锁的可能性也越大。当发生死锁时，只需重新启动事务即可。还要跟踪死锁发生的频率，以调整“50”。

备选方案B：

当处理一个项目的时间对于事务来说太长时，这将非常有用。我认为2秒可能是“太长”了。

Grab a row to process:
with autocommit=ON ...
UPDATE ... SET who_is_processing = $me,
               when_grabbed = NOW()
               id = LAST_INSERT_ID(id),
           WHERE when_grabbed IS NULL
             AND any-other-criteria
           LIMIT 1;
$id = SELECT LAST_INSERT_ID();

... process $id ...  (This may or may not involve transactions)

Release the row (or, in your case, delete it):
again, autocommit=ON suffices...
DELETE ... WHERE id = $id;

“永远不要”在使用InnoDB时使用表锁。（可能存在某些情况下需要使用，但这不是其中之一。）

- Rick James