MySQL事务原理与最佳优化实践、MVCC

隔离级别	脏读(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatableread)	不可能	不可能	可能
可串行化(Serializable)	不可能	不可能	不可能

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读"和“幻读”并不敏感，可能更关心数据并发访问的能力。

查看当前数据库的事务隔离级别: show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

（四）事务隔离级别案例分析

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `account` (`name`, `balance`) VALUES ('lilei', '500');
INSERT INTO `account` (`name`, `balance`) VALUES ('hanmei', '1000');
INSERT INTO `account` (`name`, `balance`) VALUES ('lucy', '1500');

set tx_isolation='read-uncommitted';//读未提交
set tx_isolation='read-committed';//读已提交
set tx_isolation='repeatable-read';//可重复读
set tx_isolation='serializable';//串行化

1.read uncommit (读未提交)：脏读

旧版本（5.x）的变量是tx_isolation，新版本（8.x）的系统变量改成transaction_isolation

初始数据

事务A update但是没有commit, 真实数据还是balance=500；但是事务B查出的数据是balance=1000，数据不一致了

一旦事务A因为某种原因回滚，所有的操作都将会被撤销，那事务B查询到的数据其实就是脏数据：

2.read commit (读已提交)：不可重复读

初始数据

例子1：事务A update但是没有commit，事务B查出的数据是balance=500，数据一致，解决了脏读问题

事务A commit后，事务B查出的数据是balance=1000（事务B未commit），产生了不可重复读的问题

例子2：初始balance=1000，事务A update但是未commit，事务B查出的数据是balance=1000

事务C（与事务B在同一隔离级别下） update并commit，事务A未commit，事务B查出的数据是balance=1500，产生了不可重复读的问题

如果是mysql8.0版本，事务C（与事务B在同一隔离级别下） update并commit，事务A未commit，事务B查出的数据会是balance=2000

3.幻读问题描述

例子1：.事务A insert表数据中不存在的数据，未commit,事务B也执行一样的操作，发现插入不了表中不存在的数据，类似于出现幻影，因而称之为幻读：

例子2：事务A 查询表中不存在的id=6数据未commit，事务B插入事务A中查询的id=6数据，事务A更新id=6数据且更新成功：

4.repeatable read (可重复读) ：幻读

这个隔离级别记住一句话就能理解：可重复读隔离级别在事务开启的时候，第一次查询是查的数据库里已提交的最新数据，这时候全数据库会有一个快照(当然数据库并不是真正的生成了一个快照)，在这个事务之后执行的查询操作都是查快照里的数据，别的事务不管怎么修改数据对当前这个事务的查询都没有影响，但是当前事务如果修改了某条数据，那当前事务之后查这条修改的数据就是被修改之后的值，但是查其它数据依然是从快照里查，不受影响。

初始数据

事务A如果update修改事务B已经修改提交的数据，则事务A能在当条数据的最新的数据上update（此时查询这条id=1的数据就是更新后的数据，即使未commit），其他数据行不受影响，即使insert新的数据，读取的还是和事务开始时的一样

这个事务级别更新数据不能在Java计算+500后再update，会覆盖其他事务update修改后的数据，应该在sql set balance = balance+500

5.serializable(串行化)：解决上面所有问题，包括脏写

事务A查询id为1的记录，事务B更新相同的id为1的记录会被阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁，如果查询的记录不存在会给这条不存在的记录加上锁(这种是间隙锁)。

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。

这种隔离级别并发性极低，开发中很少会用。

（五）锁

读锁读锁是共享的，多个事务可以同时读取同一个资源，但不允许其他事务修改

写锁（排它锁、X锁）：select for update；... 写锁是排他的，会阻塞其他的写锁和读锁，update、delete、insert都会加写锁

三、MVCC(Multi-VersionConcurrency Control)多版本并发控制

MVCC多版本并发控制，就可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链来实现

select操作是快照读（历史版本）
insert、update和delete是当前读（当前版本）
- 事务A update数据未提交，事务B不能update相同语句，数据行已加锁（悲观锁）
readcommit (读已提交) ，语句级快照
repeatableread (可重复读)，事务级快照

（一）隔离级别选择

业务并发要求高的选择readcommit (读已提交)
业务并发要求不高、对数据的时间维度要求更统一的选择repeatableread (可重复读)

（二）查询操作方法需要使用事务吗？

分情况：

1.如果是只读取一条数据则不需要加事务

2.如果读取多条则看使用的事务级别

如果是readcommit (读已提交) ，则不需要，
如果是repeatableread (可重复读)，则需要（假如在统计报表，多条数据查询不加事务，读取出来的数据可能不是同一时间维度的，还要对数据进行计算，就会有问题）

（三）事务持久性

MySQL引入了redolog，Buffer Pool内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。

即便MySQL在中途挂了，我们还可以根据redolog来对数据进行恢复（redo日志与page页数据比对，发现不同，更新最新数据，防止数据丢失）。

redolog （写之前会开辟一大块内存）是磁盘顺序写的，写入速度很快。并且它记录的是物理修改（xxxx页做了xxx修改），文件的体积很小，恢复速度也很快。

磁盘文件ibd,每个表都有一个单独的ibd文件，可能存在磁盘的不同地方，不可能是磁盘顺序写。

（四）事务问题定位

#查询执行时间超过1秒的事务，详细的定位问题方法后面讲完锁课程后会一起讲解
SELECT
    * 
FROM
    information_schema.innodb_trx 
WHERE
    TIME_TO_SEC( timediff( now( ), trx_started ) ) > 1;
 
 #强制结束事务
 kill 事务对应的线程id(就是上面语句查出结果里的trx_mysql_thread_id字段的值)

（五）大事务的影响

并发情况下，数据库连接池容易被撑爆
锁定太多的数据，造成大量的阻塞和锁超时
执行时间长，容易造成主从延迟（数据延迟）
回滚所需要的时间比较长
undo log膨胀（回滚需要按照每条数据的寻找对应的undo日志版本链进行回滚）
容易导致死锁

（六）事务优化

将查询等数据准备操作放到事务外 (read commit (读已提交)无影响， repeatable read (可重复读)有影响)
事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久（防止死锁一直等待）
事务中避免一次性处理太多数据，可以拆分成多个事务分次处理（1000条可分10次插入，避免长时间占用事务，让别的事务无法操作）
更新等涉及加锁的操作尽可能放在事务靠后的位置（先insert再update，防止多个事务update同一行数据等待）
能异步处理的尽量异步处理
应用(业务代码)保证数据一致性，非事务执行（多个try，catch，回滚，不推荐）

bubble小拾

关注

39
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
MySQL事务原理与最佳优化实践、MVCC

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制，用一整套机制来解决多事务并发问题。
复制链接

扫一扫