时间戳

首页 > 解决方案 > Mysql为具有不同条件的多个删除查询锁定相同的数据

mysql - Mysql为具有不同条件的多个删除查询锁定相同的数据

问题描述

Mysql 版本：5.7.14-log

我有 16 行的下表。

CREATE TABLE aggr (
 a_date DATE,
 product_id INT(11),
 data_point VARCHAR(16),
 los INT(11),
 hour_0 DOUBLE(4,2),
 UNIQUE KEY `unique_row` (a_date,product_id,data_point,los),
 INDEX product_id(product_id)
);


INSERT INTO aggr(a_date,product_id,data_point,los,hour_0) 
VALUES
('2018-07-29',1,'arrivals',1,10),('2018-07-29',1,'departure',1,9),
('2018-07-29',1,'solds',1,12),('2018-07-29',1,'revenue',1,45.20),
('2018-07-30',1,'arrivals',2,10),('2018-07-30',1,'departure',2,9),
('2018-07-30',1,'solds',2,12),('2018-07-30',1,'revenue',2,45.20),

('2018-07-29',2,'arrivals',1,10),('2018-07-29',2,'departure',1,9),
('2018-07-29',2,'solds',1,12),('2018-07-29',2,'revenue',1,45.20),
('2018-07-30',2,'arrivals',2,10),('2018-07-30',2,'departure',2,9),
('2018-07-30',2,'solds',2,12),('2018-07-30',2,'revenue',2,45.20);

在我的应用程序中，两个线程尝试执行删除查询，但它卡住了。所以我尝试在 Mysql 中重现相同的内容，如下所示。

如何重现

启动 2 个不同的 mysql 会话（我正在使用 SQLYoug）

在第一次会话中尝试以下查询

START TRANSACTION;
DELETE FROM aggr
WHERE a_date BETWEEN '2018-07-29' AND '2018-07-29' 
AND product_id = 1 ;

在第二次会话中尝试以下查询。

START TRANSACTION;
DELETE FROM aggr
WHERE a_date BETWEEN '2018-07-29' AND '2018-07-29' 
AND product_id = 2 ;

现在执行以下查询

SELECT * FROM `information_schema`.`INNODB_LOCKS`;

所以上面的查询显示两个不同的事务正在运行并且使用相同的 lock_mode、lock_space、lock_page 和 lock_data

检查以下屏幕截图。 在此处输入图像描述

所以问题

为什么两个不同的事务锁定相同的数据，因为我使用具有不同 product_id 的不同删除查询？

谢谢

标签： mysql

解决方案

MySQL 在尝试查找要删除的行时锁定它查看的行。如果可以，MySQL 将为此使用索引。

不幸的是，您的样本有点误导。MySQL 本身就太聪明了，所以当它意识到它可能无论如何都必须读取大部分表时，它会这样做并在不使用二级索引的情况下读取整个表，从而使用主键锁定（由于您没有，因此在您的情况下是 中GEN_CLUST_INDEX列出的内部lock_index）。这实际上只会锁定所有行。

Usingexplain delete FROM aggr WHERE a_date BETWEEN ...将告诉您将使用哪个索引（在 -key列中）。为其他日期添加一些行（直到explain不再显示null该键），并且您的示例应该与该确切查询一起使用，因为 MySQL 将开始使用您的unique_row-index 来查找（单个）a_date并且product_id不与另一个重叠查询（但不是日期范围）。

您的原始表可能已经有更多行，并且您可能不会调查是否有更多行可以解决您的问题，因此您可能正在使用不同的查询。很可能是不同的日期范围（嗯，实际范围而不是单个日期）。如果这样做，您的查询将与日期重叠，因为最有可能使用的索引 ( unique_row) 将锁定该完整的日期范围（如果它跨越一天以上），因为它从日期开始。MySQL可能会使用索引 on product_id，但可能不会（否则你不会有这个问题）。

因此，在您的情况下，添加索引(product_id,a_date)（或例如以这些列开头的主键）应该使查询仅锁定给定的product_id.

稍微简化一下（有关索引如何工作的更多详细信息，例如可以在文档中找到），在您的情况下，MySQL 将锁定product_id/start_date和product_id/之间的所有行end_date。要知道“介于”之间的是什么，索引中的列顺序是相关的。在第一个按 排序的列表中product_id，然后按排序，该范围内a_date没有其他列表（因为将在每个可能的日期后排序）。在按 1 排序的列表中，然后按，/将位于/和/之间。因此，在第一种情况下（索引），您的产品之间没有重叠，而在第二种情况下（例如，您的product_idproduct_id = 2product_id = 1a_dateproduct_iddate = 2018-07-30product_id = 2date = 2018-07-30product_id = 1date = 2018-07-31product_id = 1(product_id, a_date)unique_row-index），如果您的日期范围跨越多个日期，则多个产品将位于锁定范围内。

这对于您的（假设的）查询（固定product_id和日期范围）相当具体，并且如果您更改条件（例如使用产品范围）或将其与其他查询相结合（否则您可能不需要交易，或者您可能不需要关心其他查询是否需要等待 10 毫秒）或者实际上只有少数行，您可能需要进行额外的调整。

推荐阅读