mysql的锁和事务机制

1. MySQL的锁

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

1.1 全局锁

1. 加上全局锁，意味着整个数据库都是只读状态。
2. 全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

-- 加锁
flush tables with read lock

-- 释放
unlock tables 

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

• 对数据的增删改操作，比如 insert、delete、update等语句；

• 对表结构的更改操作，比如 alter table、drop table 等语句。

1.2 表级锁

MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

1. 表锁

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

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

// 释放当前会话的所有表锁
unlock tables

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能。

2. 元数据锁（MDL）

对数据库表进行操作时，会自动给这个表加上 MDL。

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

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

3. 意向锁

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

1. 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

2. 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。不过select 也是可以对记录加共享锁和独占锁的，具体方式如下：

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

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




SELECT * FROM users WHERE id = 6 FOR UPDATE;
-- 此时 users 表存在两把锁：users 表上的意向排他锁 和 id 为 6 的数据行上的排他锁。


LOCK TABLES users READ;
-- 1. 此时事务 B 检测事务 A 持有 users 表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁。
-- 2. 那么事务 B 对 users 表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁。
-- 3. 所以意向锁增加了效率

1. 意向锁的目的是为了快速判断表里是否有记录被加锁。
2. 🔥意向锁是一种不与行级锁冲突表级锁🔥，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

4. AUTO-INC 锁

表里的主键通常都会设置成自增的，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

1.3 行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

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

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

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

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

1. 行级锁类型

行级锁的类型主要有三类：

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

1. Record Lock 总是会去锁住索引记录。对表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。
2. 间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。假设表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。
3. 表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

2.RR 用 Next-Key Locking 解决幻读

表 t 由 1、2、5 这三个值组成，对于 SQL 语句 SELECT*FROM t WHERE a＞2 FOR UPDATE，其锁住的不是 5 这单个值，而是对（2，+∞）这个范围加了 X 锁。因此任何对于这个范围的插入都是不被允许的，从而避免 Phantom Problem。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。

而在事务隔离级别READ COMMITTED下，其仅采用Record Lock。

2. MySQL的事务

InnoDB存储引擎中的事务完全符合ACID的特性。

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。

• 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。不能出现用户 A 扣除了 200 元，但用户 B 未增加的情况。

• 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

• 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

2.1 事务的实现

• 持久性是通过 redo log （重做日志）来保证的；
• 原子性是通过 undo log（回滚日志） 来保证的；
• 隔离性是通过锁机制或 MVCC（多版本并发控制） 来保证的；
• 一致性则是通过持久性+原子性+隔离性来保证；

2.2 并发事务问题

在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

1. 脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

违反了事务的隔离性。

2. 不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据（提交是指别的事务 commit）。

RR级别通过只生成一个ReadView解决的不可重复读。

3. 幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

严格来讲“幻读”可以被称为“不可重复读”的一种特殊情况，在 MySQL 官方文档中将不可重复读的问题定义为 Phantom Problem，即幻像问题。

RR级别使用Next-Key Lock算法，避免了幻读的现象。

4. 丢失更新（业务问题）

脏读，幻读，不可重复读是读的问题，丢失更新是写的问题。

mysql丢失更新问题英文叫lost update。指的是两个事务同时更新一条数据，后更新的覆盖了前面更新的结果，从结果上看第一次的更新丢失了的现象。

其实现在数据库本身并没有丢失更新的问题，因为当一个事务更新一条记录时，就会加排他锁，另外一个的更新就会阻塞住。所以丢失更新大多是业务本身的问题。（查出来数据，算后再改）

• 悲观锁解决

begin;
// 所有的会话都并发执行这个SQL，只有一个可以获取 X 锁成功，其他的都等待
select id, name, balance from account where id = 1 for update;
// 假设返回：balance = 100，代码里 balance += 10;  balance = 110
update account set balance = 110 where id = 1;
commit;

• 调整更新策略解决

begin;
// 执行快照读
select id, balance from account where id = 1;
// 这个数据有可能会被其他的并发更新给修改掉，不一定是最新的数据

// 执行update操作，注意这里的不同，是直接对余额做 增/减 操作，
//  利用一致性读视图在update时执行当前读的特点，也就是说如果有多个会话执行 update，其余的会被阻塞
update account set balance = balance + 10 where id = 1;
commit;

2.3 事务隔离级别

类型	备注
Read Uncommitted(RU)未提交读	不加锁
Read Committed(RC)已提交读	指一个事务提交之后，它做的变更才能被其他事务看到；
Repeatable Read(RR)可重复读	指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的。默认级别
Serializable(SE) 串行化	会对记录加上读写锁

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，使用Next-Key Lock锁的算法，很大程度上避免幻读现象。

2.4 RR 特殊场景下的幻读 [不重要]

在RR 级别下，其实MySQL Innodb 中的 MVCC 并不能完全避免幻读现象，例如下面有些违和的场景。

场景1. 先改再查

事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id。

之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

场景2. 先快照读后当前读

例如先 select * from t_test where id > 100 得到了 3 条记录，别的事务插入一条数据。

再 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

3. 快照读和当前读

3.1 快照读（普通 select 语句）

RR 级别 通过 MVCC 方式解决了幻读。

因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。此外，读取快照数据是不需要上锁的，极大地提高了数据库的并发性。

3.2 当前读（select … for update 等语句）

update、insert、delete 也是当前读。

RR 级别 通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

3.3 读快照时机

RC和 RR的 读快照时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

• 「RC 读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View。
• 「RR 可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

举例说明：

• session A

SELECT*FROM parent WHERE id=1;

1

• session B

UPDATE parent SET id=3 WHERE id=1;

在会话 B 中将事务表 parent 中 id 为 1 的记录修改为 id=3，但是事务同样没有提交，这样 id=1 的行其实加了一个 X 锁。

回到之前的会话 A，接着上次未提交的事务，执行 SQL 语句 SELECT*FROM parent WHERE id=1 的操作，这时不管使用 READ COMMITTED 还是 REPEATABLE READ 的事务隔离级别，显示的数据应该都是 1。

• session A

SELECT*FROM parent WHERE id=1;

1

• session B

commit

在会话 B 提交事务后，这时在会话 A 中再运行 SELECT*FROM parent WHERE id=1 的 SQL 语句。
对于 READ COMMITTED 的事务隔离级别，它总是读取行的最新版本。（其他事务修改后的值，读到的是NULL，因而被修改了）
对于 REPEATABLE READ 的事务隔离级别，总是读取事务开始时的行数据。（当前事务最初的值，读到的还是1）

3.4 MVCC

参考：https://www.liuvv.com/p/a0f7945d.html

4. 头脑风暴

• 意向锁，和其他锁可以共存。本身也是一个表锁。
• 行锁三种算法：行记录锁， 间隙锁，next-key 锁（行 + 间）。
• 脏读是读到其他未提交的数据，不可重复读能读到其他事务提交的数据。

5. 参考资料

• MySQL事务和锁机制详解
• https://juejin.cn/post/6844903666332368909
• https://www.zhihu.com/question/51513268/answer/127777478
• https://www.wencst.com/archives/1521
• https://xiaolincoding.com/mysql/lock/mysql_lock.html
• https://xiaolincoding.com/mysql/transaction/mvcc.html