【MYSQL】一个关于Innodb存储引擎表的加锁问题

今天有童鞋问了一个关于innodb加锁处理的疑问，于是根据案例做出了分析，见下文：

版本Oracle mysql 5.5 (GPL) 事务隔离级别 REPEATABLE-READ，现在遇到一个很奇怪的问题：
现将问题简化：
表是这样的表：
show create table a\G             
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

select * from a;
+----+
| id | 
+----+
|  1 |
+----+
1 rows in set (0.00 sec)

会话1：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中没有，即不存在这条数据）
Empty set (0.00 sec)
完成执行 
再操作会话2：

会话2：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中没有，即不存在这条数据）
此时没有hang住，同样返回结果：
Empty set (0.00 sec)
这个是为什么、？？？

背景是因为业务需求就是先做判断是否存在id=2的数据，若无，则insert一条进去， 若两个以上的会话同时这样，会导致deadlock。 将上述步骤中的where id = 2 修改为 where id = 1 （id=1有这条数据） 同样步骤跑一边，此时会话1返回 Empty set (0.00 sec)  而会话2 正常hang住。 这应该才是正常的。。。
为什么id=2（原表中没有的数据）的情况，没有被锁住呢？？？

后面又做了测试：
将a表的primary key删除，即a表没有主键，id列无约束时，且无论是否能够匹配到数据，均可被正常锁住。 只有在主键匹配结果为empty时，无法被加上for update锁。


分析：
首先，按照前面的SQL写法：
会话1：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中没有，即不存在这条数据）
Empty set (0.00 sec)
完成执行 
再操作会话2：
会话2：
mysql> begin;
mysql> select * from a where id=2 for update; （id=2在原表中没有，即不存在这条数据）

在这种情况下，无论id是不是主键、常规索引、或者无索引，都不会加锁；因为加锁至少是要针对于每行的资源，两个会话中都根本没有id=2产生，怎么会锁住呢？
而后楼主又说了业务需求是查询是否有2，没有则插入，那么这两个会话的写法应该是第一个查询结束后再插入id=2的记录吧？？？ 所以不知道最上面SQL是不是漏掉了插入id=2呢？？
现在就当做第一个事务查询后插入id=2的记录，来分析下整个过程：
id为主键的情况下，手动开启一个事务，执行：select * from a where id=2 for update;  这条SQL是要获取id=2的一个当前读，于是会走主键索引扫描，如果扫描到数据会在该记录加上一个排它锁X，因为是主键所以只会有一条id=2的值，所以找到数据的话只会涉及一条记录的排它锁；但是很不巧，主键中没有id=2的值，所以此时数据库未加任何锁。然后这个事务又执行了：insert into a values (2)，这时表中新产生了一条数据id=2，因为id为主键，不允许重复，不能让其他事务也插入id=2这一行数据，所以id=2会加一个X锁。
那么再看第二个会话，执行：select * from a where id=2 for update时，这时就一定会被锁住了，因为select+forupdate时，会进行当前读，也就是说要获取到这一行的最新版本并加上X锁，但是id=2已经在第一个会话中加入x锁了，所以出现锁冲突，第二个会话发生阻塞，等待会话1释放X锁
而在id不是主键的情况下，也无索引，第一个会话执行：select * from a where id=2 for update;在可重复读的隔离级别下，这条SQL会将表中的所有记录以及缝隙全锁住，记录加X锁，缝隙加GAP锁，也就是说insert,update,delete都执行不了，而第二个会话执行：select * from a where id=2 for update时，同理for update需要进行当前读，加X锁，而第一个会话已经将表锁死了，所以会话2也产生阻塞

总结：在以上场景下，id是主键或不是主键，会话2执行forupdate时都会阻塞，但是阻塞原因不同；id为主键时，第一个会话的insert导致会话2的forupdate阻塞，id不是主键时，会话1的for update导致会话2的forupdate阻塞

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29018063/viewspace-2095021/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29018063/viewspace-2095021/