关于MySQL事务原理与优化

秋天的一亩三分地

已于 2023-06-25 12:35:12 修改

阅读量197

点赞数

分类专栏： MySQL专栏文章标签： mysql 数据库 java

于 2023-05-17 01:18:02 首次发布

本文链接：https://blog.csdn.net/weixin_45925485/article/details/130716555

版权

MySQL专栏专栏收录该内容

6 篇文章 1 订阅

订阅专栏

一、概述

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

二、事务

使一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性。

1、ACID属性

原子性（Atomicity）：

当前事务的操作要么全部成功，要么全部失败。原子性由 undo log 日志来实现。

一致性（Consistent）：

使用事务的最终目的，由其它 3 个特性及业务代码的正确逻辑来实现。

隔离性（Isolation）：

在事务并发执行时，各事务内部的操作不能互相干扰。隔离性由 MySQL 的各种锁以及 MVCC 机制来实现。

持久性（Durable）：

一旦提交了事务，其对数据库的改变就应该是永久性的。持久性由 redo log 日志来实现。

2、并发事务处理带来的问题

更新丢失（Lost Update）或脏写

当两个或多个事务选择同一行数据进行修改，有可能发生更新丢失问题，即最后的更新覆盖了由其他事务所做的修改。

脏读（Dirty Reads）

事务 A 读取到了事务 B 已经修改但尚未提交的数据。

不可重读（Non-Repeatable Reads）

事务 A 内部的相同查询语句在不同时刻读出的结果不一致。

幻读（Phantom Reads）

事务 A 读取到了事务 B 提交的新增数据。

3、事务隔离级别

“脏读”、“不可重复读”和“幻读”,均为数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

隔离级别	脏读 (Dirty Read)	不可重复读 (NonRepeatable Read)	幻读 (Phantom Read)
读未提交 (Read Uncommitted)	√	√	√
读已提交 (Read Committed)	×	√	√
可重复读 (Repeatable Read)	×	×	√
可串行化 (Serializable)	×	×	×

数据库的事务隔离级别越高，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读"和“幻读”并不敏感，可能更关心数据并发访问的能力。
查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’;

4、事务隔离级别案例

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 `test`.`account` (`name`, `balance`) VALUES ('lilei', '0');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '10000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '80000');

读未提交

（1）打开一个客户端 A，并设置当前事务模式为 read uncommitted（未提交读），查询表 account 的初始值：

set tx_isolation='read-uncommitted';

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

（3）这时，虽然客户端B的事务还没提交，但是客户端 A 就可以查询到 B 已经更新的数据：

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

客户端 B 进行了回滚，此时若客户端 A 再查询数据，则结果又变回了 0，倘若在此之前，业务操作中先使用了上一条查询到的脏数据 500 进行了运算使用，则就会引发很明显的事故，即脏读问题：

读已提交（Oracle默认隔离级别）

（1）打开一个客户端 A，并设置当前事务模式为 read committed（未提交读），查询表 account 的初始值：

set tx_isolation='read-committed';

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新表 account：

（3）这时，客户端 B 的事务还没提交，客户端 A 不能查询到 B 已经更新的数据，解决了脏读问题：

（4）客户端 B 的事务提交：

（5）客户端 A 执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题：

可重复读（MySQL默认隔离级别）

可重复读隔离级别在事务开启的时候，第一次查询是查的数据库里已提交的最新数据，此时会生成一个全数据库快照，在此事务之后执行的查询操作都是查快照里的数据，别的事务不管怎么修改数据对当前这个事务的查询都没有影响;

若当前事务修改了某条数据，则当前事务之后再查这条数据就是查询被修改之后的值，但是继续查其它数据时则依然是从快照里查，并不受影响。
（1）打开一个客户端 A，并设置当前事务模式为 repeatable read，查询表 account 的初始值：

set tx_isolation='repeatable-read';

（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新表 account 并提交：

此时表数据：

（3）在客户端 A 查询表 account 的数据，与步骤（1）查询结果一致，没有出现不可重复读的问题：

（4）在数据库修改表 account 的其他数据：

此时在客户端 A 继续查询表 account 的数据，其他数据也仍旧与步骤（1）查询结果一致，也没有出现不可重复读的问题

（5）在客户端 A，接着执行 SQL：update account set balance = balance - 300 where id = 1，balance 没有变成1000-300=700，而是将 lilei 的 balance 值取自步骤（2）中的 1500 来算的，所以是结果为 1200：

说明数据的一致性没有被破坏。

可重复读的隔离级别下使用了 MVCC（multi-version concurrency control）机制：

select 操作时是快照读（历史版本）;
insert、update 和 delete 操作时是当前读（当前版本）。

若此时在代码中将查询出来的数据进行运算，再更新到数据库，就会发生脏写（更新丢失）
除了可串行化，其余三种隔离级别均可能发生脏写问题，解决方案：

在更新 SQL 中直接运算，update 语句会默认使用行锁，使用当前读，读取到当前版本的数据进行运算更新，提交之后，其他事务才能对此行数据进行操作；
若为读已提交隔离级别，可使用乐观锁解决，在表里维护一个版本号字段，每次修改都将版本号+1。在更新数据库时会先比对版本号，若版本号一致才能更新成功。

（6）在数据库新增一条数据：

（7）在客户端 A 查询表 account 的数据，没有查出新增数据，所以没有出现幻读

（8）验证产生幻读
在客户端 A 执行 SQL：update account set balance=888 where id = 4；

此时能更新成功，并且再次查询能查到数据库那条新增的数据：

串行化

（1）打开一个客户端 A，并设置当前事务模式为 serializable，查询表 account 的初始值：

set tx_isolation='serializable';

（2）打开一个客户端 B，并设置当前事务模式为 serializable，更新相同的 id 为 1 的记录会被阻塞等待：

直到客户端 A 进行 commit 操作后才会执行完成，否则直接等待到超时：

但是更新 id 为 2 的记录可以成功：

在串行模式下 InnoDB 的查询也会被加上行锁
（3）若查询的记录不存在，则会给这条不存在的记录加上锁（间隙锁），锁范围为此 id 所在的无 id 区间，若之后都不存在其它 id，则锁范围为之后的正无穷大。
例如，数据库初始数据：

查询不存在的 id = 5 或者 id = 6 或者 id = 7 的数据，都会给不存在 id 的 5 至 7 区间加间隙锁，但是不影响 id =4 或者 id = 8 的数据操作：

此时客户端 A 就无法插入 id 为 5、6、7 的数据：

但是可以删改 id 小于等于 4 或者为 8，或者插入 id 为 9 及以后的数据：

且可以进行已存在的未被锁的修改操作：

（4）如果客户端 B 执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围都会被加锁，若为全表查询，则意味着全表加锁。

例如，数据库初始数据：

客户端 B 查询了全表：

此时若客户端 A 在该范围内插入新数据都会被阻塞，若为全表查询，则阻塞任何新数据，所以就避免了幻读：

可串行化的并发性极低，因此开发中很少使用

5、MySQL锁

读锁（共享锁、S锁）

select … lock in share mode;

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

写锁（排它锁、X锁）

select … for update;

写锁是排它的，会阻塞其它的写锁和读锁，update、delete、insert 都会加写锁。

可串行化的事务进行查询时，MySQL 后台会在 SQL 最后加上 lock in share mode，即加上读锁，使其它事务与其读写互斥。

另外三种隔离级别的事务进行查询时，不会加上读锁。

6、事务问题定位

# 查询执行时间超过1秒的事务
SELECT
	* 
FROM
	information_schema.innodb_trx		# 事务的表
WHERE
	TIME_TO_SEC( timediff( now( ), trx_started ) ) > 10;
# now()当前时间 减去 trx_started事务的开始时间 得出 秒钟：例如查询大事务执行时间大于 10 秒

# 强制结束事务
kill 事务对应的线程 id（即上方 SQL 查出结果里的 trx_mysql_thread_id 字段的值)

7、大事务的影响

并发情况下，数据库连接池容易被撑爆
锁定太多的数据，造成大量的阻塞和锁超时
执行时间长，容易造成主从延迟
回滚所需要的时间比较长
undo log膨胀
容易导致死锁

8、事务优化实践原则

将查询等数据准备操作放到事务外（例如 RC 级别，不可重复读对于放在事务里面或是外面所查询的结果并无差别）
事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
更新等涉及加锁的操作尽可能放在事务靠后的位置（更新操作是对已存在的数据进行操作，因而与其它事务发生互斥的可能性比较大）
能异步处理的尽量异步处理
应用侧（业务代码）保证数据一致性，非事务执行（业务需求对性能的要求非常高时，则不用事务，使用代码来保证，将数据库操作放于同个 try 中，并且 catch 出所有可能出现的异常，进行处理所有的回滚。虽然性能非常高，但对代码的要求也非常高，风险较大，不推荐）

9、面试题：查询操作方法需要使用事务吗？

若方法里的查询语句就一条，则不需要使用事务；
若方法里的查询语句有多条，则需视情况而定：

若隔离级别为 RC（读已提交），则不需要使用事务，因为此级别本身就不支持重复读，因此加事务也没有效果；
若隔离级别为 RR（可重复读），则根据业务场景来判断是否使用，例如查询出来的数据需要用于同个时间维度的运算，比如报表统计，此时便需要使用事务来确保查询的数据为同个时间维度的。

秋天的一亩三分地

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于MySQL事务原理与优化

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

扫一扫

专栏目录