6.MySql事务原理及优化

-273K

已于 2024-10-09 10:27:22 修改

阅读量962

点赞数 23

文章标签： mysql 数据库

于 2024-10-01 22:25:53 首次发布

本文链接：https://blog.csdn.net/Csdn_xzh/article/details/142675418

版权

通常我们所用的MySql数据库不是单机的，而是会有很多事务在同时进行，多个事务可能会在同一时间对相同的一批数据进行增删改查操作，也就是我们所说的并发，这样就会导致我们说的脏读、脏写、幻读、不可重复读等操作。

这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制，用一套机制来解决多事务并发问题。

一、事务ACID属性

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

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

隔离性(lsolation):在事务并发执行时，他们内部的操作不能互相干扰，隔离性由MySql内部的各种锁及Mvcc机制来实现。

持久性((Durable)：一旦提交了事务、他对数据库的改变就应该是永久的。持久性由redo log日志来实现。

一致性(Consistent):是使用事务的最终目的，由上面3个特性及业务代码正确逻辑来实现。

二、并发事务的问题

1.更新丢失(Lost Update)或脏写

当两个或多个事务选择同一行数据修改，会发生更新丢失的问题，即最后的更新覆盖前面的更新

如何解决？

1.不要再代码中进行操作数据并且更新数据，直接在数据库层面进行更新（行锁+MVCC机制控制）

2.使用乐观锁(不可在可重复读这个隔离级别使用)

2.脏读(Dirty Reads)

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

3.不可重读(Non-Repeatable Reads)

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

4.幻读(Phantom Reads)

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

什么意思呢?

可重复读隔离级别情况下，事务读取数据是第一次查询时的快照，若事务未提交则读取的数据不会改变，但是在新增、修改、删除的情况下会读取最新的数据并且会更新原始快照。即select是快照读，insert、delete、update是当前读。

如何解决：

使用间隙锁

三、事务隔离级别

脏读、幻读、不可重复读,其实都是数据库读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。

隔离级别从上往下逐渐提高。

隔离级别	脏读	不可重复读	幻读
读未提交的 (Read uncommitted)	可能	可能	可能
读已提交的 (Read committed)	不可能	可能	可能
可重复读 (Reapatablead)	不可能	不可能	可能
可串行化 (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 `test`.`account` (`name`, `balance`) VALUES ('lilei', '450'); 
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000'); 
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

1.读未提交

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

set tx_isolation='read-uncommitted';

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

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

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

（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别

2.读已提交

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

set tx_isolation='read-committed';

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

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

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

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

3.可重复读

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

（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录

set tx_isolation='repeatable-read';

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

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

（4）在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤2中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作是快照读（历史版本）；insert、update和delete是当前读（当前版本）。

（5）重新打开客户端B，插入一条新数据后提交

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

（7)验证幻读

在客户端A执行update account set balance=888 where id = 4；能更新成功，再次查询能查到客户端B新增的数据

4.串行化

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

set tx_isolation='serializable';

（2）打开一个客户端B，并设置当前事务模式为serializable，更新相同的id为1的记录会被阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁，如果查询的记录不存在会给这条不存在的记录加上锁

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

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

五、事务影响

并发情况下，数据库连接池容易被撑爆

锁定太多的数据，造成大量的阻塞和锁超时

执行时间长，容易造成主从延迟

回滚所需要的时间比较长

undo log膨胀

容易导致死锁

六、事务优化

将查询等数据准备操作放到事务外（针对RC级别，RR级会有影响）

事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久

事务中避免一次性处理太多数据，可以拆分成多个事务分次处理

更新等涉及加锁的操作尽可能放在事务靠后的位置

能异步处理的尽量异步处理

应用侧(业务代码)保证数据一致性，非事务执行

七、问题

1.查询操作方法需要使用事务吗？

对于单纯的 MySQL 查询操作（如简单的SELECT语句），大多数情况下不需要使用事务。例如，查询一张表中的所有记录：SELECT * FROM your_table;，这只是从数据库中读取数据，不会修改数据的状态，也不会影响数据的一致性、完整性等事务相关的特性。
在一些复杂的场景下，如果查询操作与其他可能影响数据的操作（如更新、插入、删除操作）有逻辑关联，并且需要保证数据的一致性，那么可能需要使用事务。

例如，有一个业务场景是查询用户账户余额，并根据余额进行扣款操作。如果不使用事务，可能会出现查询余额时得到一个数值，但是在进行扣款操作之前，另一个并发事务修改了余额，导致数据不一致。在这种情况下，可以将查询余额和扣款操作放在一个事务中
当存在并发查询和数据修改操作时，如果需要确保查询结果不受其他并发事务的未提交数据影响（即隔离性要求），事务可以起到一定的作用。例如，通过设置事务的隔离级别（如READ COMMITTED、REPEATABLE READ等），可以控制查询操作在并发环境下看到的数据状态。