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);在我的应用程序中，两个线程试图执行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`;所以上面的查询显示两个不同的事务正在运行，并且使用相同的锁模式、锁空间、锁页和锁数据
查看下面的屏幕截图。
所以我有个问题
为什么两个不同的事务会锁定同一个数据，因为我使用的是具有不同产品标识的不同删除查询？
谢谢
1条答案
按热度按时间eivnm1vs1#
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不与其他查询重叠（但不是日期范围）。您的原始表可能已经有更多的行，并且您可能不会调查是否有更多的行可以解决您的问题，因此您可能正在使用不同的查询。很可能是一个不同的日期范围（好吧，一个实际的范围，而不是一个单一的日期）。如果您这样做，您的查询将与日期重叠，因为最有可能使用的索引(
unique_row)将锁定完整的日期范围（如果它跨越一天以上），因为它从日期开始。mysql可能会在上使用索引product_id，但可能没有（否则你就不会有这个问题）。在您的例子中，添加一个索引
(product_id,a_date)（例如，以这些列开头的主键）应该使查询只锁定给定的列product_id.把它简化一点（关于索引如何工作的更多细节可以在文档中找到），在您的例子中，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-索引），如果您的日期范围跨越多个日期，则多个产品将位于锁定范围内。这相当特定于您的（假定的）查询（固定的）
product_id如果您更改了条件（例如，使用产品范围）或将其与其他查询相结合（否则您可能不需要事务，或者您可能不关心其他查询是否需要等待10毫秒），或者实际上只有少数行，则可能需要进行额外的调整。