MySQL InnoDB锁讲解

1. MySQL锁分类：

　　1)、根据锁粒度分为：全局锁(锁数据库，Server层)、表级锁(锁某个表，Server层)和行级锁(锁某行数据，引擎层，如InnoDB)

　　2)、根据锁功能分为：共享锁(Shared Locks，S锁)和排他锁(Exclusive Locks，X锁)；S锁允许其他事务再加S锁，而不能加X锁，select ...... lock in share mode；X锁不允许其他事务再加任何锁，select ...... for update

2. 全局锁：

　　1)、数据库实例加锁，整库处于只读状态，数据和表定义的修改都会被禁止，操作命令为：flush tables with read lock;

　　2)、解锁命令为：unlock tables; 注意解锁命令要在加锁的会话中进行操作，或者关闭加锁操作的会话，解锁才能生效。

　　3)、全局锁比较危险，不建议使用。如果备份数据库建议使用如InnoDB这样的支持事务的引擎，用工具mysqldump，加参数--single-transaction，可以利用MVCC提供的一致性视图，不使用全局锁，在不影响业务正常运行下进行备份；而对于不支持事务的引擎，可以通过工具mysqldump加参数--lock-all-tables，加全局锁获得一致性视图。

3. 表级锁分为表读(写)锁、元数据锁(meta data lock，MDL)、意向锁(Intention Locks)、自增锁(AUTO-INC Locks)。表test含有列id,a,b,c,d。

　　1)、表读(写)锁：

　　　　- 读锁为共享锁，写锁为独占锁，加表读锁命令lock table test read;加表写锁命令lock table test write;也可以写成lock tables test read;或lock tables test write;

　　　　- 解锁命令为unlock tables;注意解锁命令要在加锁的会话中进行操作，或者关闭加锁操作的会话，解锁才能生效；

　　　　- 多个会话间，读、读可以同时加锁，读、写或写、写不可以同时加锁；

　　　　- 命令show open tables;字段Table和In_use可以查看对应表的锁个数。

　　2)、元数据锁(meta data lock，MDL)：不需要显示使用，当访问表时会自动加上。当对表数据进行操作时加MDL读锁，多个MDL读锁可共存，这样在不影响数据操作的情况下保护表定义不被修改；当对表定义进行操作时会加MDL写锁，这种情况下不能操作数据，当然其他事务也不能修改表定义。演示可以在一个会话中开启事务并操作数据(如，begin;select * from test;)，在提交前，另一个会话进行表定义修改是不成功的，只有前一个事务提交或回滚后，修改表定义才可以成功。

　　3)、意向锁(Intention Locks)：

　　　　- 这是InnoDB引擎实现的表级锁，是为了全表更新数据时或者加其他表锁时性能提升，避免全表遍历排查某行数据是否加了行锁，对加了行锁的表，加上意向锁标识；

　　　　- 意向锁分为意向共享锁(IS锁)和意向排他锁(IX锁)，意向锁之间不互相排斥，只是作为表锁的标识，如表中多行记录可以既有共享锁和排他锁，那么导致表的意向锁就会既有IS锁和IX锁，这种意向锁就是为了给表加锁时可以更快的判断能加哪种锁。

　　4)、自增锁(AUTO-INC Locks)：对于表中使用自增列时才用到，如自增主键，防止值冲突。

4. InnoDB行级锁：通过给索引的索引项加锁实现的，所以只有通过索引条件检索的数据，InnoDB才能加行锁，否则将使用表锁。根据锁定范围分为记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)和插入意向锁(Insert Intention Locks)。按照锁功能分为共享锁(S锁)和排他锁(X锁)，S锁与其他事务S锁可共存，与其他事务X锁排斥；X锁与其他事务任何锁排斥。对于insert、update、delete语句，InnoDB会自动给涉及到的记录加X锁，对于普通select语句不会加任何锁，除非显示使用加锁，如加S锁：select ...... lock in share mode;加X锁：select ...... for update。注意普通select不会加任何锁，所以任何情况下都可以执行，如对于加了X锁的记录id=1，select * from test where id=1可以执行，而select * from test where id=1 lock in share mode就阻塞。

　　1)、记录锁(Record Locks)：锁定索引中一条记录。其实锁住的是索引(如果是主键索引，就锁住主键索引，如果是辅助索引，则锁住辅助索引和主键索引)，如果检索记录没有用到索引，那么就会将表的所有聚簇索引记录都锁定，表现为表锁；

　　2)、间隙锁(Gap Locks)：是一种区间锁，锁住的是索引开区间，即不包括两端，也就是不包括索引记录本身；间隙锁可以防止幻读，即在当前事务增删改中，用到间隙锁后，其他事务便无法加上间隙记录的X锁，也就不能增删改间隙记录；

　　3)、临键锁(Next-Key Locks)：锁定索引区间为左开右闭区间的锁，默认情况下，InnoDB用临键锁锁定增删改和select ...... for update操作记录。InnoDB会根据不同SQL操作对临键锁进行优化：

　　　　- 当主键索引等值查询且有记录时，临键锁会降级为记录锁，即锁住记录本身，而不是范围；

　　　　- 当辅助索引等值查询且有记录时，临键锁会锁住等值记录的主键索引项和辅助索引项，同时会加间隙锁锁住辅助索引区间项

　　　　- 当等值查询无记录时，会降级为间隙锁；

　　　　- 当范围匹配时，就使用临键锁，即Record Lock + Gap Lock。

　　4)、插入意向锁(Insert Intention Locks)：是一种Gap Lock，不是意向锁，在insert操作时产生；插入意向锁不会阻止任何锁，对插入的记录进行锁定，防止其他事务插入相同主键记录，而不同主键记录之间的插入互不影响。

5. 行锁加锁规则：

　　1)、主键索引：

　　　　- 等值查询：命中记录加记录锁；未命中加间隙锁；

　　　　- 范围查询：命中记录，where条件的临键区间加临键锁；未命中加间隙锁。

　　2)、辅助索引：

　　　　- 等值查询：命中记录辅助索引项和主键索引项加记录锁，如果是RR隔离级别，要避免幻读，辅助索引项两侧加间隙锁；未命中加间隙锁；

　　　　- 范围查询：命中记录，where条件的临键区间加临键锁，命中记录的主键索引项加记录锁；未命中加间隙锁。

6. RC隔离级别下，对索引的等值查询且有记录锁定的是记录，非索引的等值查询则会对所有记录锁定；RR隔离级别下，对主键索引的等值查询且有记录锁定记录，对辅助索引等值查询且有记录锁定记录和索引项间隙，对非索引的等值查询则会锁定所有记录和间隙。

7. 避免死锁：程序处理逻辑上，不同事务操作表的顺序要一致，如事务A操作表顺序是先表a后表b，那么事务B操作也要是先表a后表b；保持事务轻量，越早越快提交事务，就越能早释放锁。