mysql数据库4种隔离级别

我们知道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

五、间隙锁与MVCC模式

间隙锁
间隙锁是innodb中行锁的一种，但是这种锁锁住的不止一行数据，它锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦。

这幅图中多了一个gap锁，而且gap锁不是加在记录上的，倒是像加在两条记录之间的，为的是防止别的事务在事务A多次读取同一数据时，插入新的满足条件的记录并提交。
mvcc(多版本并发控制)
何为多版本并发控制？即在每一行后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务启动的时候，都有一个唯一递增的版本号

在插入操作时：记录的创建版本号就是当前事务版本号

id	name	create version	delete version
1	test	1

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。
比如，针对上面那行记录，事务Id为2 要把name字段更新

id	name	create version	delete version
1	test	1	2
1	new_name	2

3.删除操作也一样

id	name	create version	delete version
1	new_name	2	3

查询操作
从上面的描述可以看到，在查询时符合以下两个条件的记录才能被查询到：
删除版本号大于当前事务版本号，即删除操作是在当前事务启动之后做的
创建版本号小于或者等于当前事务版本号，记录创建是在事务之前启动的。
这里很巧妙的通过版本号来减少了锁的争夺，相比于锁它提高了系统性能
只有rc和rr模式两种事务隔离界别才能使用mvcc