大家如何理解innodb尖隙索引的加锁.

fan_zhen_hua

网上看到这样一段文字。mysql服务器只能对innodb返回的数据进行where操作。InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。 actor_id = 1的记录为什么也会被锁住呢，感觉在innodb存储引擎中就可以把actor_id<>1给过滤掉,抛出来大家讨论一下。看下面的例子。（网上找的例子，今天在家里没有环境进行验证了。）

     只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。
来看个例子：

create table actor( actor_id int unsigned NOT NULL AUTO_INCREMENT, name varchar(16) NOT NULL DEFAULT '', password        varchar(16) NOT NULL DEFAULT '', PRIMARY KEY(actor_id), KEY     (name) ) ENGINE=InnoDB insert into actor(name,password) values('cat01','1234567'); insert into actor(name,password) values('cat02','1234567'); insert into actor(name,password) values('ddddd','1234567'); insert into actor(name,password) values('aaaaa','1234567');

SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE;

该查询仅仅返回2---3的数据，实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）：

mysql> EXPLAIN SELECT actor_id FROM test.actor -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql>

表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。
为了证明row 1已经被锁住，我们另外建一个连接，执行如下操作：

SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。
如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

[ 本帖最后由 fan_zhen_hua 于 2010-8-26 22:40 编辑 ]

fan_zhen_hua

测试了一下，发现不等于是不会被锁的。这篇文章的观点是错误的，也有可能是因为版本的问题。
在session2中的结果如下:
root@zhoucang 09:35:48>SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;
+----------+
| actor_id |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

root@zhoucang 09:35:53>SELECT actor_id FROM actor WHERE actor_id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@zhoucang 09:36:03>SELECT actor_id FROM actor WHERE actor_id = 3 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@zhoucang 09:36:08>SELECT actor_id FROM actor WHERE actor_id = 4 FOR UPDATE;
+----------+
| actor_id |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

jinguanding

actor_id < 4 AND actor_id <> 1按mysql优化器的能力来说，肯定会被优化为actor_id>1 AND actor_id < 4
为此只锁住2,3记录......

P-Linux

在业务中经常发现，不仅锁需要的记录，该可能锁住临近的资源
例如a >1 and a<4，经常会发现a=0和a=5也被锁住了

fan_zhen_hua

能否贴一个具体测试的例子上来。大家分析一下。

innodb的尖隙锁不会对insert产生影响。。

jinguanding

例如a >1 and a<4，经常会发现a=0和a=5也被锁住了


以前测试过,没有发现你所描述的问题....

ucgary

由于时间关系,我没有去做测试了.
可参考 : http://dev.mysql.com/doc/refman/ ... xt-key-locking.html
mysql默认的是repeatable read ,但更进一步,他使用Next-Key Locking来防止幻象读.
请看这句话:To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking ．
比如: SELECT * FROM child WHERE id > 100 FOR UPDATE;
如果table内有id=90,id=102,那么gap就是90–102了,锁住这个gap,才能防止在事务执行期间,插入Id=101的记录,造成幻象读了.
这个Next key locking还有一个作用.
You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking allows you to “lock” the nonexistence of something in your table.
如下禁用Next key locking:
if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except in constraint checking.

fan_zhen_hua

摘自官网的这段，大概可以明白next-key-locking算法的思想。

innodb_locks_unsafe_for_binlog
这个选项在InnoDB搜索和索引扫描中关闭下一键锁定。这个选项的默认值是假（false）。
正常地，InnoDB使用一个被称为next-key locking的算法。当搜索或扫描一个表索引之时，InnoDB以这样一种方式实行行级锁定，它对任何遇到的索引记录设置共享的或独占的锁定。因此，行级锁定实际是索引记录锁定。InnoDB对索引记录设置的锁定也影响被锁定索引记录之前的“gap”。如果一个用户对某一索引内的记录R又共享的或独占的锁定，另一个用户不能立即在R之前以索引的顺序插入一个新的索引记录。这个选项导致InnoDB不在搜索或索引扫描中使用下一键锁定。下一键锁定仍然被用来确保外键强制及重复键核查。注意，使用这个选项可能会导致一些诡异的问题：假设你想要用值大于100的标识符从子表里读取并锁定所有的子记录，同时向随后在选定的行更新一些列：
SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描索引。如果在索引记录上的锁定不把在间隙处生成的插入排除锁定，同时一个新行被插进表中。如果你在同一个事务之内执行同样的SELECT，你会在查询返回的结果包里看到一个新行。这也意味着，如果新条目被加进数据库，InnoDB不保证连续性；尽管如此，对应连续性仍被保证。因此，如果这个选项被使用，InnoDB在大多数孤立级别保证READ COMMITTED。 （对这句话我不是很理解）
这个选项甚至更不安全。InnoDB在一个UPDATE或DELETE中只锁定它更新或删除的行。这大大减少了死锁的可能性，但是可以发生死锁。注意，即使在当类似的操作影响不同行时的情况下，这个选项仍然不允许诸如UPDATE这样的操作压倒相似选项（比如另一个UPDATE）。考虑下列例子：
CREATE TABLE A(A INT NOT NULL, B INT);
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
如果一个连接执行一个查询：
SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;
并且其它连接跟着第一个连接执行其它查询：
SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;

下面的解释应该可以比较清晰的明白这个算法的思想，但是(我经过测试，并没有hung住)
接着查询2要等查询1的提交或回滚，因为查询1对行（2，3）有一个独占的锁定，并且查询2在扫描行的同时也试着对它不能锁定的同一个行（2，3）采取一个独占的锁定。这是因为当innodb_locks_unsafe_for_binlog选项被使用之时，查询2首先对一个行采取一个独占的锁定，然后确定是否这个行属于结果包，并且如果不属于，就释放不必要的锁定。
因此，查询1按如下执行：
x-lock(1,2)
unlock(1,2)
x-lock(2,3)
update(2,3) to (2,5)
x-lock(3,2)
unlock(3,2)
x-lock(4,3)
update(4,3) to (4,5)
x-lock(5,2)
unlock(5,2)
并且查询2按如下执行：
x-lock(1,2)
update(1,2) to (1,4)
x-lock(2,3) - 等待查询1提交或回滚

[ 本帖最后由 fan_zhen_hua 于 2010-8-29 00:45 编辑 ]

fan_zhen_hua

ucgary兄说的问题应该是显式设置了innodb_locks_unsafe_for_binlog=true参数的原因吧，使用了next-key locking算法