mysql - 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
    }

另一个线程同时写入表。出于某种原因，我不明白此配置在读取和写入时都与锁定表陷入僵局。

我的问题是:这是锁定正确的解决方案以确保我读取每一行一次且仅一次(并删除它)。

或者这是否更好地作为交易来处理，如果是的话是哪一种？我没有交易经验。

Answer 1

计划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 使用表锁。 (可能有用例，但这不是一个。)