mysql隔离级别测试

隔离级别一直都有了解，但是从来没有测试过，今天兴趣来了刚好了来测一测吧。

隔离级别共4种：

读未提交（READ UNCOMMITTED）

读已提交 （READ COMMITTED）

可重复读 （REPEATABLE READ）

串行化 （SERIALIZABLE）

读未提交：即A事务新增一条数据后，还没有执行提交事物的命令，此时B事务就可以读取A事务提交的这条数据，如果此时A事务回滚了，那么B事务读取到的就是脏数据，此时就产生了脏读。

读已提交：即事务A新增一条数据后，还没有执行提交事物的命令，此时B事务读取不到该条数据，所以读已提交可以防止脏读。

这里只测试读已提交，其他的类似。

首先新建一个测试表：

CREATE TABLE `test_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='测试表';

现在表中的数据（随便加一条即可，这里我已经操作了很多次所以有多条数据）：

 select @@tx_isolation;#查看当前隔离级别，mysql默认隔离级别就是REPEATABLE-READ（可重复度）

把事务隔离级别改为读已提交：

set session transaction isolation level read UNCOMMITTED;#设置为READ-COMMITTED（读已提交）

关闭事务的自动提交：

先查看一下事务是否自动提交（默认就是自动提交ON）

show variables like 'autocommit';

 set autocommit = 0;#(关闭事务自动提交)

 

 A事务插入一条数据，并且不提交事务：

 此时在表中查看是可以看到的（其实不是真的插入了,只是为了给你看到效果）

 然后在另外一个navicat窗口去查看（注意：另外一个窗口也要设置隔离级别，因为navicat中设置隔离级别等只是对当前窗口有效）

查看如下：没有刚才插入的数据

执行提交事物命令：

在另一个窗口（B事务）再次查看：如下已经有了这条数据

 也可以测试update一条数据，效果是一样的。

 即测试读已提交成功，隔离级别为读已提交时，事务未提交，另外一个事务不可读取到数据。

这里例子没选择好，如果不选择insert语句，而是update语句的话，我们就可以很明显的发现读已提交隔离级别的问题：不可重复读，即同一个事务内每次读取同一条数据的结果不一样。

A事务更新数据未提交时，B事务读取到原来的数据；A事务提交后，B事务再次读取，读取到了修改后的数据（即读取两次数据，结果不一样）

以下copy到了一个比较全的文章：

原文链接：https://www.cnblogs.com/cxscode/p/13656178.html

如何设置事务隔离级别

我们可以通过以下语句查看当前数据库的隔离级别，通过下面语句可以看出我使用的 MySQL 的隔离级别是 REPEATABLE-READ，也就是可重复读，这也是 MySQL 的默认级别。

Copy

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

或者：

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

当然我们也能手动修改事务的隔离级别：

Copy

set [作用域] transaction isolation level [事务隔离级别];
作用域包含：
SESSION：SESSION 只针对当前回话窗口
GLOBAL：全局生效

隔离级别包含：
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

我们来测试一下各个隔离级别对事务的影响。

新建表：

Copy

CREATE TABLE `test_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='测试表';

插入一些测试数据。

读未提交（READ UNCOMMITTED）

首先设置事务隔离级别：

Copy

set global transaction isolation level READ UNCOMMITTED;

注意：设置完全局隔离级别只对新打开的 session 有效，历史打开的是不会受到影响的。

首先关闭事务自动提交：

Copy

set autocommit = 0;

开启事务 A：

Copy

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert test_db (name) values ('xiaocee');
Query OK, 1 row affected (0.01 sec)

在事务A 中插入一条数据，并未提交事务。

接着开启事务B：

Copy

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

事务 B 中能够查到这条数据。即不同的事务能读到对方未提交的数据。连脏读都无法解决，可重复读和幻读更没法解决。

读已提交

读已提交的数据肯定能解决脏读问题，但是对于幻读和不可重复读无法将解决。

首先设置事务隔离级别：

Copy

set global transaction isolation level READ COMMITTED;

现在数据库数据如下：

Copy

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming2 |
|  2 | xiaohong  |
|  3 | xiaowei   |
|  4 | xiaowei1  |
|  5 | xiaoli    |
|  6 | xiaoche   |
|  8 | xiaoche   |
| 10 | xiaoche   |
| 12 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

开启事务 A 将 id=1 的数据改为 “xiaoming3”：

Copy

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

mysql> update test_db set name = 'xiaoming3' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这里事务 A 未提交，接着开启事务B 做第一次查询：

Copy

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

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming2 |
+----+-----------+
9 rows in set (0.00 sec)

事务B查询还是原始值。

下面提交事务 A：

Copy

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

接着在事务 B 中再查询一次：

Copy

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
1 row in set (0.00 sec)

当然这次查到的肯定是人家已提交的数据。这里发生的问题就是不可重复读：即同一个事务内每次读取同一条数据的结果不一样。

可重复读

可重复读隔离级别的含义就是重读每次都一样不会有问题。这就意味着一个事务不会读取到别的事务未提交的修改。但是这里就会有另一个问题：在别的事务提交之前它读到的数据不会发生变化，那么另一个事务如果将结果 a 改为 b，接着又改为了 a，对于当前事务来说直到另一个事务提交之后它再读才会获取到最新结果，但是它并不知道这期间别的事务对数据做了更新，这就是幻读的问题。

首先设置事务隔离级别：

Copy

set global transaction isolation level REPEATABLE READ;

现在数据库数据如下：

现在数据库数据如下：

Copy

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
|  2 | xiaohong  |
|  3 | xiaowei   |
|  4 | xiaowei1  |
|  5 | xiaoli    |
|  6 | xiaoche   |
|  8 | xiaoche   |
| 10 | xiaoche   |
| 12 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

开启事务 A 将 id=1 的数据改为 “xiaoming4”：

Copy

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

mysql> update test_db set name = 'xiaoming3' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这里事务 A 未提交，接着开启事务B 做第一次查询：

Copy

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

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
9 rows in set (0.00 sec)

事务B查询还是原始值。

下面提交事务 A：

Copy

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

接着在事务 B 中再查询一次：

Copy

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
1 row in set (0.00 sec)

查询到还是一样的结果，下面提交事务B ，然后再查询：

Copy

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

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming4 |
+----+-----------+
1 row in set (0.00 sec)

提交完之后再查就是 “xiaoming4”。

这也意味着在事务B未提交期间，事务A做任何操作对B来说都是盲视的。

串行化读

串行化读意味着将所有事务变为顺序执行，所以就不存在上述的四种问题，当然这也意味着效率是最低的。

有了隔离级别的概念，那隔离级别又是怎么实现的呢？我们接下来要讲的锁机制就是实现隔离级别的重要手段。