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

Question

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 session (我正在使用 SQLYoug)

在第一次 session 中尝试以下查询

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

在第二个 session 中尝试以下查询。

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 的不同删除查询？

谢谢

Answer 1

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

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

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

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

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

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

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