代码之家 › 专栏 › 技术社区 › Alpesh Jikadra

Mysql对不同条件的多个delete查询锁定相同的数据

mysql

Alpesh Jikadra · 技术社区 · 7 年前

Mysql版本: 5.7.14日志

我有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);

在我的应用程序中,两个线程试图执行delete查询,但它被卡住了。所以我尝试在Mysql中复制相同的东西,如下所示。

如何繁殖

启动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`;

所以上面的查询显示两个不同的事务正在运行并使用相同的 锁定模式、锁定空间、锁定页面和锁定数据

检查以下屏幕截图。

所以问题

为什么在我使用不同的delete查询和不同的产品id时,两个不同的事务会锁定相同的数据?

谢谢

1 回复 | 直到 7 年前

Solarflare 7 年前

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

很不幸,你的样品有点误导。MySQL本身就太聪明了,所以当它意识到可能无论如何都要读取大部分表时,它只需要这样做,不需要使用辅助索引就可以读取整个表,从而使用主键来锁定(因为您没有主键,所以在您的情况下是内部的 GEN_CLUST_INDEX 列入 lock_index ). 这实际上将锁定所有行。

使用 explain delete FROM aggr WHERE a_date BETWEEN ... 将告诉您将使用哪个索引(在 key -列)。为其他日期添加一些行(直到 explain 不显示 null 你的样品应该可以用 准确的 查询,因为MySQL将开始使用 unique_row -查找(单个)的索引 a_date 和 product_id 不与其他查询重叠(但不是日期范围)。

原来的表可能已经有了更多的行,如果有更多的行可以解决您的问题,您可能还没有进行调查,因此您可能正在使用另一个查询。很可能是一个不同的日期范围(好吧,是一个实际的范围,而不是一个单一的日期)。如果您这样做,您的查询将与w.r.t.日期重叠,因为最有可能使用的索引( 唯一行 )将锁定完整的日期范围(如果它跨越一天以上),因为它以日期开始。MySQL数据库可以使用上的索引 产品id ,但可能没有(否则你就不会有这个问题)。

所以在你的例子中,添加一个索引 (product_id,a_date) (或以这些列开头的主键)应使查询只锁定给定的 产品id .

简化一点(可以找到关于索引如何工作的更多细节,例如。 in the documentation ),在您的例子中,MySQL将锁定介于 产品id / start_date 和 产品id / end_date . 要知道“中间”是什么,索引中的列顺序是相关的。在由 产品id ,然后由 约会 ,没有其他 产品id 在这个范围内 product_id = 2 将在每个 product_id = 1 每一个可能的日期)。在由 约会 ,然后由 产品id , date = 2018-07-30 / 产品id=2 将介于 日期=2018-07-30 / 产品id=1 和 date = 2018-07-31 / 产品id=1 . 所以在第一种情况下(索引 (product_id, a_date) ),产品之间没有重叠,而在第二种情况下(例如 唯一行 -如果您的日期范围跨越多个日期,则多个产品将位于锁定范围内。

这是相当具体的(假定的)查询(固定的 产品id 以及日期范围),如果您更改条件(例如使用产品范围)或将其与其他查询组合(否则您可能不需要事务处理,或者您可能不关心其他查询是否需要等待10毫秒),或者实际上只有少数行,则可能需要进行额外的调整。