MySQL学习笔记（二）—MySQL事务及锁详解

一、事务

数组库的一组操作，要么全部成功，要么全部失败

举例：银行转账 A账户向B账户转100

1. A账户余额扣去100
2. B账户余额增加100

上述两个操作要么全部成功，要么全部失败，部分成功或失败，数据就错乱了

1. 事务的四大特征

• 原子性：事务是原子性操作，要么全部成功，要么全部失败
• 一致性：多个事务对数据库操作会保证数据一致性
• 隔离性：并发时，事务之间互不影响
• 持久性：事务提交之后对数据库的影响是持久性的，不会因为数据库宕机导致数据丢失

2. 并发事务带来的问题

脏读

在一个事务中，读取了其他事务未提交的数据

不可重复读

在一个事务中，同一行记录被访问了两次却得到了不同的结果

幻读

在一个事务中，同一个范围内的记录被读取时，其他事务向这个范围添加了新的记录。

前面脏读和不可重复读容易理解，幻读稍微难一点

假设图一test开始是空表，事物1第一次查询得到空表，事物2在事物1执行期间插入一条数据，事物1第二次查询由于满足可重复读，所以查询结果依然为空，但是事物1插入同样一条数据，报重复主键错误

幻读两个要素：

1. 可重复读隔离级别下，快照读看到的是一致性视图，只有当前读才会产生幻读
2. 幻读专指新插入发行，更新不算，将上述查询后面加上For Update，就会将事务2插入的数据读出来，这就是幻读

3. 事务隔离级别

为了解决上述并发事务问题，MySQL数据库提供了事务隔离级别

事物隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

可重复读是MySQL默认级别

二、重要概念

1. MVCC和事务隔离的实现

• 同一数据库记录可以在系统中存在多个版本，这就是MVCC (多版本并发控制)

• 不同时刻开启的事务会创建不同的视图，后续直接从视图读取数据，达到数据隔离，当然数据隔离还需要数据库锁的帮助

• InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id，在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增。

MVCC实现： 在MySQL中，每条记录的更新都会记录一条undo Log，记录上最新的值通过回滚可以，都可以得到前一个状态的值。

上图中，数据库一行记录有多个版本，每个版本有自己的 row trx_id，最新版本V4的k=22，是被row trx_id=25事务更新的，不同时刻启动的事务看到不同的视图，而V1，V2，V3不是物理上真实存在的，要想得到它们需要根据当前版本和undo Log(回滚日志)计算，比如V1的值需要执行U3，U2，U1才能得到

undo Log日志如果一直存在，可能会严重占据磁盘空间，当系统没有比undo Log更早的视图时，就会把undo Log删除掉

长事务一般会保存很老的事务视图，导致其它事务的undo Log无法删除，所以在这个事务提交前，可能会导致大量undo Log存在，我们需要避免使用长事务

2. 视图

1. 用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。这是我们常说的视图
2. InnoDB 用来实现 MVCC 时用到的一致性读视图，即 consistent read view， 用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。没有物理结构，仅仅是逻辑上用来定义在事务执行期间能看到什么数据

3. 事务的起点

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动， start transaction with consistent snapshot 该命令可以立即启动事务

4. 隔离级别与视图的关系

1. “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念

2. “读提交”隔离级别，这个视图是在每个 SQL 语句开始执行的时候创建的

3. “可重复读”隔离级别： 视图是在事务启动 (执行第一条语句或者使用特定命令) 时创建的，整个事务存在期间都用同一个视图

4. “串行化”隔离级别下直接用加锁的方式来避免并行访问

5. 当前读与快照读

• 当前读，在事务执行过程中可以读到其它已已提交事务的最新数据
• 快照读，在事务执行过程中只能看到从事务起点创建的一致性视图，并不能读到其它已提交数据

在RR（可重复读）级别下，快照读满足以下两个规则：

• 读取的记录：更新的事务ID <= 当前事务ID
• 读取的记录：删除的事务ID > 当前事务ID（小于的话数据都删了，肯定读不到）

三、MySQL锁分类

按照不同维度可分为：

1）

• 悲观锁
• 乐观锁

2）

• 共享锁（写锁）
• 排它锁（读锁）

3）

• 意向共享锁
• 意向互斥锁

意向锁其实不会阻塞全表扫描之外的任何请求

假设没有意向锁，两个请求，一个修改数据某一行记录，另一个需要修改该表所有行记录，这时需要就需要对所有的行是否被锁定进行扫描，引入意向锁，只需要判断该表有没有意向锁，等待修改单行事务提交，意向锁释放

4）

• 全局锁
• 表锁和元数据锁（meta data lock 简称（MDL））
• 行锁

全局锁：对整个数据库实例加锁

作用： MyISAM不支持事务拿不到一致性视图，需要加全局读锁做逻辑备份。加读锁期间数据库只能读，不能写。

表锁：使用lock tables 命令来锁住整个表，一般不使用

MDL: 当对表做增删改查操作时，需要加MDL读锁；当需要对表做结构变更操作时需要加MDL写锁（见其它篇文章）

所以如果有两个线程，一个对表做读操操作，一个需要给表加字段，第二个操作会被阻塞。

在给表加字段的时候，如果该表请求频繁，这时会无法获取MDL写锁，同时会阻塞后续业务请求拿读锁。

解决方法：在 alter table语句里面设定等待时间，如果在指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

行锁： 在引擎层实现，MyISAM不支持行锁。

重要概念： 两阶段加锁

在数据库更新时会给扫描的数据行加行锁，更新结束不会立马释放行锁，需要等到事务提交才会释放行锁。

由于两阶段锁的存在，所以在一个事务中，更新语句如果放在前面，会阻塞其它事务对表的更新，影响并发。对于更新频繁的语句尽量放在事务的靠后部分

死锁

解决方案：

1. 超时等待
2. 发起死锁检测，主动回滚其中某个事务

超时等待的时间根据业务执行时间制定，太短误伤，太长会影响并发量

死锁检测有额外负担，在事务被锁住，需要查看其依赖的线程是否被锁住，一直循环，最后判断出现死锁，在多个线程并发修改同一行数据时，时间复杂度会变成O(n^2),会导致CPU利用率很高，却执行不了几个事务。一般通过控制并发来解决

5）

• 记录锁（record Lock）
• 间隙锁（Gap lock）
• next-key

在另一篇文章中详细讲解了加锁情况

数据库的行锁实际上record Lock，会对扫描的行加锁，如果没有走索引，扫描全表，会锁住整个表的所有行。

例：

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

语句1：select * from t where id >3 for update;
语句2：select * from t where d > 3 for update;
语句1会走主键索引，对扫描到的行数加锁
语句2不走索引，扫描全表，对所有行加record lock

在可重复读的隔离级别下：
每次开启事务，会生成一致性视图，看不到其它事务已经提交的修改，在前面已经提过

更新语句先读后写，这个读是当前读，就算我们对所有数据加上record lock，也不能阻止数据的插入。这样我们在当前读中还是会读到插入的数据，形成幻读。

如何避免幻读？

使用Gap lock + record lock

间隙锁是对索引记录中的一段连续区域的锁
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;
这个语句阻止其他事务向表中插入 id = 15 的记录，因为整个范围都被间隙锁锁定

虽然间隙锁中也分为共享锁和互斥锁，不过它们之间并不是互斥的，也就是不同的事务可以同时持有一段相同范围的共享锁和互斥锁，它唯一阻止的就是其他事务向这个范围中添加新的记录

间隙锁的引入，可能会导致同样的语句锁住更大的范围，但是它只在可重复读级别下才会生效

Next-Key是记录锁和记录前的间隙锁的结合，每个 next-key lock 是前开后闭区间
select * from t where id = 5
会加上(4, 5]的next-key,同时会加上(5, 6]的间隙锁
next-key的加锁原则是锁定的是当前值和前面的范围

注：一般生产都会设置读已提交级别，这个时候为了防止binlog和数据库数据不一致需要设置binlog格式为row，在代码中使用锁来解决并发问题。数据库应该尽可能简单，不管是语句，还是隔离级别，保证数据库的性能。

参考

丁奇老师 MySQL45讲