事务控制新增后修改_Mysql InnoDB存储引擎并发控制与事务

最新推荐文章于 2023-06-07 23:19:39 发布

weixin_39531780

最新推荐文章于 2023-06-07 23:19:39 发布

阅读量217

点赞数

文章标签：事务控制新增后修改同一个事务里面对同一条数据做2次修改

在上一篇文章《mysql基础-索引》我们对mysql索引中的一些基础知识进行了学习，本篇文章我们一起来看看InnoDB中锁与事务。

InnoDB如何进行并发控制

首先需要明确的是为什么需要并发控制？

多个事务在同一时刻操作同一个临界资源，如果不进行有效的并发安全处理，就会导致数据产生不一致问题。

并发控制的常见思路

锁：数据操作前锁住，不允许其他并发任务操作，操作完成后释放锁
数据多版本：写任务时将数据克隆一份，以版本号区分，写任务操作新克隆数据直至提交，读任务可以并发读取旧版本，不至于阻塞

InnoDB存储引擎中这两种并发控制的方法都有实现，对于锁InnoDB实现了两种锁模式：

共享锁(S锁)：S锁能够实现读读并发，读取数据时加S锁
排他锁(X锁)：X锁实现读写互斥，写写互斥，修改数据时加X锁

InnoDB使用锁进行并发控制能够实现读读并行，读写与写写均无法并行。一旦写数据任务还没有完成，数据是无法被其他事务读取，这对事务并发有较大影响。

数据多版本：

InnoDB利用数据多版本实现读写任务的并发，大大提高innodb的并发度。

如图所示：T1时刻来了一个写任务，复制了一个新的版本data(v1)，写任务操作data(v1)；同时T2、T3时刻来的读任务还可以对data(v0)的数据执行读操作。

InnoDB是如何实现数据多版本呢

redo日志

数据库事务提交后必须将数据刷到磁盘，以保证ACID特征。但磁盘读写性能较差，每次刷新磁盘会极大影响数据库性能。因此InnoDB会将修改行为先写到redo日志里(此时变成了顺序写)，然后定期异步刷新到磁盘。

undo日志

数据库事务未提交时将事务修改前的数据存放到undo日志中，当事务回滚时，可以利用undo日志中的数据对事务进行回滚。

对于insert 操作，undo日志中存的是primary key，回滚时直接删除; 对于delete和update，undo记录旧数据的row，回滚时直接恢复。

例如下面一张表tb_user,主键是id：

先插入一条数据

insert into tb_user(id, name, password) values(4,'along','123456');

执行一条更新：

update tb_user(name,password) values('tom','666') where id=2;

再执行一条删除：

delete from tb_user where id=3;

那么InnoDB的undo日志中就会增加三条日志：

存放undo日志的地方被称为回滚段，在事务回滚时，InnoDB会根据undo日志中的内容对将事务进行回滚，例如insert操作的回滚会删除id=4的数据；update和delete操作回滚时则会恢复旧版本的数据。

存放undo日志的地方被称为回滚段，回滚段作为InnoDB的数据旧版本为InnoDB提供多版本并发控制支持(Multi Version Concurrency Control, MVCC)

我们对InnoDB的多版本并发控制(MVCC)进行简单总结：

旧版本存在回滚段里(存放undo日志的地方)
InnoDB利用undo日志实现MVCC，提高并发
数据多版本实现读写并行
InnoDB并发度较其他引擎更高，快照读不加锁
InnoDB所有的普通select都是快照读

InnoDB实现的锁类型

在上面的内容中我们简单提到了InnoDB中的锁，其实InnoDB锁实现锁机制远比上文中提到的要复杂，在这一小节中我们就系统的看看InnoDB实现的哪些锁。

我们有一张表tb_user，id是表的主键，本节中的列子都基于这张表展开：

共享锁和排他锁(Shared and Exclusive Locks)

共享锁(S锁)：实现读读并发
排他锁(X锁)：实现读写互斥，写写互斥，事务只有拿到排他锁才能删除或修改这一行

select * from tb_user where id=1 in share mode; //加S锁

select * from tb_user where id=1 for update; //加X锁

记录锁(Record Locks)

记录锁加到索引记录上，用于锁定一个索引记录

注意记录锁是加到索引上，而非数据行，InnoDB行锁都是基于索引，而非数据行。

我们做一个实验来验证一下记录锁，将数据库事务改为手动提交，

先执行：

start transaction;

update tb_user set address='beijing' where id=9;

事务暂不提交

再执行:

start transaction:

update tb_user set password='123' where id=9;

事务暂不提交

可以看到第二个事务获取锁超时，执行失败。就是由于第一个事物拿到了id=9索引的记录锁，并且事务没有提交，锁也就不会被释放，导致第二个事务等待锁超时。

既然记录锁是在索引上加的，而非在数据行上。那么如果我们在name字段上建一个辅助索引(Secondary Index)，在第一个事务还没提交的情况下执行：

start transaction:

update tb_user set password='666' where name='xiaohong';

那么本事务能执行成功吗？

留给大家思考。

间隙锁(gap locks)

间隙锁可以封锁索引记录的间隔

举例来说，表里有以上这些字段，id上存在主键索引，索引记录的间隔就是(-oo,1)、(1,6)、(6，9)、(9，11)、(11，+oo)。那么获取间隙锁的事务就会锁住索引记录之间的间隔，其他事务就无法插入id在这些间隔的记录。

我们做下面这个实验：

——————————————————————

开启一个事务A

start transaction:

select * from tb_user where id between 6 and 9 for update;

事务不提交

——————————————————————

开启一个事务B

start transaction:

insert into tb_user values(7,'xiaohua','beijing','123',null);

——————————————————————

可以看到事务B获取锁超时，是由于事务A获取临键锁锁住了区间(6，9)，因此事务B无法插入id为7的记录。

临键锁 (Nexted-Key Locks)

记录锁和间隙锁的组合，它封锁的范围既包含索引记录，又包含索引区间。

举例来说，表里还是以上这些字段，临键锁封锁的区间是(-oo,1)、[1,6)、[6，9)、[9，11)、[11，+oo)。

针对临键锁我们做下面这个实验：

——————————————————————

开启一个事务A

start transaction:

select * from tb_user where id between 6 and 9 for update;

事务不提交

——————————————————————

开启一个事务B

start transaction:

insert into tb_user values(6,'xiaohua','beijing','123',null);

——————————————————————

因为事务A获取的临键锁会锁住区间[6,9]，事务B同样是获取锁超时。

插入意向锁

专门针对insert操作，多个事务同一个范围内插入记录，如果位置不冲突，不会阻塞彼此。(非自增主键)

插入意向锁很好理解，就是针对id是非自增的情况下有效。我们在这篇文章中给出的表就是非自增主键，分别执行下面两个插入SQL:

insert into tb_user values(7,'xiaohua','beijing','123',null);

insert into tb_user values(8,'xiaoqing','shanghai','123456',null);

由于他们插入id不同，所以这两个事务不会阻塞彼此。

自增锁

是一种表级别锁，专门针对事务插入自增主键的列。如果有一个事务正在往表中插入记录，其他插入事务必须等待。

InnoDB存储引擎主要支持的锁类型就介绍完了，这里我们做个小结：

共享锁和排他锁是行级锁，实现读读并发，读写互斥，写写互斥;
记录锁锁定索引记录，而不是锁定数据行
间隙锁锁定间隔，防止间隔中被其他事务插入
临键锁锁定索引记录+加间隔
插入意向锁是间隙锁的一种，针对非自增主键插入，如果新增主键不冲突，则不会彼此阻塞
自增锁是一种表级别锁，针对自增主键插入，如果有事务正在插入，其他插入型事务必须等待

InnoDB事务

并发事务中存在的问题

我们在学数据库时应该都学过脏读、不可重复读、幻读，但好像总是闹不清它们三个的区别，今天我们一起来看看它们仨到底啥区别。

脏读：一个事务读取到另一个事务未提交的数据
不可重复读：一个事务两次读取一条或一批记录结果不一致，期间另一个并发事务对数据进行了修改
幻读：一个事务两次读取一条或一批记录结果不一致，期间有另一个并发事务新增了一条数据
对于脏读比较容易区分，对于不可重复读和幻读好像这哥俩比较像，其实我们只要抓住一点“不可重复读的重点在于修改，幻读的重点在于新增或删除”，就能很容易的将不可重复读和幻读区别开来。

事务的隔离级别

读未提交(Read Uncommitted)
读提交(Read Committed, RC)
可重复读(Repeated Read, RR, InnoDB默认隔离级别)
串行化 (Serializable)

这几个事务隔离级别从上到下并发性逐渐减弱，一致性逐渐增强。下面这个表格很多同学都见过：

在实际中由于读未提交的一致性太差、串行化的并发性太差，这两个隔离级别很少用到。不同的隔离级别下其实是不同的SQL加锁会存在差异，因此我们主要来看看RC和RR在InnoDB中究竟差别在哪儿。

可重复读隔离(RR)级别下加什么锁

普通快照读(select … from tb_user where id=10)，是一种不加锁的一致性读(Consistent Nonlocking Read)，使用MVCC实现.

加锁读(select … in share mode/ for update)，update, delete则与查询条件有关

唯一索引(索引字段值唯一，如id)上使用唯一查询条件，使用记录锁，不会封锁记录间隔。如：update tb_user set address=‘beijing’ where id=10。
非唯一索引上(索引字段值不唯一，如name)使用唯一查询条件，则会使用间隙锁。如update tb_user set address='beijing' where name='jack'。其实这是一种特殊间隙锁，所有以name='jack'为查询条件的事务均无法执行，包括新增和删除，以避免不可重复读和幻读。
范围查询，会使用临键锁，锁住索引记录以及索引之间的范围，避免范围内插入记录和修改，可避免不可重复读和产生幻影记录。如：delete from tb_user where id between 3 and 9;

对于InnoDB的RR事务隔离级别，大家是否有这样的困惑：之前说说RR只能避免不可重复读不能避免幻读，而这里又说RR可以避免幻读，这是要搞事情啊！

我刚开始也有这个困惑，我们平时看到的说RR只能避免不可重复读不能避免幻读是标准的隔离级别，InnoDB在这块实现上确实没有遵循标准来。因为InnoDB在实现时使用了临键锁，避免并发事务再已经加锁的区间进行插入或删除，因此是可以避免幻读的，官网上也有说明。