MySQL InnoDB即使在READ COMMITTED级别下，删除操作仍然会锁定主键。

Question

前言 我们的应用程序运行多个线程并行执行DELETE查询。这些查询会影响隔离的数据，即不存在并发的DELETE操作会同时作用于不同线程中的同一行。然而，根据MySQL的文档，DELETE语句使用所谓的“next-key”锁，既锁定匹配的键值，又锁定一些间隙。这就导致了死锁问题，我们唯一找到的解决方法是使用READ COMMITTED隔离级别。 问题 当执行涉及大型表的复杂DELETE语句和JOIN操作时，问题就出现了。在一个特殊情况下，我们有一个警告表，只有两行数据，但是查询需要删除属于两个不同内连接表中某些特定实体的所有警告。查询语句如下：

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=? AND dp.dirty_data=1

当day_position表足够大（在我的测试案例中有1448行）时，即使使用 READ COMMITTED 隔离模式，任何事务都会阻塞整个proc_warnings表。 该问题在这个示例数据http://yadi.sk/d/QDuwBtpW1BxB9上始终能够复现，无论是在MySQL 5.1（在5.1.59上验证过）还是MySQL 5.5（在MySQL 5.5.24上验证过）。 编辑：链接的示例数据还包含了查询表的模式和索引，为了方便起见，在此重现。

CREATE TABLE  `proc_warnings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `transaction_id` int(10) unsigned NOT NULL,
    `warning` varchar(2048) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `proc_warnings__transaction` (`transaction_id`)
);

CREATE TABLE  `day_position` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `transaction_id` int(10) unsigned DEFAULT NULL,
    `sort_index` int(11) DEFAULT NULL,
    `ivehicle_day_id` int(10) unsigned DEFAULT NULL,
    `dirty_data` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `day_position__trans` (`transaction_id`),
    KEY `day_position__is` (`ivehicle_day_id`,`sort_index`),
    KEY `day_position__id` (`ivehicle_day_id`,`dirty_data`)
) ;

CREATE TABLE  `ivehicle_days` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `d` date DEFAULT NULL,
    `sort_index` int(11) DEFAULT NULL,
    `ivehicle_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `ivehicle_days__is` (`ivehicle_id`,`sort_index`),
    KEY `ivehicle_days__d` (`d`)
);

每笔交易的查询如下：

• 事务1

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从proc_warnings pw表中删除数据
  内连接day_position dp表
      ON dp.transaction_id = pw.transaction_id 
  内连接ivehicle_days vd表
      ON vd.id = dp.ivehicle_day_id 
  WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;
  

• 事务2

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从proc_warnings pw表中删除数据
  内连接day_position dp表
      ON dp.transaction_id = pw.transaction_id 
  内连接ivehicle_days vd表
      ON vd.id = dp.ivehicle_day_id 
  WHERE vd.ivehicle_id=13 AND dp.dirty_data=1;
  

其中一个总是出现“超过锁等待超时...”错误。 information_schema.innodb_trx 包含以下行：

| trx_id     | trx_state   | trx_started           | trx_requested_lock_id  | trx_wait_started      | trx_wait | trx_mysql_thread_id | trx_query |
| '1A2973A4' | 'LOCK WAIT' | '2012-12-12 20:03:25' | '1A2973A4:0:3172298:2' | '2012-12-12 20:03:25' | '2'      | '3089'              | 'DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1' |
| '1A296F67' | 'RUNNING'   | '2012-12-12 19:58:02' | NULL                   | NULL | '7' | '3087' | NULL |

information_schema.innodb_locks

| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |

正如我所看到的，这两个查询都想要对主键为53的行进行排他性的X锁定。然而，它们都不得删除proc_warnings表中的行。我只是不明白为什么索引被锁定。此外，当proc_warnings表为空或day_position表包含较少的行（例如100行）时，索引也不会被锁定。 进一步调查是运行类似SELECT查询的EXPLAIN命令。它显示查询优化器不使用索引来查询proc_warnings表，这就是我能想到的为什么会阻塞整个主键索引的唯一原因。 简化的情况也可以复现该问题，当只有两个表，并且记录只有几条时，但子表在父表的引用列上没有索引。 创建parent表：

CREATE TABLE `parent` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

创建子表

CREATE TABLE `child` (
  `id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

填写表格

INSERT INTO `parent` (id) VALUES (1), (2);
INSERT INTO `child` (id, parent_id) VALUES (1, NULL), (2, NULL);

在两个并行事务中进行测试：

• 事务1

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从child表中删除c
    内连接parent表p ON p.id = c.parent_id 
  WHERE p.id = 1;
  

• 事务2

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从child表中删除c
    内连接parent表p ON p.id = c.parent_id 
  WHERE p.id = 2;
  

在这两种情况下，共同的问题是MySQL不使用索引。我相信这就是整个表锁定的原因。 我们目前能看到的唯一解决办法是将默认的锁等待超时时间从50秒增加到500秒，以便让线程完成清理工作。然后祈祷一切顺利。 非常感谢任何帮助。

- vitalidze

---

我有一个问题：在任何一次事务中，你是否执行了COMMIT操作？ - RolandoMySQLDBA

当然。问题在于，除非其中一个事务提交更改，否则所有其他事务都必须等待。简单的测试用例中没有包含提交语句来展示如何重现该问题。如果在非等待事务中运行提交或回滚操作，它会同时释放锁定，并且等待事务完成其工作。 - vitalidze

当你说MySQL在任何情况下都不使用索引，是因为实际场景中没有索引吗？如果有索引，你能提供它们的代码吗？是否可以尝试以下发布的索引建议？如果没有索引，并且无法尝试添加任何索引，那么MySQL无法限制每个线程处理的数据集。如果是这种情况，N个线程将简单地将服务器工作负载乘以N倍，只让一个线程运行并使用参数列表如{WHERE vd.ivehicle_id IN (2, 13) AND dp.dirty_data=1;}会更高效。 - JM Hicks

好的，在链接的示例数据文件中找到了隐藏的索引。 - JM Hicks

иҝҳжңүеҮ дёӘй—®йўҳпјҡ1пјүеҪ“day_positionиЎЁејҖе§ӢиҝҗиЎҢзј“ж…ўпјҢдҪ дёҚеҫ—дёҚе°Ҷи¶…ж—¶йҷҗеҲ¶еўһеҠ еҲ°500з§’ж—¶пјҢйҖҡеёёдјҡеҢ…еҗ«еӨҡе°‘иЎҢж•°жҚ®пјҹ2пјүеҪ“еҸӘжңүзӨәдҫӢж•°жҚ®ж—¶пјҢиҝҗиЎҢжүҖйңҖзҡ„ж—¶й—ҙжҳҜеӨҡд№…пјҹ - JM Hicks

添加了一个更新颖的方法来回答。将单个删除语句拆分为一个存储过程，其中包含一个独立的读取语句和一个独立的删除语句。删除语句是通过使用 DELETE FROM proc_warnings WHERE IN (...) 动态生成的，其中 IN 子句是第一条语句的查询结果。仍然认为如果有大量数据，尝试更好地覆盖索引将会产生很大的差异，但这似乎可以防止线程因相互不兼容的排他锁请求而被阻塞的时间过长。 - JM Hicks

1）在实际情况中，“day_position”包含大约300-400K行，“ivehicle_days”包含50-100K行。 2）在样本数据上运行速度很快，不到1秒钟。 3）关于存储过程 - 我们目前没有使用它。谢谢您的建议。最后，我们采用了两阶段（或双查询）的清理方案。第一个查询检查是否有与“WHERE”子句和“SELECT”语句匹配的行（它不会锁定行）。下一个查询是实际的“DELETE”操作，如果检查成功，则执行删除。这对我们很有帮助。 - vitalidze

嗯... "两个阶段"，先是一个 "SELECT" 然后是一个 "DELETE"，SELECT "不锁定行"，DELETE "如果检查成功"，这正是在最后一条评论之前的9小时内发布的存储过程中的情况。如果这是你自己接受的解决方案，为什么不在这里标记一下，让未来的读者更容易意识到这个变通/解决方案呢？如果是别人的答案，你标记了就可以得到2分 :-) 新年快乐。 - JM Hicks

Answer 1

我可以看出READ_COMMITTED如何导致这种情况。

READ_COMMITTED允许三件事情：

• 使用READ_COMMITTED隔离级别的其他事务可以看到已提交更改的可见性。
• 不可重复读取：执行相同检索的事务每次可能会得到不同的结果。
• 幻像：事务可能会出现之前不可见的行。

这为事务本身创建了一个内部范式，因为事务必须与以下内容保持联系：

• InnoDB缓冲池（在提交仍未刷新时）
• 表的主键
• 可能
  • 双写缓冲区
  • 撤消表空间
• 图示表示

如果两个不同的READ_COMMITTED事务正在访问相同的表/行，并且以相同的方式进行更新，那么请准备好期望在gen_clust_index（也称为聚集索引）中获得独占锁，而不是表锁。根据您简化案例中的查询：

• 事务 1

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从child表中删除与parent表中id字段匹配的记录，条件是parent表的id为1。
  

• 事务 2

  设置事务隔离级别为读已提交;
  设置自动提交为0;
  开始事务;
  从child表中删除与parent表中id字段匹配的记录，条件是parent表的id为2。
  

你正在锁定gen_clust_index中的相同位置。有人可能会说，“但是每个事务都有不同的主键。”不幸的是，在InnoDB的眼中，情况并非如此。恰好id 1和id 2位于同一页上。 回顾一下你在问题中提供的information_schema.innodb_locks。

| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |

除了lock_id和lock_trx_id之外，锁的描述都是相同的。由于事务处于相同的隔离级别，这确实会发生。 相信我，我以前处理过这种情况。以下是我以前的帖子：

• 2012年11月05日：如何分析插入查询中的InnoDB死锁状态？
• 2011年08月08日：InnoDB死锁是否仅限于INSERT/UPDATE/DELETE操作？
• 2011年06月14日：偶尔出现查询缓慢的原因是什么？
• 2011年06月08日：如果按顺序执行这两个查询，会导致死锁吗？
• 2011年06月06日：解读InnoDB状态日志中的死锁问题困扰我。

- RolandoMySQLDBA

---

我已经阅读了你在MySQL文档中描述的内容。但对我来说，READ COMMITTED 的主要特点是它如何处理锁。它应该释放非匹配行的索引锁，但实际上并没有这样做。 - vitalidze

如果由于错误而回滚了一个单独的SQL语句，该语句设置的一些锁可能会被保留。这是因为InnoDB以一种格式存储行锁，无法事后确定哪个语句设置了哪个锁：http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlock-detection.html - RolandoMySQLDBA

请注意，我提到了在同一页中存在两行进行锁定的可能性（请参阅您在问题中提供的information_schema.innodb_locks）。 - RolandoMySQLDBA

关于回滚单个语句 - 我理解的是，如果在单个事务中单个语句失败，它仍然可能保持锁定。这没问题。我有一个很大的疑问是，为什么在成功处理“DELETE”语句后它不释放不匹配的行锁定。 - vitalidze

有了两个互斥锁，必须回滚一个。可能会出现锁卡住的情况。工作理论是：回滚的事务可能会重试，并且可能会遇到前一个事务持有的旧锁。 - RolandoMySQLDBA

Answer 2

新答案（MySQL风格的动态SQL）： 好的，这个解决方案采用了其他帖子中描述的方法，即颠倒了获取互斥不兼容的排他锁的顺序，以便无论发生多少次锁定，它们只会在事务执行结束时的最短时间内发生。 这是通过将语句的读取部分分离为自己的select语句，并动态生成一个delete语句来实现的，由于语句出现的顺序，这个delete语句将被强制在最后运行，并且只会影响到proc_warnings表。 在sql fiddle上提供了一个演示： 这个链接显示了模式和样本数据，以及一个简单的查询，用于匹配ivehicle_id=2的行。结果有2行，因为它们都没有被删除。

这个 链接 显示了相同的模式、示例数据，但将值 2 传递给 DeleteEntries 存储过程，告诉 SP 删除 proc_warnings 条目，以便 ivehicle_id=2。行的简单查询没有返回结果，因为它们都已成功删除。

演示链接仅说明代码按预期工作以进行删除。拥有适当测试环境的用户可以评论是否解决了阻塞线程的问题。

这里也提供代码以方便查看：

CREATE PROCEDURE DeleteEntries (input_vid INT)
BEGIN

    SELECT @idstring:= '';
    SELECT @idnum:= 0;
    SELECT @del_stmt:= '';

    SELECT @idnum:= @idnum+1 idnum_col, @idstring:= CONCAT(@idstring, CASE WHEN CHARACTER_LENGTH(@idstring) > 0 THEN ',' ELSE '' END, CAST(id AS CHAR(10))) idstring_col
    FROM proc_warnings
    WHERE EXISTS (
        SELECT 0
        FROM day_position
        WHERE day_position.transaction_id = proc_warnings.transaction_id
        AND day_position.dirty_data = 1
        AND EXISTS (
            SELECT 0
            FROM ivehicle_days
            WHERE ivehicle_days.id = day_position.ivehicle_day_id
            AND ivehicle_days.ivehicle_id = input_vid
        )
    )
    ORDER BY idnum_col DESC
    LIMIT 1;

    IF (@idnum > 0) THEN
        SELECT @del_stmt:= CONCAT('DELETE FROM proc_warnings WHERE id IN (', @idstring, ');');

        PREPARE del_stmt_hndl FROM @del_stmt;
        EXECUTE del_stmt_hndl;
        DEALLOCATE PREPARE del_stmt_hndl;
    END IF;
END;

这是在事务中调用程序的语法：

CALL DeleteEntries(2);

原回答（还是觉得不错） 看起来有两个问题：1）查询缓慢 2）意外的锁定行为

关于问题＃1，通常可以通过两种技术同时解决缓慢查询 - 查询语句简化和对索引的有用添加或修改。你自己已经注意到了索引的重要性 - 没有索引，优化器无法搜索需要处理的有限行集，每个表中的每一行都会增加额外的工作量。

在看到模式和索引的帖子之后进行修订： 但我想，通过确保您拥有良好的索引配置，您的查询可能会获得最佳性能收益。为此，可以追求更好的删除性能，甚至可能会在相同的表上添加额外的索引结构时导致更大的索引和明显较慢的插入性能。

稍微改善了一些：

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd` (`dirty_data`, `ivehicle_day_id`)

) ;


CREATE TABLE  `ivehicle_days` (
    ...,
    KEY `ivehicle_days__vid_no_sort_index` (`ivehicle_id`)
);

修订了这里：由于运行时间如此之长，我会将dirty_data保留在索引中，并且当我按照索引顺序将其放置在ivehicle_day_id之后时，我肯定弄错了 - 它应该是第一个。 但是如果我现在能处理它，因为必须有大量数据才能导致运行时间如此之长，我会选择使用全覆盖索引，以确保获得最佳的索引，以便在故障排除期间尽可能地解决问题，至少可以排除问题的这一部分。 最佳/全覆盖索引：

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd_trnsid_cvrng` (`dirty_data`, `ivehicle_day_id`, `transaction_id`)
) ;

CREATE TABLE  `ivehicle_days` (
    ...,
    UNIQUE KEY `ivehicle_days__vid_id_cvrng` (ivehicle_id, id)
);

CREATE TABLE  `proc_warnings` (

    .., /*rename primary key*/
    CONSTRAINT pk_proc_warnings PRIMARY KEY (id),
    UNIQUE KEY `proc_warnings__transaction_id_id_cvrng` (`transaction_id`, `id`)
);

最后两个变更建议追求的是两个性能优化目标：
1）如果连续访问的表的搜索键与当前访问表返回的聚集键结果不相同，我们可以消除在聚集索引上进行第二组索引搜索和扫描操作的需求
2）如果前者不成立，仍然至少存在一种可能性，即优化器可以选择更高效的连接算法，因为索引将按照所需的连接键保持排序顺序。

您的查询似乎已经尽可能简化（在此复制以防以后被编辑）：

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
    ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
    ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;

除非当然有关于书面连接顺序的问题会影响查询优化器的执行方式，否则你可以尝试一些其他人提供的重写建议，包括可能带有索引提示的这个（可选）：

DELETE FROM proc_warnings
FORCE INDEX (`proc_warnings__transaction_id_id_cvrng`, `pk_proc_warnings`)
WHERE EXISTS (
    SELECT 0
    FROM day_position
    FORCE INDEX (`day_position__id_rvrsd_trnsid_cvrng`)  
    WHERE day_position.transaction_id = proc_warnings.transaction_id
    AND day_position.dirty_data = 1
    AND EXISTS (
        SELECT 0
        FROM ivehicle_days
        FORCE INDEX (`ivehicle_days__vid_id_cvrng`)  
        WHERE ivehicle_days.id = day_position.ivehicle_day_id
        AND ivehicle_days.ivehicle_id = ?
    )
);

关于第二点，意外的锁定行为。 正如我所看到的，这两个查询都想要在主键为53的行上获得独占的X锁。然而，它们都不应该删除proc_warnings表中的行。我只是不明白为什么索引会被锁定。 我猜测索引被锁定是因为要锁定的数据行位于聚集索引中，也就是说数据行本身就存在于索引中。 它会被锁定，因为： 1）根据http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html中的说明 “...DELETE语句通常会在处理SQL语句时对扫描到的每个索引记录设置记录锁。无论语句中是否有WHERE条件来排除该行都无关紧要。InnoDB不会记住确切的WHERE条件，只知道哪些索引范围已经被扫描过。” 你之前也提到了： 对我来说，READ COMMITTED 的主要特点是它如何处理锁。它应该释放非匹配行的索引锁，但实际上并没有这样做。 他提供了以下参考资料：
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed 该参考资料与您所说的相同，但根据同一参考资料，有一个条件可以释放锁： 此外，在 MySQL 评估 WHERE 条件之后，将释放非匹配行的记录锁。 这也在此手册页面中重申：http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html 使用READ COMMITTED隔离级别或启用innodb_locks_unsafe_for_binlog还会产生其他影响：MySQL在评估WHERE条件之后会释放不匹配行的记录锁。因此，我们被告知必须在释放锁之前评估WHERE条件。不幸的是，我们没有被告知何时评估WHERE条件，这可能会因优化器创建的不同计划而有所变化。但它确实告诉我们，锁释放取决于查询执行的性能，在上面讨论的语句的谨慎编写和适当使用索引的基础上进行优化。它还可以通过更好的表设计来改进，但这可能最好留给一个单独的问题。此外，如果proc_warnings表为空，则也不会锁定索引。如果day_position表包含较少数量的行（即100行），则索引也不会被锁定。 这可能意味着很多事情，但可能不仅限于以下几种情况：由于统计数据的变化而导致执行计划不同，由于数据集/连接操作规模较小导致执行速度更快从而无法观察到锁定时间过短。

- JM Hicks

---

WHERE条件在查询完成时进行评估，对吗？我以为锁会在某些并发查询执行后立即释放。这是自然的行为。然而，事实并非如此。这个帖子中建议的任何查询都无法避免在proc_warnings表中出现聚集索引锁定。我想我会向MySQL提交一个错误报告。谢谢你的帮助。 - vitalidze

我也不会期望他们避免锁定行为。我会期望它被锁定，因为我认为文档说了这就是预期的方式，无论我们是否希望它如何处理查询。我只是期望解决性能问题能够让并发查询在明显的（超过500秒的）长时间内不再阻塞。 - JM Hicks

尽管你的{WHERE}看起来可以在连接处理过程中用来限制哪些行包含在连接计算中，但我不明白你的{WHERE}子句如何在每个锁定的行上进行评估，直到整个连接集合也被计算完成。话虽如此，在我们的分析中，我怀疑你是对的，我们应该认为"WHERE条件在查询完成时进行评估"。然而，这使我得出了同样的总体结论，即性能问题需要解决，然后并发度将成比例增加。 - JM Hicks

记住，适当的索引有可能消除对proc_warnings表进行的全表扫描。为了实现这一点，我们需要查询优化器能够很好地工作，并且我们的索引、查询和数据与之协同工作。最终，参数值必须评估为目标表中两个查询之间不重叠的行。索引需要为查询优化器提供一种有效搜索这些行的方法。我们需要优化器意识到潜在的搜索效率，并选择这样的计划。 - JM Hicks

如果在参数值、索引、proc_warnings表中的非重叠结果以及优化器计划选择方面一切顺利，即使在每个线程执行查询所需的时间内会生成锁定，但是这些锁定如果不重叠，则不会与其他线程的锁定请求发生冲突。 - JM Hicks

如果您有一个合理的索引集，您还可以尝试使用索引提示语法 - http://dev.mysql.com/doc/refman/5.1/en/index-hints.html。这在某些情况下很有帮助，如果优化器似乎没有使用建议的好索引。继续上面的建议，如果添加索引后仍然花费太长时间，您可以尝试从proc_warnings pw中使用索引(pw_transaction_id_index)来查询{...}。 - JM Hicks

正如我在这里某个地方所说的，有几个DELETE查询（约10个）在并行事务中执行。从proc_warnings表中进行的DELETE只是其中之一。所有索引都已正确定义，当proc_warnings足够大或者连接的表不太大时，MySQL会使用它们。我相信这个特定的查询速度非常快。然而，它会阻塞整个proc_warnings表，所有其他并发事务必须等待所有（约10个）DELETE完成。根据表的大小，它们可能需要很长时间。 - vitalidze

我已经尝试过USE INDEX和FORCE INDEX，但没有帮助。 - vitalidze

是的，如果FORCE INDEX不起作用，那么我看不到其他方法可以尝试避免对小表进行锁定扫描（除非添加一堆带有NULL transaction_ids、ANSI NULL设置和现有=运算符的虚拟记录来愚弄优化器）。但我认为这个解决办法存储在程序中，因为它只在最后强制执行锁定部分，应该能够将对并发性的影响降到可察觉的程度。你能试试这个存储过程吗？（祈祷一切顺利）。希望如此。 - JM Hicks

我会在今天或明天试一试。然后在这里报告结果。 - vitalidze

Answer 3

我看了一下查询和解释。虽然不确定，但我有一种直觉，问题可能是以下这样的。让我们来看一下查询：

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=? AND dp.dirty_data=1;

等效的SELECT语句是：

SELECT pw.id
FROM proc_warnings pw
INNER JOIN day_position dp
   ON dp.transaction_id = pw.transaction_id
INNER JOIN ivehicle_days vd
   ON vd.id = dp.ivehicle_day_id
WHERE vd.ivehicle_id=16 AND dp.dirty_data=1;

如果你看一下它的解释，你会发现执行计划从proc_warnings表开始。这意味着MySQL会扫描表中的主键，并对每一行检查条件是否为真，如果是，则删除该行。这就是为什么MySQL必须锁定整个主键。

你需要做的是颠倒JOIN的顺序，也就是找到所有具有vd.ivehicle_id=16 AND dp.dirty_data=1的事务ID，并将它们与proc_warnings表进行连接。

也就是说，你需要修补其中一个索引：

ALTER TABLE `day_position`
 DROP INDEX `day_position__id`,
 ADD INDEX `day_position__id`
   USING BTREE (`ivehicle_day_id`, `dirty_data`, `transaction_id`);

重新编写删除查询语句：

DELETE pw
FROM (
  SELECT DISTINCT dp.transaction_id
  FROM ivehicle_days vd
  JOIN day_position dp ON dp.ivehicle_day_id = vd.id
  WHERE vd.ivehicle_id=? AND dp.dirty_data=1
) as tr_id
JOIN proc_warnings pw ON pw.transaction_id = tr_id.transaction_id;

- newtover

---

很遗憾，这并没有帮助，也就是说proc_warnings中的行仍然被锁定。不管怎样，还是谢谢。 - vitalidze

Answer 4

当您设置事务级别时，如果不采用特定方法进行设置，则仅将“读取已提交”应用于下一个事务（即set auto commit）。这意味着在自动提交为0之后，您不再处于“读取已提交”的状态。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
DELETE c FROM child c
INNER JOIN parent p ON
    p.id = c.parent_id
WHERE p.id = 1;

你可以通过查询来检查你所处的隔离级别。

SELECT @@tx_isolation;

- Franck

---

这是不正确的。为什么SET AUTOCOMMIT=0应该重置下一个事务的隔离级别？我相信如果之前没有开始事务，它会开始一个新的事务（这就是我的情况）。因此，更准确地说，下一个START TRANSACTION或BEGIN语句是不必要的。我禁用自动提交的目的是在DELETE语句执行后保留事务的打开状态。 - vitalidze

@SqlKiwi 这是编辑帖子的方式，这是评论的方式；-) - jcolebrand