是关于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来说,会产生锁行和锁表两种情况吗,谁能解释下

解决方案 »

  1.   

    纠正一下
    `name` varchar(0) default NULL,错了
    应该是
    `name` varchar(11) default NULL,
    另外id值可以不用管,因为是自增的变量
      

  2.   

    按你说的步骤做了一遍,确实如你所说。
    另外,我将你的插入数据改为如下:
    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。
      

  3.   

    楼上的回答很专业,一定程度解答了我的问题,可是我加排它锁的目的就是为了防止一个session在查询并修改数据的时候,另一个session不允许读,如果加共享锁就违背我的需求 了
      

  4.   

    MYSQL文档中有一个说明。 估计是这个原因。
    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锁定允许你锁住在你的表中并不存在的一些东西。 
      

  5.   

    为了解决问题,我来给你方法。
    出现此类问题时,好好分析SHOW ENGINE INNODB STATUS 里面的信息! 
      

  6.   

    我看过了,答案正如3楼所说,可是那我的需求不就没法实现了么,我加上force index 强制索引也不行,mysql还是会锁表
      

  7.   

    可能是innodb独特的锁机制。可能在这种情况下,innodb认为与其行锁,不如来个表锁,毕竟表锁所花的代价比行锁来的小。如果是这样,那就没办法了,还是要用exclusive锁,毕竟业务逻辑最重要。设计程序时尽可能以最短的时间完成一个事务,以提高并发性。
      

  8.   


    此言正合我意,不過納悶的是我使用force index,強制索引,mysql竟然還是鎖表
      

  9.   

    大哥,明显表锁了……
    你11占去50%不锁表锁行?mysql才懒得找行在什么位置!