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

听课问题

1. 除了读未提交,有一个事务对一条数据进行了修改，但是另外又有一个没有加事务的查询sql，那么读取到的数据是原始数据还是没提交的数据。

答案：没加事务的查询读取的是老数据，等事务提交以后就会读取新修改的数据

1. 除了读未提交,事务A修改一个 id = 5的数据之后没提交，事务B进来也修改相同id = 5的数据，那么会是什么现象？ 是可以提交还是等待事务A提交或者回滚之后进行操作

答案： 会等待A事务修改完以后B再执行修改

ACID

A：原子性

在一个事务中要不全部成功要不全部失败。比如事务A中有两个update 第一个update A表 第二个update B表。那么这两个update语句要么全部成功，要么有一个失败另外一个就回滚掉。

C：一致性

1. 从一个有效状态改为另外一个有效状态。需要其他三个特性共同维持。更是一种达到某种目的的约束，
2. 比如A有500，B有100.那么A要给B转钱500，那么A减100，B加100，但是如果A成功了变成400，B失败了还是100没加上，这时候凭空少了100肯定是不行的，那么就需要使用原子性，B加100失败了连着A也一起回滚掉。
3. 如果仅仅有原子性的情况，假设C也要给A转钱100并且成功提交了（此时A还没回滚但是减了100），而A给B转钱因为B失败了要回滚到500，C已经给A了100那么A应该有600，但是C当时读到了的是已经减去了100的A也就是400，加了100变为500，A又因为B失败回滚到了500，导致C凭空消失了100元A也没加上这100，这时候应该有隔离性，A没提交C要修改A的数据就要等待因为有读锁。
4. 如果只有原子性+隔离性：当A数据库宕机了，数据都没了那么肯定保证不了一致性，这时候就会写到硬盘，持久化，再次开机还是有原本已经提交过的数据。

I： 隔离性

数据之间的数据是相互隔离的。其中有隔离级别分别为：读未提交、读已提交、可重复读、串行化

其中这四种隔离界别会导致四种问题：脏读、不可重复读、幻读、脏写

隔离级别分别造成的四种问题

脏读

一个事物读取到了另外一个事务没有提交的数据。

不可重复读

一个事务，第一次查询该条数据和第二次查询该条数据不一致

幻读

假设表A中有id = 1、2、3；三条数据，事务A查询A表中数据，是三条数据，这时候事务A不提交

事务B新增一条数据id = 4；成功提交

事务A再次查询其实还是三条数据，但是如果修改id = 4的数据，会直接修改掉。看不到id = 4的数据却能修改。

有一个概念： 查询时是redolog，修改时是当前数据。比如：事务A第一次id = 1的account = 10，这时候其他事务修改了并且提交了account = 20。 事务A再次查询id = 1的数据account 还是 10，如果这时候 事务A写一个update A set account = account + 10 where id = 1； 之后再去查询 account 竟然变为了 30。修改数据以后就会刷新数据，并且使用当前表最新数据进行修改

脏写

假设是读已提交。两个事务分别为A事务和B事务，A事务读取id = 1的数据 得到account = 10并且在原有基础上加20，没提交。
B事务修改A事务id=1的数据account = 20，因为是读写锁不互斥B可以修改并且提交了，A事务在Java代码中直接取到了10+20 = 30 update 表 set account = 30 where id= 1;这样account变为了30，但是人家原有的事务B已经把account改为了10并且提交了，B事务怎么还是拿的account=10进行计算的呢？

读未提交（有脏读、不可重复读、幻读问题）

两个事务分别为A事务，B事务。A事务进行了update A 将A表的account = 10 修改为了account = 20。事务A还没提交，这时候事务B读取数据读到了20之后在Java代码进行操作。而事务A又因为其他的失败了导致了回滚又到了10，这时候B读取的肯定是错误的。

读已提交（有不可重复读、幻读问题，解决脏读）

两个事务分别为事务A，事务B。事务B读取select * from A where id= 1;不提交，当事务A修改了A表中 update A set account = account + 10 where id = 1;时还没提交，事务B再次查询id = 1 的数据，那么和第一次读取到的数据是一样的，即使第一次读取完以后，事务A修改了id = 1 的数据但是没有提交，事务B第二次读取数据依然是第一次读取的而不是修改过的数据，但是如果事务A提交了，那么事务B第二次查询就会导致和第一次不一致，这也就是不可重复读。解决了脏读，因为没提交的数据读取不到。

可重复读（有幻读问题，解决脏读、不可重复读）

两个事务分别为事务A、事务B。当事务A查询A表中id = 1 的数据得出account = 10，不提交。事务B修改A表中id=1的account = 10 成功后提交。事务A还没提交事务再次查询，这时候会和第一次查询一样，不管其他事务如何修改该数据并且提交，当前事务查询到的数据和第一次都是一致的。但是如果新增数据

串行化（解决脏读、不可重复读、幻读）

单线程,多个事务：事务A、事务B、事务C、事务D他们依次开始进来开启事务，那么事务A先执行完毕在执行事务B再就是C、D。这样就保证了

怎么解决脏写

在Java中比如开启事务，修改表A中account原有基础+10；A表原有account = 20；

错误逻辑

1. 先 查询 select account from A where id = 1;
2. 拿到了account java代码进行计算 + 10 = 30；
3. 在修改 update A set account = 30 where id = 1;
4. 在查询完成以后其他事务如果对account 进行了修改 已经不是20了，那么就会出现问题。

正确逻辑

直接从数据库中拿数据

update A set account = account + 10 where id = 1;

此时事务是修改读取当前数据，查询读取redolog数据，肯定就是最新的数据进行累计+10；

D：持久性

写入了硬盘，即使MySQL宕机，重启了数据依然存在

事务问题定位

#查询执行时间超过1秒的事务

SELECT

*

FROM

information_schema.innodb_trx

WHERE

TIME_TO_SEC( timediff( now( ), trx_started ) ) > 1;

#强制结束事务

kill 事务对应的线程id(就是上面语句查出结果里的trx_mysql_thread_id字段的值)

事务的注意点和优化

1. 不适用长事务，比如事务中很多操作修改、查询而且又慢，这时候会加锁导致其他事务来修改数据时等待。影响其他程序
2. 修改尽量放到后面，因为如果修改放前边就会占用这把锁，必须等提交才能释放，其他事务就要等待，如果最后修改，会减少占用锁的时间，从而减少其他程序的锁等待。
3. 使用远程调用设置超时时间，因为如果时间太久，事务一直不提交其他事务就要等待
4. 尽量将查询操作放到事务以外
5. 事务中避免一次性做很多操作，可以拆分多个事务
6. 能异步尽量异步