mysql数据库4种隔离级别

作者: ZMRWEGo | 来源:发表于2018-12-13 15:29 被阅读6次

聊聊MySQL的隔离级别
MySQL 事务隔离级别
Mac系统mysql设置事务隔离级别
Mac系统设置MySQL事务隔离级别永久版
为什么MySQL 默认隔离级别是RR，又被阿里设置为RC
MySQL 事务隔离级别解析和实战
Spring Transactional的传播特性/隔离级别
MySQL数据库锁、MVCC与事务隔离级别的关系
数据库四种隔离级别
数据库隔离级别相关

我们知道Mysql的存储引擎有很多种，默认的为InnoDB，它也是mysql中唯一支持事务的存储引擎。

一、事务的基本要素(ACID)

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发特性

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2. 不可重复读：事务A在一次事务中多次读取同一数据，但是事务B在数据A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDEFG等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A修改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

三、Mysql事务隔离级别

下表为事务的隔离级别与可能会引发的并发问题

事务隔离级别	脏读	不可重复读	幻读
读未提交(read-uncommitted)	是	是	是
不可重复读(read-committed)	否	是	是
可重复读(repeatable-read)	否	否	是
串行化(serializable)	否	否	否

mysql8中使用select @@transaction_isolation;查看数据库默认隔离级别

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

四、隔离级别演示

先查看我们的数据库存储引擎
show variables like '%storage_engine%';

+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| default_storage_engine           | InnoDB    |
| default_tmp_storage_engine       | InnoDB    |
| disabled_storage_engines         |           |
| internal_tmp_disk_storage_engine | InnoDB    |
| internal_tmp_mem_storage_engine  | TempTable |
+----------------------------------+-----------+

读未提交
1.客户端A

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

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

mysql> select * from mg_user;
+-------------+----------------------------------+
| mg_username | mg_password                      |
+-------------+----------------------------------+
| zcl         | 202cb962ac59075b964b07152d234b70 |
+-------------+----------------------------------+
1 row in set (0.00 sec)

在客户端A的事务提交执勤啊，打开另一个客户端B，更新表mg_user

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

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

mysql> update mg_user set mg_password = 10086 where mg_username = 'zcl';
Query OK, 1 row affected (0.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10086       |
+-------------+-------------+
1 row in set (0.00 sec)

这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10086       |
+-------------+-------------+
1 row in set (0.01 sec)

一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：

mysql> rollback;
Query OK, 0 rows affected (0.15 sec)
mysql> select * from mg_user;
+-------------+----------------------------------+
| mg_username | mg_password                      |
+-------------+----------------------------------+
| zcl         | 202cb962ac59075b964b07152d234b70 |
+-------------+----------------------------------+
1 row in set (0.00 sec)

在客户端A执行select * from mg_user;

mysql> select * from mg_user;
+-------------+----------------------------------+
| mg_username | mg_password                      |
+-------------+----------------------------------+
| zcl         | 202cb962ac59075b964b07152d234b70 |
+-------------+----------------------------------+
1 row in set (0.00 sec)

读已提交

客户端A

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

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

mysql> select * from mg_user;
+-------------+----------------------------------+
| mg_username | mg_password                      |
+-------------+----------------------------------+
| zcl         | 10086 |
+-------------+----------------------------------+
1 row in set (0.00 sec)

在客户端A的事务提交之前，打开另一客户端B，更新表account；

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

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

mysql> update mg_user set mg_password = 10010 where mg_username = 'zcl';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

这时客户端B还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题；

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10086       |
+-------------+-------------+
1 row in set (0.00 sec)

客户端B事务提交

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题。

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10086       |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

可重复读

打开客户端A，设置当前事务模式为repeatable read

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

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

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

mysql> update mg_user set mg_password = 10086 where mg_username = 'zcl';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.14 sec)

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10086       |
+-------------+-------------+
1 row in set (0.00 sec)

在客户端A查询表account的所有记录，没有出现不可重复读的问题。

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 10010       |
+-------------+-------------+
1 row in set (0.00 sec)

4 . 在客户端执行字符串拼接，mg_password的值变为1008611，它用的是步骤2中的10086来计算的，值也就变为了1008611.可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

mysql> update mg_user  set mg_password = concat(mg_password,'11') where mg_username = 'zcl';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 1008611     |
+-------------+-------------+
1 row in set (0.00 sec)

重新打开客户端B，插入一条新数据后提交

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

mysql> insert into mg_user values('cl','10000');
Query OK, 1 row affected (0.05 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| cl          | 10000       |
| zcl         | 10086       |
+-------------+-------------+
2 rows in set (0.00 sec)

在客户端A查询记录，没有查出新增数据，所以出现了幻读

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| zcl         | 1008611     |
+-------------+-------------+
1 row in set (0.00 sec)

串行化

打开一个客户端A，并设置当前事务模式为serializable，查询表

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

mysql> start transaction;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from mg_user;
+-------------+-------------+
| mg_username | mg_password |
+-------------+-------------+
| cl          | 10000       |
| zcl         | 1008611     |
+-------------+-------------+
2 rows in set (0.00 sec)

打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败。mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

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

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

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

聊聊MySQL的隔离级别
原文：聊聊MySQL的隔离级别 | MySQL隔离级别原理参考：oracle - mysql - 数据库事务隔离级...
MySQL 事务隔离级别
一、MySQL 数据库命令查看数据库版本：查看数据库现在的隔离级别修改隔离级别：级别参数：1.READ-U...
Mac系统mysql设置事务隔离级别
Mac系统mysql设置事务隔离级别 MySQL数据库事务隔离级别主要有四种： Serializable 串行化，...
Mac系统设置MySQL事务隔离级别永久版
Mac系统设置MySQL事务隔离级别永久版之前介绍过一种暂时性的更改MySQL数据库事务隔离级别的设置方法...
为什么MySQL 默认隔离级别是RR，又被阿里设置为RC
我们知道，我们可以通过这个命令查看数据库当前的隔离级别，MySQL 默认隔离级别是RR. select @@tx_...
MySQL 事务隔离级别解析和实战
MySQL 事务隔离级别解析和实战 1、MySQL 隔离界别查看查看回话隔离级别查看系统隔离级别 2、MySQ...
Spring Transactional的传播特性/隔离级别
一：隔离级别项目中用到的数据库是mysql，默认的隔离级别是REPEATABLE_READ重复读，pgsql与o...
MySQL数据库锁、MVCC与事务隔离级别的关系
上一篇文章中讲了MySQL的事务隔离级别（mysql数据库事务的隔离级别），有个问题一直困扰了自己很长时间，MyS...
数据库四种隔离级别
高性能MYSQL的事物隔离级别数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Rea...
数据库隔离级别相关
1.数据库隔离级别目前数据库MySQL 并发事务下,在不同的隔离级别下,可能会出现四个问题.分别是:脏写(Lost...