是关于innodb行锁的问题,会的同学帮忙下我吧,跟我一步一步来,先建立表
CREATE TABLE `book` (
`id` int(11) NOT NULL auto_increment,
`num` int(11) default NULL,
`name` varchar(0) default NULL,
PRIMARY KEY (`id`),
KEY `asd` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk其中num字段加了索引然后插入数据,运行,
insert into book(num) values(11),(11),(11),(11),(11);
insert into book(num) values(22),(22),(22),(22),(22);然后打开2个mysql控制台窗口,其实就是建立2个session做并发操作********************************************************************
在第一个session里运行:
begin;
select * from book where num=11 for update;
出现结果:
+----+-----+------+
| id | num | name |
+----+-----+------+
| 11 | 11 | NULL |
| 12 | 11 | NULL |
| 13 | 11 | NULL |
| 14 | 11 | NULL |
| 15 | 11 | NULL |
+----+-----+------+
5 rows in set然后在第二个session里运行:
begin;
select * from book where num=22 for update;
出现结果:
+----+-----+------+
| id | num | name |
+----+-----+------+
| 16 | 22 | NULL |
| 17 | 22 | NULL |
| 18 | 22 | NULL |
| 19 | 22 | NULL |
| 20 | 22 | NULL |
+----+-----+------+
5 rows in set好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看:
回到第一个session,运行:
update book set name='abc' where num=11;
********************************************************************************************
问题来了,session竟然处于等待状态,可是num=11的行不是被第一个session自己锁住的么,为什么不能更新呢?好了,打这里大家也许有自己的答案,先别急,再请看一下操作。
把2个session都关闭,然后运行:
delete from book where num=11 limit 3;
delete from book where num=22 limit 3;
其实就是把num=11和22的记录各删去3行,
然后重复“***********************”之间的操作
竟然发现,运行update book set name='abc' where num=11;后,有结果出现了,说明没有被锁住,
这是为什么呢,难道2行数据和5行数据,对mysql来说,会产生锁行和锁表两种情况吗,谁能解释下
CREATE TABLE `book` (
`id` int(11) NOT NULL auto_increment,
`num` int(11) default NULL,
`name` varchar(0) default NULL,
PRIMARY KEY (`id`),
KEY `asd` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gbk其中num字段加了索引然后插入数据,运行,
insert into book(num) values(11),(11),(11),(11),(11);
insert into book(num) values(22),(22),(22),(22),(22);然后打开2个mysql控制台窗口,其实就是建立2个session做并发操作********************************************************************
在第一个session里运行:
begin;
select * from book where num=11 for update;
出现结果:
+----+-----+------+
| id | num | name |
+----+-----+------+
| 11 | 11 | NULL |
| 12 | 11 | NULL |
| 13 | 11 | NULL |
| 14 | 11 | NULL |
| 15 | 11 | NULL |
+----+-----+------+
5 rows in set然后在第二个session里运行:
begin;
select * from book where num=22 for update;
出现结果:
+----+-----+------+
| id | num | name |
+----+-----+------+
| 16 | 22 | NULL |
| 17 | 22 | NULL |
| 18 | 22 | NULL |
| 19 | 22 | NULL |
| 20 | 22 | NULL |
+----+-----+------+
5 rows in set好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看:
回到第一个session,运行:
update book set name='abc' where num=11;
********************************************************************************************
问题来了,session竟然处于等待状态,可是num=11的行不是被第一个session自己锁住的么,为什么不能更新呢?好了,打这里大家也许有自己的答案,先别急,再请看一下操作。
把2个session都关闭,然后运行:
delete from book where num=11 limit 3;
delete from book where num=22 limit 3;
其实就是把num=11和22的记录各删去3行,
然后重复“***********************”之间的操作
竟然发现,运行update book set name='abc' where num=11;后,有结果出现了,说明没有被锁住,
这是为什么呢,难道2行数据和5行数据,对mysql来说,会产生锁行和锁表两种情况吗,谁能解释下
`name` varchar(0) default NULL,错了
应该是
`name` varchar(11) default NULL,
另外id值可以不用管,因为是自增的变量
另外,我将你的插入数据改为如下:
insert into book(num) values(11),(12),(13),(14),(15);
insert into book(num) values(16),(17),(18),(19),(20),(21),(22),(23),(24),(25);
是不会出现这种情况的。
所以可以得出结论:这是表锁!!在你的实验数据作为测试数据的情况下,由于你的数据num的值重复率太高,只有连个值,分别是11和12.而数据量相对于这两个值来说却是比较大的,是10条,5倍的关系。
那么mysql在解释你的sql的时候,会忽略索引,因为它的优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁。
这就是你的问题造成的原因。你的“begin;”会开始一个事务,这个事务没有被提交的话,这个锁是不会释放的。
去掉这个"begin;“,其他操作不变,你肯定应该发现不会出现这种状况了。for update是排他锁。不妨建立一个共享锁,这样也可以避免这个问题---lock in share mode代替for update。
15.2.10.6. Next-Key锁定:避免幽灵问题
在行级锁定中,InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定:当它搜索或扫描表的索引之时,它对遇到的索引记录设置共享或独占锁定。因此,行级锁定事实上是索引记录锁定。 InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定,另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。假设你想要从有一个标识符值大于100的子表读并锁定所有子记录,并想着随后在选定行中更新一些列: SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外,一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT,你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的:一个事务应该能够运行,以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项,新的“幽灵”子记录可能会违反这一隔离原则。 当InnoDB扫描一个索引之时,它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生:InnoDB设置的锁定防止任何插入到id可能大过100的表。 你可以用next-key锁定在你的应用程序上实现一个唯一性检查:如果你以共享模式读数据,并且没有看到你将要插入的行的重复,则你可以安全地插入你的行,并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此,the next-key锁定允许你锁住在你的表中并不存在的一些东西。
出现此类问题时,好好分析SHOW ENGINE INNODB STATUS 里面的信息!
此言正合我意,不過納悶的是我使用force index,強制索引,mysql竟然還是鎖表
你11占去50%不锁表锁行?mysql才懒得找行在什么位置!