MySQL---事务+事务隔离级别

最新推荐文章于 2023-06-21 15:17:44 发布

keep one's resolveY

最新推荐文章于 2023-06-21 15:17:44 发布

阅读量190

点赞数

分类专栏：数据库+ES 文章标签： mysql 数据库 database

原文链接：https://blog.csdn.net/w_linux/article/details/79666086

版权

数据库+ES 专栏收录该内容

27 篇文章 3 订阅

订阅专栏

一、事务定义

Transaction
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

二、转账操作理解事务

关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分—也就是说银行账户转账是一个事务

以下是银行账户表t_act(账号、余额)，进行转账操作

actno		balance
1			500
2			100

转账操作

update t_act set balance=400 where actno=1;
update t_act set balance=200 where actno=2;

以上两台DML语句必须同时成功或者同时失败。最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务

三、事务四大特征(ACID)

原子性(A)：事务是最小单位，不可再分
一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：并发时，事务A和事务B之间具有隔离性
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

五、和事务相关的两条重要的SQL语句(TCL)

commit:提交
rollback：回滚（事务commit之前可以rollback，commit之后rollback就无效了）

七、事务与数据库底层数据

在事务进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据

八、在MySQL中，事务提交与回滚

在MySQL中，默认情况下，事务是自动提交的,自动提交机制是可以关闭的：

方式一：
start transaction;  //手动开启事务
DML语句...
commit;   //手动提交事务(事务成功地结束)

start transaction;  //手动开启事务
DML语句...
rollback;   //手动回滚事务(事务失败地结束)

方式二：
关闭自动提交：
set autocommit=off;
或：set session autocommit=off;
打开自动提交
set autocommit=on;
或：set session autocommit=on;
以上打开和关闭自动提交机制，只对当前会话有效

对 t_user 进行提交和回滚操作

提交操作(事务成功)

start transaction
DML语句

commit

  mysql> start transaction;  #手动开启事务，mysql命令行输入这个后，
                             #就关闭了自动提交功能，需要手动提交和回滚
  mysql> insert into t_user(name) values('pp');
  mysql> commit;             #commit之后即可改变底层数据库数据
  mysql> select * from t_user;
  +----+------+
  | id | name |
  +----+------+
  |  1 | jay  |
  |  2 | man  |
  |  3 | pp   |
  +----+------+
  3 rows in set (0.00 sec)

回滚操作(事务失败)

start transaction
DML语句

rollback

  mysql> start transaction;
  mysql> insert into t_user(name) values('yy');
  mysql> rollback;
  mysql> select * from t_user;
  +----+------+
  | id | name |
  +----+------+
  |  1 | jay  |
  |  2 | man  |
  |  3 | pp   |
  +----+------+
  3 rows in set (0.00 sec)

九、事务四大特性之一————隔离性(isolation)

MySql默认是隔离级别是可重复读(历史原因造成)，Oracle，SqlServer 的默认事务隔离级别是读已提交(Read Commited)；

一般互联网项目中mysql的事务隔离级别设置成 Read Commited 读已提交，因为实际工作中不需要可重复读，可重复读有多缺点（死锁概率高；在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行；等等）参考：互联网项目中mysql应该选什么事务隔离级别转(示例代码)_136.la

Mysql的四种隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，
哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。


Read Uncommitted（读取未提交内容）  ru
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，
因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。


Read Committed（读取提交内容）     rc
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：
一个事务只能看见已经提交事务所做的改变。
这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），
因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

  
Repeatable Read（可重读）         rr
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，
当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制
解决了该问题。


Serializable（可串行化）         s
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

隔离级别的不同会导致出现不同的问题：

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

测试Mysql的隔离级别：

下面，将利用MySQL的客户端程序，我们分别来测试一下这几种隔离级别，两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。

Read Uncommitted（读取未提交内容）：

（一）、将A的隔离级别设置为read uncommitted(未提交读)

set session transaction isolation level read uncommitted;

查看隔离级别是否设置成功

select @@transaction_isolation （mysql版本 8.0 以后）

select @@tx_isolation （mysql版本 8.0 之前）

查看mysql版本 

> status

 

A：启动事务，此时select读取数据为初始状态

 start transaction;

B：启动事务，更新数据，但不提交

 start transaction;

A：再次读取数据，发现数据已经被修改了，这就是所谓的“脏读”


B：回滚事务

rollback;
 
A：再次读数据，发现数据变回初始状态

经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，
此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

Read Committed（读取提交内容）：

（二）、将客户端A的事务隔离级别设置为read committed(已提交读)

 set session transaction isolation level read committed;

A：启动事务，此时select读取数据为初始状态

B：启动事务，更新数据，但不提交

A：再次读数据，发现数据未被修改

B：提交事务

A：再次读取数据，发现数据已发生变化，说明B提交的修改被事务中的A读到了，
这就是所谓的“不可重复读”

 

经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，
即事务A在两次查询的数据不一致，
因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，
但不要求可重复读。

Repeatable Read（可重读）：

(三)、将A的隔离级别设置为repeatable read(可重复读)

A：启动事务，此时select读取数据为初始状态

B：启动事务，更新数据，但不提交

A：再次读取数据，发现数据未被修改

B：提交事务

A：再次读取数据，发现数据依然未发生变化，这说明这次可以重复读了

B：插入一条新的数据，并提交

A：再次读取数据，发现数据依然未发生变化，虽然可以重复读了，
但是却发现读的不是最新数据，这就是所谓的“幻读”

A：提交本次事务，再次读取数据，发现读取正常了

由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，
而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。
但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，
但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。
像以上的实验，就没有出现数据幻读的问题

Serializable（可串行化）：

(四)、将A的隔离级别设置为可串行化(Serializable)

A：启动事务，此时数据为初始状态

B：发现B此时进入了等待状态，原因是因为A的事务尚未提交，只能等待（此时，B可能会发生等待超时）

A：提交事务

B：发现插入成功

serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，
直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

十、隔离级别与一致性关系

十一、设置事务隔离级别

方式一

可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

该选项值可以是：

  – READ-UNCOMMITTED
  – READ-COMMITTED
  – REPEATABLE-READ
  – SERIALIZABLE

  •	例如：
  [mysqld]
  transaction-isolation = READ-COMMITTED

方式二

通过命令动态设置隔离级别
• 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
• 其语法模式为：

  		SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
  		其中的<isolation-level>可以是：
  	–	READ UNCOMMITTED
  	–	READ COMMITTED
  	–	REPEATABLE READ
  	–	SERIALIZABLE
  	•	例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

十二、隔离级别的作用范围

•	事务隔离级别的作用范围分为两种： 
–	全局级：对所有的会话有效 
–	会话级：只对当前的会话有效 
•	例如，设置会话级隔离级别为READ COMMITTED ：
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
•	设置全局级隔离级别为READ COMMITTED ： 
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

十三、查看隔离级别

•	事务隔离级别的作用范围分为两种： 
–	全局级：对所有的会话有效 
–	会话级：只对当前的会话有效 
•	例如，设置会话级隔离级别为READ COMMITTED ：
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
•	设置全局级隔离级别为READ COMMITTED ： 
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

题外话：

事务是数据库 DML 语句中的，有 ACID 原则，数据库行锁是并发操作同一数据时防止数据混乱的。

悲观锁（行锁）、乐观锁、JUC的锁区分详见这2个博客：

MySql悲观锁（行锁）和乐观锁_逮虾户<的博客-CSDN博客

MySQL行锁详解（共享锁和排他锁）及应用级JUC锁和数据库锁的区别_逮虾户<的博客-CSDN博客

keep one's resolveY

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL---事务+事务隔离级别

一、事务定义Transaction 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元) 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同二、转账操作理解事务关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分————————也就是说银行账户转账是一个事务以下
复制链接

扫一扫