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毫秒),或者实际上只有少数行,则可能需要进行额外的调整。