MySQL锁

MySQL锁

全局锁

FTWRL

加锁操作

flush tables with read lock

解锁操作

unlock tables

场景

• 全库逻辑备份

表级锁

表锁

• 加锁

  //表级别的共享锁，也就是读锁；
  lock tables t_student read;

  //表级别的独占锁，也就是写锁；
  lock tables t_stuent wirte;

• 解锁

  unlock tables

• 当会话退出后，也会释放所有表锁

元数据锁（MDL）

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
  对一张表做结构变更操作的时候，加的是 MDL 写锁
• 事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的

意向锁

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」
  在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

  //先在表上加上意向共享锁，然后对读取的记录加共享锁
  select … lock in share mode;

  //先表上加上意向独占锁，然后对读取的记录加独占锁
  select … for update;

• 注意：意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突

• 目的：为了快速判断表里是否有记录被加锁

AUTO-INC锁

• 为某个字段声明 AUTO_INCREMENT 属性时，之后可以在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值，这主要是通过 AUTO-INC 锁实现的

• 锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放

• MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增

  • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁；
  • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁（性能最高，但在主从复制场景中是不安全的）；
  • 当 innodb_autoinc_lock_mode = 1，这个是默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就采用 AUTO-INC 锁

行级锁

Record Lock

• 记录锁，仅仅把一条记录锁上

Gap Lock

• 间隙锁，锁定一个范围，但是不包含记录本身

Next-Key Lock

• Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

• 唯一索引等值查询

  • 当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」
  • 当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」

• 非唯一索引等值查询

  • 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。

    • 先会对普通索引加上 next-key lock
    • 还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止

  • 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁

• 唯一索引范围查询

  • 当查询记录存在时，next-key lock 会退化成「记录锁」
  • 由于范围查询，还会继续往后查找存在的记录，然后加next-key lock，如果查询记录不满足，则会退化成间隙锁

• 非唯一索引范围查询

  • 普通索引范围查询，next-key lock 不会退化为间隙锁和记录锁