MySQL事务与隔离级别

MySQL之事务、隔离级别

1.事务概念

个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。

什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。
假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2.事务四大特征

事务是必须满足4个条件（ACID）
事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性：是指是不可分割的最小操作单位，一个事务包含的所有操作要么全部成功，要么全部失败回滚。 不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，一个事务执行之前和执行之后都必须处于一致性状态，数据库的完整性没有被破坏，数据总量不变。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
• 隔离性：多个事务之间，相互独立，跟隔离级别相关，数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行；
• 持久性：是指一个事务一旦被提交或回滚，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

3.事务应用场景

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

4.使用事务条件

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

5.事务命令

一、操作：

1. 开启事务： start transaction;

2. 回滚：rollback;

3. 提交：commit;
   注：如果手动开启了事务，但是忘记了手动进行回滚或者提交，直接把命令行窗口给关了或退出mysql了， 则系统帮我自动进行会回滚（即恢复到原始数据）；
   MySQL数据库是如果不输入 start transaction的话则事务是默认自动提交的

二.、事务提交的两种方式：

​ 1.自动提交：

• mysql就是自动提交的(即不需要自己开启事务)

• 一条DML(增删改)语句会自动提交一次事务。

  2.手动提交：

• Oracle 数据库默认是手动提交事务

• 需要先开启事务，再提交
  3.修改系统中事务的默认提交方式：

• 查看系统中事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交

• 修改系统的默认提交方式： set @@autocommit = 0;

6.操作事务案例(张三给李四转500元)

CREATE TABLE account (
			id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10),
			balance DOUBLE
		);
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;

UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';-- 1. 张三账户 -500
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 2. 李四账户 +500 	
SELECT * FROM account;

UPDATE account SET balance = 1000;
SELECT * FROM account;

START TRANSACTION;-- 开启事务
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';-- 1. 张三账户 -500
出错了
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 2. 李四账户 +500 出错了.	
COMMIT;-- 发现执行没有问题，则提交事务		
ROLLBACK;-- 发现出问题了，则回滚事务

7.事务的3个问题

存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样，这是由于在查询间隔，被另一个事务修改并提交了。
3. 幻读：一个事务操作(DML)数据表中所有记录（比如给表中数据加都加100快钱），另一个事务添加了一条数据，则第一个事务查询不到自己的修改；即幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。比如两个事务操作，A 事务查询状态为 1 的记录时，这时 B 事务插入了一条状态为 1 的记录，A 事务再次查询返回的结果不一样。
   注意：Mysql中看不到第三种幻读的情况，前面两种是可以演示出来的

8.隔离级别概念

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据(类似于并发)，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

9.四大隔离级别

隔离级别：
1.read uncommitted：读未提交
产生的问题：脏读、不可重复读、幻读
2.read committed：读已提交 （Oracle数据库默认这个级别）
产生的问题：不可重复读、幻读
即只有提交了数据，另一个事务才可以读到
3.repeatable read：可重复读 （MySQL数据库默认这个级别）
产生的问题：幻读
4.serializable：串行化
（串行化其实是个锁表的动作，如果一个事务在操作一张数据表，另外一个事务是不可以再进行操作表的，只有当这个锁打开了之后才可以进行操作，这与多线程加锁的机制很类似）
可以解决所有的问题
注意：隔离级别从小到大安全性越来越高，但是效率越来越低

10.隔离级别命令

1.数据库查询隔离级别：

• select @@tx_isolation; ( 5.7.20 版本之前用这个)
• select @@transaction_isolation; ( 5.7.20 版本之后用这个)
• 老版本 MySQL 比如 5 中用的是 tx_isolation，而应该是在 5.7.20 版本之后，用的是 transaction_isolation。

2.据库设置隔离级别：

• set global transaction isolation level 级别字符串;

11.演示read uncommitted

在第一个窗口

CREATE TABLE account (
			id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10),
			balance DOUBLE
		);
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;

在第一个窗口，设置成read uncommitted

set global transaction isolation level read uncommitted;

在第二个窗口查询发现事务隔离级别已经发生了变化

在第一个窗口开启事务

 start transaction;

在第一个窗口完成转账
注意：此时还并没有进行提交

update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;  

在第二个窗口查询，如果可以查询得到窗口1那边还没有提交的数据（500与1000）的话，则说明可以脏读
以下结果发生了脏读

select * from account;

在第一个窗口进行事务回滚

rollback;

在第二个窗口进行再次进行查询（发现钱又变了），即发生了不可重复读，两次读取到的数据不一样

select * from account;

在mysql数据库中演示不出来幻读，所以暂时不演示

12.演示read committed

在第一个窗口中设置成read committed
并开启事务

set global transaction isolation level read committed;

在第二个窗口开启事务

在第一个窗口完成转账

在第二个窗口进行查询，发现并没有查到500与1000，所以没有发生脏读，解决了脏读问题

第一个窗口进行事务提交

第二个窗口再次进行查询，发现数据变了，发生了“不可重复读问题”
注意：此时窗口二还仍在事务中

12.演示repeatable read

第一个窗口设置为repeatable read
并更新数据都为1000
并开启事务

第二个窗口开始事务

窗口1进行转账

窗口2进行查询

窗口1进行提交

窗口2再次进行查询
发现数据没有跟着读，即“可重复读“生效了

窗口2提交事务，再次查询，发现结果更新了

13.演示serializable

在第一个窗口中设置成serializable
并开启事务

set global transaction isolation level serializable ;

第二个窗口开启事务，并查询

第一个窗口完成转账的操作

第二个窗口进行查询
发现光标一直在闪，查询不了，原因是第一个窗口先开启事务，所以它先占用这个表，把这个表锁住了

第一个窗口进行提交

第二个窗口进行查询，又可以查询得到了

总结

总的来说，隔离级别和脏读、不可重复读以及幻象读的对应关系如下：

性能关系如图：