mysql如何实现读提交锁_MySQL学习笔记（二）—MySQL事务及锁详解

一、事务

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

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

A账户余额扣去100

B账户余额增加100

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

1. 事务的四大特征

原子性：事务是原子性操作，要么全部成功，要么全部失败

一致性：多个事务对数据库操作会保证数据一致性

隔离性：并发时，事务之间互不影响

持久性：事务提交之后对数据库的影响是持久性的，不会因为数据库宕机导致数据丢失

2. 并发事务带来的问题

脏读

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

不可重复读

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

幻读

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

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

假设图一test开始是空表，事物1第一次查询得到空表，事物2在事物1执行期间插入一条数据，

事物1第二次查询由于满足可重复读，所以查询结果依然为空，但是事物1插入同样一条数据，报重复主键错误

幻读两个要素：

可重复读隔离级别下，快照读看到的是一致性视图，只有当前读才会产生幻读

幻读专指新插入发行，更新不算，将上述查询后面加上For Update，就会将事务2插入的数据读出来，这就是幻读

3. 事务隔离级别

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

事物隔离级别

脏读

不可重复读

幻读

读未提交(read-uncommitted)

是

是

是

读已提交(read-committed)

否

是

是

可重复读(repeatable-read)

否

否

是

串行化(serializable)

否

否

否

可重复读是MySQL默认级别

二、重要概念

1. MVCC和事务隔离的实现

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

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

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id，在事务开始的时

候向 InnoDB 的事务系统申请的，是按申请顺序严格递增

在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. 视图

用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。这是我们常说的视图

InnoDB 用来实现 MVCC 时用到的一致性读视图，即 consistent read view， 用于支持 RC(Read Committed，读提交)和 RR(Repeatable Read，可重复读)隔离级别的实现。没有物理结构，仅仅是逻辑上用来定义在事务执行期间能看到什么数据

3. 事务的起点

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

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

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

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

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

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

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不支持行锁。

重要概念： 两阶段加锁

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

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

死锁

解决方案：

超时等待

发起死锁检测，主动回滚其中某个事务

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

死锁检测有额外负担，在事务被锁住，需要查看其依赖的线程是否被锁住，一直循环，最后判断出现死锁，在多个线程并发修改同一行数据时，时间复杂度会变成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讲