mysql事务，隔离级别简述

最新推荐文章于 2024-07-23 19:44:12 发布
最新推荐文章于 2024-07-23 19:44:12 发布
阅读量195 收藏
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lovetimiforever/article/details/108991763
版权 
说起关系型数据库，总是绕不开事务(transaction)一说，本文旨在简单叙述mysql的事务概念，并通过实际的sql数据来解释事务隔离级别。下面所有测试引擎均使用innodb。

1、什么是事务？

事务(transaction)是对数据库库操作的一个序列，当一个事务被提交给数据库后，应该要保证事务里的所有执行都成功，或者都失败。

2、事务的基本特性

一般我们是的事务的基本特性，就是指事务的ACID，
　　 1)、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做。
　　 2)、一致性（Consistency）： 指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
　　 3)、隔离性（Isolation）：数据库库不同的事务之间彼此没有任何干扰。
4)、持久性（Durability）：事务提交后，事务对数据库的所有更新将被持久化，不能回滚。

在具体解释事务前我们要先了解下数据库缓冲池的概念还有MVCC。

在这里插入图片描述

数据库缓冲池就是内存中的一块区域。通过内存的高速来弥补硬盘的速度对数据库的应影响。数据库读取的时候，会将读到的数据存放在缓冲池中。
mysql的基本数据查询流程如下在发起sql请求的时候，首先会在缓存中去查新缓存结果，这里的缓存只要查询有一个字节不同，就不会匹配缓存结果。
如果没有命中缓存，就会走如下流程。解析sql发起查询请求，查询到结果返回到缓存中去，并返回给客户端。

MVCC(Multi-Version Concurrency Control)多版本的并发控制协议,mysql的innodb引擎实现就是基于mvcc的。与之相对应的是LBCC(Lock-Based Concurrent Control)基于锁的并发控制。mvcc的主要优点在于读不加锁，读写不冲突。
在一个mvcc控制的系统中，读可以分为，快照读和当前读。

快照读： select * from wcs where xxxx; 这类以的简单查询，不加锁
当前读： 1>增删改语句以及
2>select * from xxx where xxx for update
3>select * from xxx where xxx lock in share mode;
当前读会读取最新的记录版本。并且会加锁，保证在事务中读取后，不允许被其他事务修改。
分为S锁(共享锁)(上述示例:3)和X锁(排他锁)(上述示例:1,2)，
这里不做mvcc的具体介绍，只为了引入快照读和当前读，以便后面的事务并发控制。

3、事务并发的问题

1）、脏读（dirty read），指的是一个事务读取了另一个事务修改未提交的数据
2）、不可重复读（unrepeatable read），和脏读有一定的关联，一个事务读取了一个数据，再次读取的时候读到了被另一个事务修改过的数据。
3）、幻读（phantom problem），一个事务读取到了另一个事务提交的新增或删除的结果。这和不可重复读有点类似。侧重点在新增删除
脏读很容易了解，在事务隔离级别最低的情况下会发生这个情况，
幻读和不可重复读比较奇怪。
假设有一个表User，有age,name,sex字段
事务A，查询sex=’男’的人，查出10个人
事务B,新增一个sex=’男’的人。并提交事务
事务A，继续操作这个时候，修改sex=’男’的人的age=15岁，发现修改结果为11个人。产生了幻读。

mysql事务隔离级别：

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否
Mysql innodb默认事务隔离级别是repeatable-read
顺带一提，oracle的默认事务隔离级别是read committed.

为什么serializable的事务隔离级别这么高，mysql的默认不是它呢。因为serializable是事务隔离级别最高的一种，因为它有mvcc控制调整到LBCC控制，对于他来说都是当前读，并对数据加锁。在该隔离级别下，读写冲突，并发性能下降。

4、具体示例

(1).查看当前会话mysql服务的事务隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

(2).查看当前会话和全局事务隔离级别

mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

(3).修改当前会话mysql事务隔离级别

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

(4).修改全局事务隔离级别

mysql>  set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| READ-UNCOMMITTED      | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

(5).事务并发问题示例
1.脏读
首先我们打开两个链接，分别修改事务隔离级别为read-uncommitted，

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

事务一，查询一张表，

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

mysql> update wcs set status = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

这个时候这个事务并没有提交。
再开启另外一个事务，查询这张表

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

查询到了第一个事务未提交的数据，属于脏读
第一个事务，rollback后，在查询

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

2.不可重复度
为了显示和脏读的区别，这里我们不在使用read uncommitted事务隔离级别，用read-committed隔离级别
打开两个连接分别修改事务隔离级别：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

事务一，开启事务后查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
###步骤一：
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

###步骤三
mysql> update wcs set status = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###步骤五
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务二，快开启事务后：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

###步骤二
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.01 sec)

###步骤四
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 2      |
+---------+-----------+-------+--------+
1 row in set (0.01 sec)


###步骤六
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

mysql>

1>事务一，先查询表，得到一个结果status为1
2>事务二，查询表，也得到了status为1
3>事务一，更新表，将status修改为2
4>事务二，查询表，得到表为1(该事务隔离级别，未发生脏读)
5>事务一，提交事务，
6>事务二，查询表，得到status为2（未重复读）

3.幻读
先打开两个链接，分别修改事务隔离级别为repeatable-read

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

事务一

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

###步骤一
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)
###步骤三
mysql> insert into wcs values('WCS2','0.0.0.1',30000,1);
Query OK, 1 row affected (0.01 sec)

###步骤五
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
| WCS2    | 0.0.0.1   | 30000 | 1      |
+---------+-----------+-------+--------+
2 rows in set (0.00 sec)

事务二

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

###步骤二
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

####步骤四
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

####步骤六
mysql> select * from wcs;
+---------+-----------+-------+--------+
| WCSNAME | IPADDRESS | PORT  | STATUS |
+---------+-----------+-------+--------+
| WCS1    | 127.0.0.1 | 20000 | 1      |
+---------+-----------+-------+--------+
1 row in set (0.00 sec)

###步骤七
mysql> update wcs set status = 2 where status = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

1>事务一，查询表，结果为一行数据WCS1，status为1
2>事务二，查询表，结果为一行数据WCS1，status 为1
3>事务一，插入一行数据WCS2，status为1
4>事务二，查询数据,一行数据WCS1，status为1（该事务隔离级别，未发生脏读）
5>事务一，提交事务，查询结果两行数据，WCS1，WCS2。status为1
6>事务二，查询数据,一行数据WCS1，status为1（该事务隔离级别，重复读）
7>事务二，更新数据status为1的，查询为1，更新matched为2，changed为2，发生了幻读。

中年失业码农
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL事务篇：ACID原则、事务隔离级别及事务机制原理剖析
Huangjiazhen711的博客
10-11 863
一点点看到这里，《MySQL事务篇》也就接近了尾声，在本篇中对事务机制一点点去引出，慢慢的到事务机制的概述、并发事务的问题、事务的隔离级别、事务的实现原理等诸多方面进行了全面剖析，但大家应该也略微有些不尽兴，毕竟对于隔离级别的具体实现并未讲到，这是由于MySQL事务与锁机制之间有着千丝万缕的关系，所以在《MySQL锁篇》中会再次详细讲到事务隔离机制的。当然，由于目前是分布式/微服务架构横行的时代，所以也引出了新的问题，即分布式事务问题。
【MySQL —— 事务】四大特性、隔离级别
AlinaQ05的博客
08-06 977
事务诞生的目的就是为了把若干个独立的操作打包成一个整体，这样的一组操作，要么全都执行，要么都不执行。事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位。
MySQL事务隔离级别
03-21
NULL 博文链接：https://cuishuangjia.iteye.com/blog/964885
MYSQL事务隔离级别
PPprogrammer的博客
02-18 375
mysql有4种事务隔离级别，分别为：read uncommited(读未提交数据) read commited(读已提交数据) ，repeatable read(可重复读) 和 serializable(串行化)。其默认隔离级别为为repeatable read。这些隔离级别是为了解决同时运行多个事务时，当这些事务访问数据库中相同的数据时引发的并发问题。一般有三种：脏读，不可重复读，幻读，在下...
一篇文章搞懂MySQL的事务与隔离级别
最新发布
qq_46863837的博客
07-23 707
MySQL的事务是什么？什么是ACID？什么是隔离级别？一篇文章全搞懂
Mysql事务隔离级别
茹煜得氺的博客
07-16 234
Transaction Isolation Levels(事务隔离级别)： 事务隔离是数据库处理的基础之一，Isolation代表ACID原则的"I"。当多个事务对同一记录进行修改或查询的时候，事务隔离级别很好的协调了性能和可靠性、一致性和查询结果的复制性的关系。   Transaction Isolation Levels提供4种隔离级别(按级别由低到高排列)： （1)，READ_UNC...
简述MySql四种事务隔离级别
12-14
在SQL标准中定义了四种隔离级别, 每一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。 下面简单地介绍一下四种隔离级别。 1.READ ...
MySQL事务并发问题和隔离级别
drizzletowne的博客
10-24 2240
一 事务的基本语法 事务的官方定义： Transactions are atomic units of work that can be committed or rolled back. 详情见官网 事务常用的SQL语句： begin; -- 开始，还可以使用下列语法： start transaction; commit; -- 提交：使得当前的修改确认 rollback; -- 回滚：使得当前的修改被放弃 我们常用的SQL语句其实都是隐式提交的，如：
MySQL数据库--事务TCL
weixin_43739146的博客
10-26 149
事务：一个或多个SQL语句组成一个执行单元，这个单元要么全部执行，要么全部不执行。如果执行过程中某条语句执行出错，整个单元都会回滚，返回最开始的状态。 隐式事务： 事务没有明显的开启和结束的标记（如：insert、update、delete）。 显示事务 事务具有明显的开启和结束标记。 前提：必须设置自动提交功能为禁用。 #(1)开启事务 show variables like 'autocommit'; --查看自动提交服务 #或 select @@autocommit; set autocommit =
请简述MySQL的事务特性
05-28
MySQL的事务（Transaction）是指一个或多个SQL语句组成的逻辑操作单元，这些SQL语句要么全部执行成功，要么...在实际应用中，我们需要根据具体的业务场景和需求来选择合适的事务隔离级别，以保证数据的一致性和完整性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值