MySQL知识总结之事务、隔离级别

文章目录

MySQL知识总结之事务、隔离级别

1.事务概念2.事务四大特征3.事务应用场景4.使用事务条件5.事务命令6.操作事务案例(张三给李四转500元)7.事务的3个问题8.隔离级别概念9.四大隔离级别10.隔离级别命令11.演示read uncommitted12.演示read committed12.演示repeatable read13.演示serializable

MySQL知识总结之事务、隔离级别 1.事务概念

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

2.事务四大特征

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

原子性：是指是不可分割的最小操作单位，一个事务包含的所有操作要么全部成功，要么全部失败回滚。 不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，一个事务执行之前和执行之后都必须处于一致性状态，数据库的完整性没有被破坏，数据总量不变。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

隔离性：多个事务之间，相互独立，跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。

持久性：是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

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 数据库默认是手动提交事务
* 需要先开启事务，再提交
5.修改系统中事务的默认提交方式：
* 查看系统中事务的默认提交方式：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;

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快钱），另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
注意：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;

在第二个窗口开启事务

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 ;

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

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

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

第一个窗口进行提交

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