INSERT ... SELECT，InnoDB和锁定 [英] INSERT ... SELECT, InnoDB and locking

问题描述

我在MySQL 5.5.34(在Ubuntu 12.04上)上使用InnoDB引擎遇到以下行为.

I came accross the following behaviour with InnoDB engine under MySQL 5.5.34 (on Ubuntu 12.04).

执行INSERT ... SELECT语句时，某些意外行似乎被锁定在正在读取的表中.

When performing INSERT ... SELECT statements, some unexpected rows seem to be locked in the table being read from.

让我举个例子.假设两个表table_source和table_dest具有以下结构(特别注意索引):

Let me give an example. Suppose two tables table_source and table_dest with the following structure (particular attention to the indices):

CREATE TABLE table_source (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  group_id int(11) NOT NULL,
  data text NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY group_id_created (group_id,created)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE table_dest (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  group_id int(11) NOT NULL,
  data text NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY group_id_created (group_id,created)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

假设我现在执行以下事务:

Suppose I now execute the following transaction:

BEGIN;
INSERT INTO table_dest 
    SELECT * FROM table_source WHERE group_id = 3 AND created < '2014-01-04';
....

然后源表似乎已被group_id 2锁定为INSERT:

Then the source table seems to be locked for INSERT with group_id 2:

INSERT INTO table_source (group_id, data, created) 
    VALUES (2, 'data', NOW()); --< This locks

还有其他一些语句，以及它们是否锁定:

Here are some other statements and if they lock or not:

INSERT INTO table_source (group_id, data, created) 
    VALUES (3, 'data', NOW()); --< Does not lock

INSERT INTO table_source (group_id, data, created) 
    VALUES (1, 'data', NOW()); --< Does not lock

INSERT INTO table_source (group_id, data, created) 
    VALUES (3, 'data', '2014-01-01'); --< Does lock

有人可以解释一下为什么会发生这种情况吗(我想这与间隙锁有关)?有没有办法避免这种情况(我仍然想保持REPEATABLE READ隔离级别)?

Can somebody explain me why this happens (I suppose it has something to do with gap locks)? Is there a way to avoid this (I still want to keep the REPEATABLE READ isolation level)?

推荐答案

是正确的.要读取的表中的行被共享锁锁定(SELECT隐式地为LOCK IN SHARE MODE).没有办法避免这种情况.这就是您要系统询问的内容:复制与条件匹配的所有行.确保实际上所有与条件匹配的行以及该列表在执行该语句期间或之后不会更改的唯一方法是锁定行.

That's correct. The rows in the table being read from are locked with a shared lock (the SELECT is implicitly LOCK IN SHARE MODE). There isn't a way to avoid this. It's sort of what you're asking the system for: copy all rows which match a condition. The only way to ensure that is in fact all rows that match the condition and that that list does not change during or immediately after the execution of that statement, is to lock the rows.

为澄清为什么您无法使用group_id = 2进行INSERT:

As a clarification regarding why you are unable to INSERT with group_id = 2:

这与您的查询在KEY group_id_created (group_id, created)上具体为WHERE group_id = 3 AND created < '2014-01-04'有关.为了搜索所有与group_id = 3 AND created < '2014-01-04'匹配的行，将从从该条件开始的第一行开始向上向后遍历索引，该上限为(3, '2014-01-14')并一直持续到找到与该条件不匹配的行为止，因为created没有下界将是group_id < 3当然是group_id = 2的第一行.

This has to do with your query being specifically WHERE group_id = 3 AND created < '2014-01-04' on KEY group_id_created (group_id, created). In order to search all rows which match group_id = 3 AND created < '2014-01-04' the index will be traversed backwards starting with the first row which exceeds that condition the upper bound, which is (3, '2014-01-14') and continuing until finding a row which does not match the condition, which since created has no lower bound will be the first row where group_id < 3 which of course is group_id = 2.

这意味着group_id = 2遇到的第一行也被锁定，这将是具有最大created值的行.尽管这不是专门的间隙锁"，但这将使INSERT不可能进入(2, MAX(created))和(3, MIN(created))之间的间隙"(当然不是正确的SQL，只是伪SQL).

That means that the first row encountered with group_id = 2 is also locked, which will be the row with the maximum created value. This will make it impossible to INSERT into the "gap" between (2, MAX(created)) and (3, MIN(created)) (not proper SQL of course, just pseudo-SQL), although this is not a "gap lock" specifically.

这篇关于INSERT ... SELECT，InnoDB和锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！