Mysql锁的种类

Mysql锁的种类

1. Mysql锁的种类

• 全局锁

• 表级锁

  • 表锁

  • 元数据锁(MDL)

  • AUTO-INC锁

  • 意向锁

• 行级锁

  • 记录锁(Record Lock)
  • 间隙锁(Gap Lock)
  • 临键锁(Next-Key Lock)
  • 插入意向锁

2.全局锁

1. 加锁

flush tables with read lock

2. 释放锁

unlock tables

3. 作用

对整个数据库加锁。使数据库变成只读的状态，其他线程再执行update insert delete语句，或是对表的结构进行变更(alter table)都会使线程阻塞

全局锁主要应用于对全库的逻辑备份，通过加锁并对所有的表进行select操作来进行数据备份。这样在备份数据库期间，不会因为数据的变更或者表结构的改变而使备份的数据与预期不一样。但是由于数据库在备份中保持只读的状态，使整个业务停摆，我们可以采用开启事务后再进行备份的方式来解决这个问题。

InnoDB中在可重复读隔离级别下 由于MVCC的支持，备份期间其他业务仍可以对库进行操作，并且通过在事务开启时生成的Read View 使得备份的数据与事务开启时的数据是一致的。

Mysql中备份语句为 mysqldump 如果加上 -singletransaction这一参数，就可以实现开启事务后对数据库的备份，但是这种方法只适用于支持可重复读隔离级别下的存储引擎

3.表锁

1.表锁

1. 加锁

lock tables table_name read/write

2. 释放锁

unlock tables 该命令会释放当前session的所有表锁

3. 分类

• 共享表锁(S型) : 一个线程获取到S型锁后，其他线程也可以获取该表的X型锁，但是所有线程对该表只能执行读命令，如果执行update insert delete 或alter table 都会使线程阻塞，直到锁被释放
• 独占表锁(X型) ：一个线程获取到X型锁后，其他线程无法获取该表的X型或S型锁，只有该线程可以对表操作，直到独占锁被释放

但是并不推荐使用这种锁，它的细粒度太大，InnoDB可以支持细粒度更小的锁

2.元数据锁(MDL)

1. 加锁

元数据锁并不是被显示调用的，而是数据库隐式加上的

2. 释放锁

事务提交后就会被释放

3. 类型

• 对一张表进行CRUD操作时，加的是MDL读锁(S型)

• 对一张表结构进行变更时，加的是MDL写锁(X型)

4. 作用

MDL是为了保证当一个线程对表进行CRUD操作时，其他线程不会更改该表的结构(直到MDL读锁被释放)

当一个线程对表结构进行变更时，这张表的数据是没有变化的(直到MDL写锁被释放)

5. 注意

申请MDL锁时会形成一个队列，而且MDL写锁申请的优先级是大于MDL读锁的，也就是如果MDL写锁不被拿到，队列中其他读锁的申请也会被阻塞

举个例子

1. 开启一个事务A执行一条普通select语句，此时该线程持有该表的MDL读锁
2. 开启事务B执行select语句，此时这条select语句并不会阻塞，因为读读是不冲突的
3. 开启事务C执行alter table语句，但是该表的MDL读锁还被占用着，该线程无法申请到MDL写锁，就会使命令阻塞，直到锁被释放
4. 如果该表的读锁一直不被释放，那么在事务C后所有的select语句都会被阻塞，因为申请锁的操作会形成一个队列，并且申请写锁的优先级是最高的，所以其他事务也无法申请到读锁

3.AUTO_INC锁

在Mysql中数据通常是以主键自增(AUTO_INCREMET)的顺序存放的，我们在插入一条数据时不需要指定主键值，Mysql会帮我们进行自增长的操作，这个过程中就少不了AUTO_INC这一表级锁的帮助

1. 加锁

在对表进行插入操作时会自动给该表加上AUTO_INC锁，在该事务持有该锁的过程中其他插入语句就会被阻塞，直到锁被释放

2. 释放锁

自增锁不需要等事务提交后才被释放，而是在insert语句执行结束后就被释放

在Mysql5之后还新增了一种轻量级的自增锁，它在insert语句执行过程中申请到主键后对主键赋完值就被释放

3. 注意

这种轻量级锁同样也有弊端，在执行像insert … select这样的语句时，就会出现主从不一致的现象

举个例子：

• 表t1中有3条数据(1, 1) (2, 2) (3, 3)
• 两条语句insert t2 values(null, 4, 4) 和 insert t2 select * from t1 同时执行

由于这种轻量级锁是在申请完主键就被释放，而不是执行完这条语句就被释放，所以t2的数据可能会变成

(1, 1, 1) (2, 4, 4) (3, 2, 2) (4, 3, 3)这样的数据，如果在binlog的记录格式为statement的情况下，binlog要么先记录第一条insert语句，要么先记录第二条insert，用这个binlog进行主从复制时在从库中这两条语句就会按顺序执行，那么第二条语句插入的三个数据一定是连续的，这就造成了主从数据不一致

但是binlog的记录格式为row的情况下，binlog记录的是真实的记录，这样从库进行复制时，主库存的是什么从库就复制什么

在Mysql中为AUTO_INC提供了三种模式

当innodb_autoinc_lock_mode = 0 时该自增锁在语句执行完被释放

innodb_autoinc_lock_mode = 1时该自增锁变为轻量级锁，在赋完主键值后就被释放

innodb_autoinc_lock_mode = 2 时 普通insert语句使用轻量级锁，而像insert … select这样一次性插入多条数据的语句使用普通自增锁，在这个模式下既能提高并发性能，又能保持主从一致

4.意向锁

1. 加锁

针对普通select语句需要声明 select … lock in share mode，为该表加上意向共享锁

对select … for update这样的语句，会自动加上一个表级别的意向独占锁

2.释放锁

事务结束后自动释放

3.注意

表锁和行锁之间是满足 读写互斥 写写互斥的，但意向锁这种表级锁却不和行锁冲突

原因是，在我们给一张表加普通表锁前，需要判断表中每一条记录是否加有行级锁，但是如果我们在加行锁前加一个表级别的意向锁，那么就不需要对每一条记录进行判断了，直接看该表是否存在意向锁，如果存在就阻塞。

总结： 意向锁的目的就是在加表锁时能够快速判断是否会产生锁冲突

4.行锁

InnoDB中是支持行级锁的，但在MyIsam中不支持

而且普通的select语句是不加行锁的，因为它属于快照读，根据MVCC进行查询

如果要给该记录加锁可以有两种方式

select … lock in share mode 加表级的意向共享锁

select … for update 加的是行级别的独占锁

但是这两条语句一定要放在事务中，因为事务一旦提交锁就会被释放

行锁的分类主要有

• Record Lock 记录锁，锁的是该条记录

• Gap Lock 间隙锁，锁住的是一个范围并不会锁住记录本身，在这个范围内无法进行数据的插入操作

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

1.Record Lock

记录锁是有X型和S型两种类型的

select * from t where id = 1 for update这条语句就会给id = 1这条记录加上一个记录写锁，但是其实这个记录写锁是从Next-Key Lock退化来的

2.Gap Lock

间隙锁只存在于可重复读这一隔离级别下，防止了幻读这一并发问题

假如一条记录持有(2, 4)这一范围内的间隙锁，那么在这一范围内就不会有记录的增加，直到该锁被释放

注意

间隙锁是可以相互兼容的，也就是两个间隙锁的范围是允许重复的，因为持有该锁的目的就是为了防止有幻影记录的产生

3.Next-Key Lock

也叫做临键锁他是记录锁和间隙锁的结合，它锁住的是一个左开右闭的区间，也就是当一条记录持有该锁时不仅锁住的是该记录，也锁住了一个范围

在可重复读隔离级别下，加锁的基本单位就是Next-Key Lock，但是在不同的查询语句中可能会退化成间隙锁或者记录锁

4.插入意向锁

在一个事务插入一条记录时，会先判断大于该记录的第一条语句是否持有间隙锁，如果持有，就会发生阻塞，在此期间就会生成一个该区间的插入意向锁，并将锁的状态设置为等待状态，直到间隙锁被释放，该锁就会被设置为正常状态 (在Mysql中生成锁不代表持有该锁，只有在锁的状态为正常时该锁才生效)

插入意向锁是一个特殊的间隙锁，该间隙锁锁住的是间隙范围内的一个点