mysql学习篇-锁篇

mysql有那些锁

在mysql 里面有三类锁，他们锁的粒度是不通的，分为全局锁，表级锁和行级锁。

全局锁

全局锁的命令是：

flush table with read lock

执行完后，整个数据库就都处于只读状态了，这时其他现在一下的操作都会阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句。

那么我们该如何释放他呢？

unlock tables

使用场景:
全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁会带来什么缺点？
如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

如何避免呢
如果数据库引擎支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建read view，然后整个事务执行期间都在用这个read view，而且由于mvcc的支持，备份期间业务依然可以对数据进行更新操作。

表级锁

• 表锁：

# 表级别的共享锁，也就是读锁
lock tables table_name read;
# 表级别的独占锁（排他锁），也就是写锁
lock tables table_name write;

排他锁：也就是说一个事务对表加了写入锁后，其他事务不能读取也不能写入表，其它事务的读写操作都会被阻塞。
共享锁：一个事务对表加入了读取锁以后，其他事务可以读取，但是不能写入，这个保证了读取操作的并发性，但是写入操作会阻塞。

如何释放表锁：

unlock tables

• 元数据锁
  元数据锁是由系统自动管理的，用户无法直接设置和释放，当你执行一个操作的时候，mysql也会自动获取必要的元数据锁，以确保数据的一致性和完整性。
  • 对一张表进行CURD的时候，mysql给数据加的是MDL的读锁
  • 对一张表的表结构进行修改的时候，mysql加的是MDL的写锁

注意：
MDL是在事务提交之后才会释放，这意味着事务执行期间，MDL是被一直持有的状态。
如果MDL被一直持有不释放，那么当有人做表结构修改的时候，这时候做alter操作就会进入阻塞状态，写入操作阻塞以后，那么后续的CURL操作也会阻塞。这时候除非一开始的事务将mdl锁释放，不然后续就会造成死锁。

• 意向锁
  • 在使用innodb引擎的表里对某些记录加上【共享锁】之前，需要在表级别加上一个【意向共享锁】；
  • 在使用innodb引擎的表里对某些记录加上【独占锁】之前，需要在表级别加上一个【意向独占锁】；

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

意向共享锁和意向独占锁是表级别的锁，不会和行级共享锁和独占锁发生冲突，而且意向锁之间不会发生冲突，只会和共享表锁的（共享锁和排他锁）发生冲突。

• auto-inc 锁
  auto-inc锁是一个特殊的锁，是用于保护auto_incrment列的并发插入操作。
  当一个事务尝试插入一行，并且需要生成一个新的auto_incrment的值的时候，innodb存储引擎会自动获取auto_inc锁。
  auto-inc锁的主要目的是为了保证auto_incrment的值的连续性和唯一性。但是，这种锁机制可能会降低并发插入的性能。

innodb下对auto_inc锁提供了3种模式：

1. 0/TRADITIONAL：默认模式，这种模式也是最安全的模式，innodb会在插入语句开始的时候获取aoto_inc锁，并在插入完成后释放。这是为了保证auto_incrment的连续性。
2. 1/CONSECUTIVE：这种模式下，innodb只在生成auto_incrment值的时候短暂的获取auto_inc锁，然后立即释放，这可以保证插入的高并发性能，但可能会导致auto_incment的值的不连续性。（这个模式在插入的时候，只会获取主键自增）
3. 2/INTERLEAVED：这是高性能模式，但也是最不安全的模式，这种模式下innodb不会获取auto_inc锁，而且允许每个事务自己生成auto_incrment的值。这也是进一步的提高了并发插入的能力。但会导致auto_incrment值的严重不连续或重复。

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';  -- 查看当前值
SET GLOBAL innodb_autoinc_lock_mode = 1;         -- 设置新值

行级锁
innodb引擎是支持行级锁的，MyISAM引擎是不支持行级锁的。

普通select是不会对记录加锁的，因为他属于快照读，如果我们想对查询记录加行锁，可以使用一下两种形式

// 对读取记录加共享锁
select ... lock in share mode;
// 对读取记录加排他锁
select ... for update;

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

共享锁（s）：也被称为读锁，多个事务可以在同时在同一行数据加共享锁，它们可以读取该行数据，但不能修改。如果一个事务对一行数据加了共享锁，其他事务就不能对该行数据加排他锁
排他锁（x）：也被称为写锁，如果一个事务对一行数据加了排他锁，其他事务不能对该行数据加任何锁，它既不能读也不能写。

Record Lock 记录锁
Record Lock被称为记录锁，锁住一条记录。
共享锁（s）：当一个事务对一条记录加了共享锁，其他事务也可以继续对该条记录加共享锁，但是不可以对该记录加排他锁（X）。
排他锁（x）：当一个事务对一条记录加了排他锁，其他事务即不可以加共享锁，也不可以加排他锁。

mysql > begin;
mysql > select * from t_test where id = 1 for update;

我们对当前数据加里排他锁，这时候我们就不可以对这条数据进行修改。

Gap Lock 间隙锁
间隙锁，只存在与可重复读的隔离级别，目的是为了解决可重复度隔离级别下的幻读。
间隙锁锁的是一个范围数据但不包括它本身，使用了间隙锁以后无法在该区间内插入数据。

表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
间隙锁存在共享锁和排他锁但是没有什么区别，间隙锁之间是兼容的，即两个事务可以同事持有包含共同间隙范围的间隙锁，并不互斥，因为间隙锁的目的是防止插入幻影记录而提出的。

SELECT * FROM table WHERE index BETWEEN 1 AND 4 FOR UPDATE;

Next-Key Lock 临键锁
Next-Key Lock是 Record lock （记录锁）+ Gap lock （间隙锁）的组合，，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。
所以 next key lock （临键锁）既能保护记录，又能防止其他事务将记录插入到被保护记录前面的间隙中。
next key lock 是包含间隙锁+记录锁的，如果一个事务获取的是共享型（x）的next key lock，那么另外一个事务在获取相同范围的 共享型（x）的next key lock时，也会被阻塞。

插入意向锁：
一个事务在插入一条记录的时候，需要判断插入位置是否已经被其它事务加了间隙锁（next key lock）。
如果有，插入就会阻塞，直到拥有间隙锁的哪个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。
举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态，此时事务 B 就会发生阻塞，直到事务 A 提交了事务。
插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。
间隙锁锁的是一个区间，那么插入意向锁，锁的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。