06.全局锁、表锁、行锁

Mysql中的锁

全局锁：对整个数据库实例加锁

• 全库逻辑备份：
  1. 针对MyISAM做全库逻辑备份，只能用FTWRL方式，在备份时不能更新
     • 补充：MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，破坏了数据一致性
  2. 针对innodb备份，可以使用mysqldump –single-transaction 在备份时可以更新
     • 补充：通过启动一个事务来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的
• 全库只读设置：
  1. Flush tables with read lock
     • FTWRL，连接周期内有效，如果连接断开会自动释放库锁
  2. set global readonly=true
     • 全局生效，连接断开也不会自动修改readonly状态
     • 常用来做主从库判断
     • 备份操作不建议使用，如果备份客户端断开连接，整个库会一直维持不可写状态
• 备份补充说明：
  • 物理备份：使用binlog
    • binlog记录了对数据库执行的所有更改，增量数据
    • 日志格式是二进制
  • 逻辑备份：使用mysqldump
    • 提供数据库对应时间点的完整的数据快照，当前时间点的全量数据

    • 格式是快照形式

      表级锁：针对数据表加锁

1. 表级锁包括：
   • 表锁
   • 元数据锁（meta data lock/MDL锁）
   • 意向锁
   • AUTO-INC锁
2. 表级锁说明：表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作
3. 使用时机：Innodb锁的是索引优先使用MDL锁，如果更新、删除条件没走索引，会降级成表锁
4. 表锁：
   1. 表级别的共享锁/读锁：lock tables T read
   2. 表级别的独占锁/写锁：lock tables T write
   3. 释放：unlock tables 或者 连接断开时会自动释放锁
5. MDL锁：
   • 支持版本 >= MySQL5.5, 不需要显式的使用 MDL
   • MDL锁的目的：为了表结构的一致性设计的
     1. 防止CURD期间变更结构，导致不一致
     2. 防止变更结构期间，执行CURD，导致不一致
   • MDL锁分为：
     • MDL读锁：对表做增、删、改、查操作（DDL）的时候添加
     • MDL写锁：对表结构变更操作（DML）的时候添加
     • 释放：MDL直到事务提交commit/rollback才释放
     • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
     • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。 ´
   • DDL：增、删、改、查 SQL语句
   • DML：修改表结构语句
   • 锁表的例子：
     • MDL申请失败导致锁表的例子：
       • 申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。如果出现 MDL 写锁获取阻塞，该操作会同时阻塞后续获取MDL读锁，即后续该表的所有CRUD 操作都会阻塞
       • 对有访问的数据库做DML操作
         • DDL会占用MDL读锁，如果事务一直不提交，对应读锁将一直得不到释放。因为读锁共享，其他DDL期间申请读锁不会阻塞。
         • 如果执行DML，因为读写锁互斥，会导致申请写锁阻塞
         • 因为写锁的原因，后续的DDL再申请读锁不再成功，从而导致整个表不可用。
       • 解决方案：
         • 针对1，避免DDL长事务
           • 长事务不提交会长期占用锁，从而导致其他事务申请MDL锁失败
           • 长事务还会导致回滚段内存占用不释放
         • 针对2，对DML设定等待时间
           • 如果时间内无法申请到MDL写锁，则重复该步骤，而不是一直阻塞等待
           • 具体语法：NOWAIT/WAIT N
           • e.g. ALTER TABLE tbl_name WAIT N add column ...
     • DML执行时间过长，导致锁表的例子：
       1. 表数据超大
       2. DML拿着MDL写锁，一直执行
       3. 其他事务执行DDL申请MDL读锁一直申请不到阻塞，导致失败
       4. 解决方案：
          • 避免表数据超大
            • 升级mysql版本到8.0
6. 意向锁：
   • 设计的目的：协调行级锁和表级锁之间的关系。（为了快速判断表里是否有记录被加锁）
   • 意向锁的含义：表明事务打算在表的某些行上获取锁
   • 存在意向锁，可以快速判断一定存在行锁。（这样就不需要通过遍历行记录来判断是否有行锁）
   • 加锁时机：
     • InnoDB引擎的表里，对某些行记录加上共享锁之前，则需要先在表级别加上一个意向共享锁；
     • InnoDB引擎的表里，对某些行记录加上独占锁，则需要先在表级别加上一个意向独占锁；
   • 对某些行记录加共享锁的触发条件：
     • select 添加共享锁

        select ... lock in share mode;

   • 对某些行记录加独占锁的触发条件：
     • 增、删、改语句操作
     • select 添加互斥锁

         select ... for update;

   • 意向锁和表锁兼容性：

   • 兼容性	X	IX	S	IS
     X	不兼容	不兼容	不兼容	不兼容
     IX	不兼容	兼容	不兼容	兼容
     S	不兼容	不兼容	兼容	兼容
     IS	不兼容	兼容	兼容	兼容

7. AUTO_INC锁：
   • 设计目的：使用AUTO_INC锁，数据库实现自增主键的自动赋值递增的逻辑
   • 释放时机：不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放
   • 弊端：大量数据进行插入的时候，因为锁存在，会影响插入性能
   • 工作机制：
     • 在插入数据时，MySQL 会加一个表级别的 AUTO-INC 锁
     • 然后会为被 AUTO_INCREMENT 修饰的字段赋递增的值
     • 等插入语句执行完成后，才会把 AUTO-INC 锁释放掉
   • 轻量级的锁： > MySQL 5.1.22
     • 工作机制：
       • 在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，
       • 然后给该字段赋值一个自增的值
       • 然后就把这个轻量级锁释放了，** 不需要等待整个插入语句执行完**

     • 注意事项：当innodb_autoinc_lock_mode = 2，开启轻量锁，此时binlog日志格式不能设置为statement，而要使用row，否则会出现自增主键数据不一致的情况

       行锁：针对数据表中行记录的锁

8. 引擎差异：
   • MyISAM 不支持行锁
     • 只能用表锁来实现并发控制
     • 同一时间单个表只能有一个更新
     • 并发度低
   • Innodb支持行锁
     • 并发度高
9. 两阶段锁协议：
   • 在InnoDB事务中，行锁是在需要的时候才加（具体sql执行时）
   • 在事务结束时才释放（commit/rollback）。
   • 即使中间不需要锁，也不会自动释放
10. 最佳实践：
    • 避免锁冲突和并发占用过长时间
      • 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
11. 死锁和死锁检测：
    • 死锁出现时机：当多个事务出现循环依赖，导致所有事务都在等待。（即饿死现象）
    • 死锁的解决方案：
      1. 设置超时时间：innodb_lock_wait_timeout=X
         • 缺点：超时时间不太好把控，过长过短都不行，innodb默认50s
      2. 开启死锁检测：innodb_deadlock_detect=on
         • 缺点：死锁检测要耗费大量的CPU资源，需要慎重开启
    3. 最佳实践：
       1. 确定不会出现死锁的业务，可以考虑关闭死锁检测
       2. 控制并发程度，降低死锁检测成本。具体做法是使用dbproxy控制并发线程数量或者直接修改mysql源码

       3. 从该行数据的设计上考虑，拆解成多行。但是这需要结合业务

行锁分类：

• Record Lock：记录锁，也就是仅仅把一条记录锁上
  • 记录锁: 有S锁和X锁的区分
  • 发生时机：

    • 增、删、改会对行记录加行锁

    • 普通SELECT查询时都是快照读，不加锁

    • 特殊的SELECT查询会对记录加行锁: ``` //对读取的记录加共享锁/S锁 select … lock in share mode;

      //对读取的记录加独占锁/X锁 select … for update; ```

• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身
  • 作用：只存在于可重复读隔离级别，是为了解决可重复读隔离级别下幻读的现象
  • 间隙锁: 存在S锁和X锁区分，但没区别。且多个事务可以同时拥有间隙锁，获取锁操作不互斥。（因为间隙锁是为了防止对应区间插入）
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身，区间范围：左开右闭
  • 作用：即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中
  • 存在S锁和X锁区分
  • 仅S锁之间不互斥，SX、XX获取是互斥的(因为Record Lock的原因)
• 插入意向锁：插入位置没有记录，为了解决幻读问题，同时尽可能降低影响范围
  • 当插入操作INSERT被间隙锁阻塞时，会申请插入意向锁(此时插入意向锁是等待状态，意味着并未获取到)，等待间隙锁释放后，插入意向锁变成正常状态，成功获取到插入意向锁，然后执行插入操作。
  • 如果没有插入意向锁? 那么只能使用间隙锁，锁粒度太粗，对同一个间隙的插入操作必须逐个进行；而插入意向锁可以控制锁的粒度只在插入点上
  • 为什么叫插入意向锁而不是插入锁？个人理解因为插入意向锁只表示期望插入，在阻塞期间实际未真正拿到锁。
  • 作用：确保间隙锁释放后，执行当前的插入操作。这些事务按顺序执行。
  • 插入意向锁并不是意向锁（表级别锁），它是一种特殊的间隙锁，属于行级别锁
  • 插入意向锁锁住的是一个插入点，间隙锁是点和点之间的区间
  • 插入意向锁之间不互斥（非唯一索引情况下，即使是同一个插入点也不互斥），插入意向锁和间隙锁互斥

行锁补充：

• MySQL加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁
• Mysql加行锁的对象是索引，加锁的基本单位是 next-key lock
• 锁是在遍历索引的时候加上的，并不是针对输出的结果加锁
• next-key look 退化成Record lock、Gap lock：
  • 在能使用Record lock、Gap lock就能避免幻读现象的场景下
  • 不可以针对不存在的记录加记录锁，此时会退化成Gap lock
  • 锁区间如何确定：
    • 唯一索引、主键索引：
      • 确定锁定区间按照索引扫描的方向（从小到大）
      • 等值查询：
        • 值存在，记录锁
        • 值不存在，值对应索引上一条记录，下一条记录的间隙锁
      • 范围查询：
        • 考虑前后的间隙，考虑等值临界点是否可以不锁
    • 非唯一索引、二级索引：
      • 确定锁定区间按照索引扫描的方向（从小到大）
      • 等值查询：
        • 需要基于索引扫描到所有的符合等值的行记录，添加next-key lock
        • 等值存在间隙锁时，进行等值插入：
          • 再基于插入值的主键是否存在间隙锁，判断插入是否成功
      • 范围查询：
        • 索引的 next-key lock 不会有退化为间隙锁和记录锁,主键依然会退化
    • 无索引：
      • 当前读查询语句、增、删、改都会触发全表扫描，全记录都会加 next-key lock，等价于锁住全表
      • 在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引
• 查看当前引擎的锁状态：select * from performance_schema.data_locks\G; (mysql >= 8.0)
  • LOCK_MODE： 判断是何种锁：以下只考虑写锁情况
    • 如果 LOCK_MODE 为 X，说明是 next-key 锁；
    • 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
    • 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；
    • 如果 LOCK_MODE 为 IX, 说明X型意向锁，属于表锁；
    • 如果 LOCK_MODE 为 X,GAP,INSERT_INTENTION，说明是插入意向锁
  • LOCK_TYPE: 判断是表锁还是行锁：
    • RECORD：行锁
    • TABLE：表锁
  • LOCK_STATUS：判断当前锁状态
    • GRANTED：获取
    • WAITING：等待
  • LOCK_DATA：判断锁定的行数据
    • 如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围右边界
    • 而锁的左边界需要通过当前索引找到上一个记录
  • INDEX_NAME：判断是对哪个索引加锁
  • e.g. mysql> select THREAD_ID,LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks; +-----------+-----------+------------+-----------+-------------+------------------------+ | THREAD_ID | LOCK_TYPE | INDEX_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+-----------+------------+-----------+-------------+------------------------+ | 52 | TABLE | NULL | IX | GRANTED | NULL | | 51 | TABLE | NULL | IS | GRANTED | NULL | | 51 | RECORD | PRIMARY | S | GRANTED | supremum pseudo-record | | 51 | RECORD | PRIMARY | S | GRANTED | 21 | | 51 | RECORD | PRIMARY | S | GRANTED | 1 | | 51 | RECORD | PRIMARY | S | GRANTED | 15 | | 51 | RECORD | PRIMARY | S | GRANTED | 5 | | 51 | RECORD | PRIMARY | S | GRANTED | 10 | | 51 | RECORD | PRIMARY | S | GRANTED | 20 | +-----------+-----------+------------+-----------+-------------+------------------------+