MYSQL复习——第六章：锁

6.1 锁

• 锁的类型（从粒度分类）
  • 行锁（都是悲观锁）
    • 共享锁（S Lock）：读锁
    • 排他锁（X Lock）：写锁
  • 表锁：Innodb意向锁
    • 意向共享锁（IS Lock）：事务打算给数据行加行共享锁，事务在给一个数据行加锁前必须获得该表的IS锁
    • 意向排他锁（IX Lock）：事务打算给数据行加行排他锁，事务在给一个数据行加锁前必须获得该表的IX锁
  • 页锁
• 乐观锁vs悲观锁
• 锁的算法（Innodb）
  • Record Lock：单个行记录上锁
  • Gap Lock：间隙锁，锁范围，开区间
  • Next-Key Lock：Record + Gap，锁范围，左开右闭
• 如何加锁（重点）
• 其他
  • 外键和锁
  • ICP问题
  • 显示地关闭Gap Lock
  • 查看加锁方法
  • Innodb的RR到底能不能解决幻读
• 死锁

6.1 锁

（1）锁的类型

• 行锁（都是悲观锁）
  • 共享锁（S Lock）：读锁
  • 排他锁（X Lock）：写锁
• 表锁：Innodb意向锁
  • 意向共享锁（IS Lock）：事务打算给数据行加行共享锁，事务在给一个数据行加锁前必须获得该表的IS锁
  • 意向排他锁（IX Lock）：事务打算给数据行加行排他锁，事务在给一个数据行加锁前必须获得该表的IX锁
  • 理解
    • 如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突
    • 如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果
• 页锁

（2）乐观锁vs悲观锁

• 悲观锁：通常都通过排它锁占住资源，并发量大的情况下性能开销大
• 乐观锁：大多是基于数据版本（ version ）记录机制实现。一般是通过为数据库表增加一个 version 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

（3）锁的算法

• Record Locks（记录锁，加在索引记录上）
• Gap Locks（间隙锁）：开区间
• Next-Key Locks：闭区间，左开右闭
  • 假设一个索引包含以下几个值：10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间：
    • (negative infinity, 10]
    • (10, 11]
    • (11, 13]
    • (13, 20]
    • (20, positive infinity)
  • innodb设计Next-Key Locks的目的是为了解决幻读

（3）如何加锁

Ref：https://www.cnblogs.com/crazylqy/p/7611069.html

假如存在三条SQL语句，如何加锁？

• SQL1：select * from t1 where id = 10; 

  • 答：在RR,RC隔离级别下，所有普通读操作均不加锁，用MVCC

  • 答：在串行化下，所有普通 select 都会自动转化成 select ... lock in share mode 加共享锁

• SQL2:  select * from t1 where id = 10 lock in share mode

  • 答：因为显示地加了lock in share mode，所以在RR,RC,串行化下，都加共享锁

• SQL3：delete from t1 where id = 10;        答案：分多种情况，见下

1. 读已提交（RC）隔离级别：RC的情况下全部采用Record Locks
   1. id是主键索引：
      1. 对id=10的那一行加X锁
      2. 结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可
      3. 
   2. id是唯一索引：假设主键索引是name，存在字段(id=10,name='d')
      1. 不需要回表查询：加一个X锁，只对id=10的那一行加锁
      2. 需要回表查询：加两个X锁，对唯一索引id=10的那一行加锁，同时对主键索引name='d'的那一行加锁
      3. 结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录
      4. 
   3. id是非唯一索引：假如有n条id=10的记录，即存在字段(id=10,name='b'), (id=10,name='d')...
      1. 不需要回表查询：加n个X锁在非唯一索引上
      2. 需要回表查询：加n个X锁在非唯一索引上，同时加n个X锁在对应的主键索引上
      3. 结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁
      4. 
   4. id没有索引：由于没有索引，因此只能走主键索引，进行全表扫描：对主键索引每一行都加锁（即对表的每一行加锁，索引个数 = 行数）
      1. MYSQL优化：在实际中有优化，对于不满足条件的记录，会在判断后立刻放锁。最终持有的，是满足条件的记录上的锁
      2. 结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束
      3. 
2. 可重复读（RR）隔离级别：对于主键索引和唯一索引，Next Key Locks会锁降级为Record Locks
   1. id是主键索引：
      1. 同情况1.1，与RC相同
   2. id是唯一索引：
      1. 同情况1.2，与RC相同，加一个X锁或两个X锁
   3. id是非唯一索引：假设找到3条符合的行 (id=10,name='b'), (id=10,name='c'), (id=10,name='e')
      1. 不需要回表查询：在非唯一索引上共加4个gap锁，3个lock行锁
      2. 需要回表查询：在非唯一索引上共加4个gap锁，3个lock行锁（X锁）；在主键索引上加3个X锁
      3. 
      4. 
      5. 结论：个人感觉next-key只是一种概念，实际手段即 record lock + gap lock
      6. 总结：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
      7. 这里讲得不是很清楚：具体看Ref：https://www.cnblogs.com/crazylqy/p/7611069.html
   4. id没有索引：走主键索引进行全表扫描
      1. 所有记录和间隙都要被上锁！假如n行记录，则n个X锁，n+1个gap锁。除了快照读，任何操作都不能被执行，全表锁死。
      2. 可以通过触发semi-consistent read优化（对于不满足查询条件的记录，MySQL提前放锁），但是semi-consistent read本身会存在其他问题，不建议使用
      3. 
3. 串行化（Serializable）隔离级别
   1. 所有的写操作的加锁和RR级别完全一致
   2. 所有的读操作都要加锁，不再是MVCC快照读

（4）其他

• （4.1）显示地关闭Gap Lock（两种方式）
  • 将隔离级别变成RC，读已提交
  • 将参数innodb_locks_unsafe_for_binlog设置为1
• （4.2）ICP（Index Condition Pushdown）问题（SQL>  5.6）
  • ICP：比如delete from t1 where a >1 and b ='aaa'，其中辅助索引为(a,b)。
    • 没有ICP优化地情况下，先会去辅助索引，然后在Mysql Server层进行 b='aaa'的过滤
    • 有ICP的情况下，会在搜索辅助索引的层面上就进行 b='aaa'的过滤
  • 以上述语句为例，在RR的隔离级别下
    • 如果有ICP，则不满足 b='aaa' 的主键索引不需要加X锁，在（3）中讨论的加锁算法与有ICP逻辑相同
    • 有过没有ICP，主键索引中a>1的行都需要加X锁
• （4.3）外键和锁
  • 在Innodb中，如果没有显示地对外键列加索引，Innodb会自动加一个索引，这样可以避免表锁
  • 对外键更新或者插入时，首先需要select父表，对父表加S锁。此时使用的是SELECT...IN SHARE MODE方式
  • 为什么需要对父表加S锁，而并非使用一致性非锁定读
    • 如果Session A先开启了事务访问父表，B用一致性非锁定读去读父表，A更改了父表。则B要读的数据可能已经不存在了。
• ​​​​​​（4.4）查看锁的方法
  • INFORMATION_SCHEMA下有INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS三张表
    • INNODB_TRX：显示当前运行的Innodb事务
    • INNODB_LOCKS：查看锁信息
• （4.5）Innodb的RR到底能不能解决幻读
  • 结论：解决了，但是有条件
    • 在标准的事务隔离级别（ANSI SQL-92）定义下，RR是会有幻读问题的，但是Innodb通过间隙锁解决了幻读。

（5）死锁问题

• 死锁的例子
  • 1. 最常见的例子：两个事务互相持有对方的锁
    • 
  • 2. 与MySQL加锁机制相关的例子
    • 见Reference：https://www.cnblogs.com/crazylqy/p/7611069.html
  • 总结：死锁的关键在于：事务之间加锁顺序不一致
• 解决方案：
  • 超时：当两个事务相互等待时，时间超过某一阈值，就进行回滚，由参数innodb_lock_wait_timeout控制
    • 缺点：如果事务很大，回滚需要很多时间
  • wait-for graph（等待图）：Innodb采用的方式
    • 通过锁和事务的关系图，判断是否存在回路。若存在，选择回滚undo量最小的事务
    • 1.2之后通过非递归算法实现，优化了性能