mysql 面试(四) 整理之mysql 索引 锁

2/13/2017来源:SQL技巧人气:2530

4.1     行锁是怎么实现的,为什么?

InnoDB存储引擎的锁是通过加在索引上面完成的,如果表没有创建索引,InnoDB会自动创建一个6字节的自增索引。 因为Innodb是索引组织表,通过索引去找对应的数据行。

4.2MySQL 锁机制

锁类型分别为:record-lock, gap锁, next-key锁

(1)记录锁:在行相应的索引记录上的锁

(2)gap锁:是在索引记录间歇上的锁

(3)next-key锁:是记录锁和在此索引记录之前的gap上的锁的结合

(4)innodb行锁的加锁方式: 当根据innodb表的索引搜索时, 设置共享锁和排它锁在索引记录上

(5)行锁实际上是索引锁

(6)innodb_locks_unsafe_for_binlog:

当为0时(disabled), 这个开启了gap锁;设置为1,关闭gap锁(这会导致幻读,引起主从同步不一致)。

(7)开启这个选项innodb_locks_unsafe_for_binlog并不关闭gap锁在外键检查方面的作用

(8)在UPDATE和DELETE时,innodb首先对遇到的每一行加行锁;如果innodb_locks_unsafe_for_binlog开启,那么不匹配的行上的锁将被释放;如果未开启,不匹配的行上的锁也不释放,直到事务结束

(9)即使innodb表上没有索引,也会使用内部的clustered index来进行锁定;

(10)innodb除主键的索引之外的其他索引和clustered index在内部是建立一张索引对应表;当利用其他索引扫描记录时,对其他索引加的锁最后都转换为对clustered index加的锁

(11)在UPDATE模式下,对检索中遇到的记录加排它锁;在INSERT...SELECT模式下,对检索中遇到的记录加共享锁;在INSERT模式下,对检索中遇到的记录加排它锁;在DELETE模式下,对检索中遇到的记录加排它锁

(12)在使用unique index进行搜索,并且只返回一行时,不使用gap锁

(13)next-key锁举例:假设索引包括10,11,13,20,则next-key锁为:(negative infinity, 10],(10, 11],(11,13],(13, 20],(20, positive infinity)

(14)使用next-key锁可以预防幻读

(15)gap锁在read_committed下或当innodb_locks_unsafe_for_binlog=on时被关闭;当在这种情况下时,不匹配的行上的锁将被释放