6.1 锁

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

6.1 锁

（1）锁的类型

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

（2）锁的算法

• 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)
  • 设计Next-Key Locks的目的是为了解决幻读

（3）如何加锁

Ref：https://www.cnblogs.com/crazylqy/p/7611069.html，顺便提一下，这篇文章也TQL！

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

• SQL1：select * from t1 where id = 10;       答案：所有读操作均不加锁（除非串行化），用MVCC

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

1. 读已提交（RC）隔离级别：RC的情况下全部采用Record Locks
   1. id是主键索引：
      1. 对id=10的那一行加X锁
   2. id是唯一索引：假设主键索引是name，存在字段(id=10,name='d')
      1. 不需要回表查询：加一个X锁，只对id=10的那一行加锁
      2. 需要回表查询：加两个X锁，对唯一索引id=10的那一行加锁，同时对主键索引name='d'的那一行加锁
   3. id是非唯一索引：假如有n条id=10的记录，即存在字段(id=10,name='b'), (id=10,name='d')...
      1. 不需要回表查询：加n个X锁在非唯一索引上
      2. 需要回表查询：加n个X锁在非唯一索引上，同时加n个X锁在对应的主键索引上
   4. id没有索引：没有索引——>走主键索引，进行全表扫描，对主键索引每一行都加锁（即对表的每一行加锁，索引个数 = 行数）
      1. MYSQL优化：对于不满足条件的记录，会在判断后立刻放锁。最终持有的，是满足条件的记录上的锁。
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. 结论：个人感觉next-key只是一种概念，实际手段即 record lock + gap lock
      5. 总结：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
   4. id没有索引：走主键索引进行全表扫描
      1. 所有记录和间隙都要被上锁！假如n行记录，则n个X锁，n+1个gap锁。除了快照读，任何操作都不能被执行，全表锁死。
      2. 可以通过触发semi-consistent read优化，但是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：查看锁信息

（5）死锁问题

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