Innodb索引和锁的学习笔记

附录：前段时间学习了下innodb锁的相关知识，对锁和事务有了大体理解，这里做个小总结。

1.Innodb事务和锁的关系。

   Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持。事务要求了一组SQL语句的ACID特性，同时为了避免对一行记录的并发更新，innodb本身会在一定情况下加锁，然后等语句所在的事务退出后（rollbak或者commit）释放锁。其实在autocommit=true时，一个sql本身就是一个事务。

   Innodb在执行Update,Delete,Insert时会对记录加写锁（排他锁，加上排他锁后，不能再加共享锁和排他锁）。而Select语句不会对记录加锁。

 

   共享锁：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

   排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

 

举例：

 

 

   如图，左侧事务加了排他锁(for update),右侧事务直接查询不会等待（不加锁），加排他锁失败，加共享锁失败（lock in share mode）。左侧事务提交或者回滚后，释放锁。

 

2.Innodb的行锁特性。

   Innodb使用行锁加锁记录，也就是说会在加锁条件下对访问的具体行进行加锁，而不会锁住全表。这样能够大大提高表访问的吞吐量，减少不必要的记录锁。而Innodb的行锁是使用索引实现的，因此有一下几点需要注意：

•     如果对已一条加锁语句访问的数据走不了索引或者没加索引，其实他锁的是全表，这点需要注意。
•     如果索引使用的是范围，那么他会锁住命中的记录，同时会锁住不存在的记录间隙防止插入（这一行并不存在），gap lock。

          如图

          

    

       左边事务锁住了age>3的记录，那么很自然age=4的记录会被锁住。而在右边事务中，插入一条age=5个记录，而插入操作被hang住，这就是间隙锁。间隙锁是为了避免幻象读的发生，即A事务批量update condition=x的记录，同时B事务插入了一条condition=X的记录，那么A事务提交后，发现仍然存在一条没有更新的记录，貌似出现了幻觉，这就是幻读。

       因为间隙锁似乎为了防止插入，所以update age=5并不锁表，因为记录并不存在- -。

•     如果两个查询走不同索引，但是却需要处理同一条记录，会竞争锁。
•     虽然两个查询到的记录不同，但是走的是同一个索引，那么依然会竞争锁。

   例如：

 
 learntransaction表在age加了索引，左边窗口查询age=122 AND id=6的记录，右边窗口查询age=122 and id=3的记录，可以发现两者的记录并不同，但是却出现了锁竞争，因为两个公用了一个索引age=122。

•     当where语句的查询字段涉及到多个索引时，mysql会优化sql决定走的索引，所以如果id也加了索引，那么索然在where语句里age在前面，但不一定走age的索引而走了id的索引。所以这个时候对于怎么加锁需要看具体索引的使用情况，建议使用explain工具查询执行计划判断。

        这里需要注意的是，慢查询导致数据库hang住并不一定是因为竞争锁，慢查询可能因为要更新的记录太多，导致sql迟迟无法完成，而innodb默认32个槽，也就是32个并发工作线程，当32个线程都在运行而无法接收新的sql时，数据就可能被hang住了，而与锁无关。

 

 

参考资料：

《深入浅出MySQL——数据库开发、优化与管理维护》 http://book.51cto.com/art/200803/68127.htm