MySql特性和隔离级别

MySql事物的四个特性和四个隔离级别

事物四个属性(ACID)

事物: 在数据库中执行的所有以分号结束的sql操作都可以看做一次事物, 在非数据库环境下, 对应的就是一次功能的实现, 可以包含多个一条或多条sql代码(例: 先查出某个表中的数据, 然后对数据进行更新, 整个过程就是一个事物)

1. 原子性(Atomicity)
   事物中的所有操作要么成功, 要么失败后回滚, 因此事物操作成功后会把成功操作应用到数据库, 失败也不会影响数据库
2. 一致性(Consistency)
   一个事物执行之前和执行之后不会对数据库的一致性产生影响, 前后都是保持一致性状态.(例如: 用户A 和用户B 共有1000元, 不管他们之间如何转账, 发红包, 或者有网络中断的情况, 等操作都结束后, 总金额还是1000)
3. 隔离性(Isolation)
   当用户并发访问数据库时, 比如同时操作同一张数据表时, 为每一个用户开启的事物,不能被其他事物影响, 多个并发事物相互隔离. (数据库的隔离机制实现)
4. 持久性(Durability)
   一个事物当他提交后, 对数据库中数据的改变就是永久的. 不会因为数据库故障的情况影响事物的后续处理. 因为用户事务操作完成后, 用户就认为事件已经执行, 对数据的操作也成功了, 不能因为数据库故障而导致事物没有执行, 这是不允许的.

事物的隔离级别

1. 为什么设置隔离级别?

数据库操作时, 并发 的情况下会出现以下问题:

• 更新丢失(Lost update)
  当多个线程操作, 基于同一个查询结构对表中的记录进行修改, 后面的操作会覆盖前面的更新操作, 导致前面更新的数据丢失了, 这就是更新丢失, 因为没有执行任何锁操作, 导致并发事物没有相互隔离.
  第1类: 事务A撤销时，把已经提交的事务B的更新数据覆盖了。

取款事物A	转账事物B
开始事物	***
***	开始事物
查询账户余额为1000	***
***	查询账户余额为1000
***	汇入100元, 账户余额更新为1100
***	提交事物
取出100元,账户余额更新为900	***
撤销事物	***
余额恢复为1000(丢失更新)	***

第2类：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

取款事物A	转账事物B
开始事物	***
***	开始事物
查询账户余额为1000	***
***	查询账户余额为1000
***	汇入100元, 账户余额更新为1100
***	提交事物
取出100元,账户余额更新为900	***
提交事物	***
余额为900(丢失更新)	***

解决方法: 对行数据加锁, 只允许并发一个更新事物

• 脏读(dirty read)
  脏读: 一个事物A读取事物B还没提交的事物并且在此基础上对数据进行操作, 当B事物回滚后,A读取的数据就是脏数据

取款事物A	转账事物B
开始事物	***
***	开始事物
查询账户余额为1000	***
***	查询账户余额为1000
***	汇入100元, 账户余额更新为1100
查询账户余额为1100(脏数据)	***
***	撤销事物恢复余额为1000
取出200元, 账户余额为900	***
提交事物	***

解决办法: 在一个事物提交前, 任何事物都不可读取其修改过的值

• 不可重复读(non-repeatable reads)
  一个事物对同一行数据重复读取两次, 得到了两次不同的结果, 事物A读取某一行数据后, 事物B对该行数据进行了修改, 事物A再次读取该行数据时, 数据和第一次读取的结果不一致.

取款事物A	转账事物B
开始事物	***
***	开始事物
查询账户余额为1000	***
***	查询账户余额为1000
***	汇入100元, 账户余额更新为1100
***	提交事物
查询账户余额为1100(不可重复读)	***

解决办法: 只有在修改数据完全提交后,才读取数据

• 幻读
  执行同一条select语句会出现不同的结果, 第二次读会增加一数据行，并没有说这两次执行是在同一个事务中, 一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为 游标稳定性 的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。

统计金额事物A	转账事物B
开始事物	***
***	开始事物
统计金额存款为10*1000	***
***	新增一个账户,存款为1000
***	提交事物
查询账户余额为11*1000(幻读)	***

解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。

2. 事物隔离级别

数据库事物的隔离级别有4种, 由低到高分为Read uncommited(未授权读取, 读未提交), Read commited(授权读取, 读提交), Repetable read(可重复读取), Serializable(序列化) , 这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

• Read uncommited(未授权读取, 读未提交)
  简单理解： 一个事务可以读取另一个尚未提交事务的修改数据
  一个事物开始写数据, 不允许其他事物对相同数据进行写操作, 但是其他事物可以读取数据, 可以通过X锁(排他锁)实现, 有脏读, 可重复读, 幻读产生
• Read commited(授权读取, 读提交)
  简单理解： 一个事务读取另一个已经提交的事务
  读取数据的事物允许其他事物访问, 隔离了脏读, 有 可重复读, 幻读产生
• Repetable read(可重复读取)
  每个事务只关注自己事务开始查询到的数据值，无论事务查询同一条数据多少次，该数据改了多少次，都只查询到事务开始之前的数据值。（MySQL 默认隔离级别）, 可参考 可重复读例子讲解 可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本),这样方式下, 避免了不可重复读取和脏读，但是有时可能出现幻象读

当前读，快照读： 《快照读、当前读和MVCC》

• Serializable(串行读)
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。花费最高，性能很低, 不仅可以避免脏读、不可重复读，还避免了幻像读。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。MySQL的默认隔离级别就是Repeatable read。

参考

https://www.cnblogs.com/limuzi1994/p/9684083.html
https://www.cnblogs.com/AlmostWasteTime/p/11466520.html
https://blog.youkuaiyun.com/luzhensmart/article/details/84069545