3、MySQL 原理二

1、锁

在 MySQL 里，根据加锁的范围，可以分为：全局锁、表级锁、行锁

1.1、全局锁

flush tables with read lock
unlock tables    -- 当会话断开了，全局锁会被自动释放

执行后整个数据库就处于只读状态了，这时其它线程执行以下操作，都会被阻塞

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

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样
加上全局锁，意味着整个数据库都是只读状态，如果数据库里有很多数据，备份就会花费很多的时间，备份期间业务只能读数据，而不能更新数据，这样会造成业务停滞

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免

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

因为在可重复读的隔离级别下，即使其它事务更新了表的数据，也不会影响备份数据库时的 Read View
这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据
备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务

这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎
InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库，但是对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法

1.2、表级锁

MySQL 里面表级别的锁有这几种：表锁、元数据锁（MDL）、意向锁、AUTO-INC 锁

1.2.1、表锁

如果我们想对学生表（t_student）加表锁，可以使用下面的命令

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

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

unlock tables

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作
如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句是会被阻塞的，其它线程对学生表进行写操作时也会被阻塞，直到锁被释放

尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁

1.2.2、元数据锁

元数据锁（MDL），我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁
• 对一张表做结构变更操作的时候，加的是 MDL 写锁

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其它线程对这个表结构做了变更

• 当有线程在执行 select 语句（加 MDL 读锁）的期间
  如果有其它线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）
• 当有线程对表结构进行变更（加 MDL 写锁）的期间
  如果有其它线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）

MDL 不需要显示调用，那它是在什么时候释放的

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的

如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交）
那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景

• 首先：线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁
• 然后：线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突
• 接着：线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞

在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务
是否有事务已经对表加上了 MDL 读锁，如果条件允许，可以考虑 kill 掉这个长事务，然后再做表结构的变更

1.2.3、意向锁

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

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁
而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的
不过 select 也是可以对记录加共享锁和独占锁的，具体方式如下

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

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

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突
只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突
表锁和行锁是满足读读共享、读写互斥、写写互斥的

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁
那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录
所以意向锁的目的是：为了快速判断表里是否有记录被加锁

1.3、行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁
普通的 select 语句是不会对记录加锁的，因为它属于快照读
如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读

// 对读取的记录加共享锁
select ... lock in share mode;

// 对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放
所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0

行级锁的类型主要有三类

• Record Lock：记录锁，也就是仅仅把一条记录锁上
• Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

1.3.1、Record Lock

Record Lock 称为记录锁，锁住的是一条记录，而且记录锁是有 S 锁和 X 锁之分的，当事务执行 commit 后，事务过程中生成的锁都会被释放

• 当一个事务对一条记录加了 S 型记录锁后
  其它事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）
• 当一个事务对一条记录加了 X 型记录锁后
  其它事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）

1.3.2、Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象
间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的
即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的

1.3.3、Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身
Next-Key Lock 是包含间隙锁 + 记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的

2、死锁

3、日志

undo log 两大作用

• 实现事务回滚，保障事务的原子性
• 实现 MVCC（多版本并发控制）关键因素之一

为什么需要 redo log

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力
  能够保证 MySQL 在任何时间段突然崩溃，重启后，之前已提交的记录都不会丢失
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能