mariadb事务隔离级别相关实验

• 关于SQL的隔离级别

  成都网站设计、网站建设的关注点不是能为您做些什么网站，而是怎么做网站，有没有做好网站，给成都创新互联公司一个展示的机会来证明自己，这并不会花费您太多时间，或许会给您带来新的灵感和惊喜。面向用户友好，注重用户体验，一切以用户为中心。

SQL标准定义了4类隔离级别，如下所示：
1. Read Uncommitted (读取未提交内容)
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读(Dirty Read)
　
2. Read Committed (读取提交内容)
这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read)，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
　
3. Repeatable Read (可重复读)
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
　
4. Serializable (可串行化)
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在Mariadb中，这4种隔离级别有可能产生的问题如下图所示：

---

• 相关实验　
  下面分别针对不同的隔离状态进行测试：
  准备的环境如下所示：
  在Mariadb服务器端，创建一个数据库名为hldb，创建一个INNODB引擎的数据表名为test，并提前插入两条数据

准备两个MySQL客户端线程，连接到服务器上面：

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+


MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+

---

 
1. Read uncommitted(读未提交) 
首先将两个客户端的隔离级别分别都设置为Read uncommitted模式：

MariaDB [hldb]> select connection_id();                       
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select @@session.tx_isolation;                  
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

......
......

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

第一步，在再在id=6的客户端上面(以后简称为id6)上面完成一次查询。第二步，在两个客户端上面同时打开start transaction。第三步，两个客户端都打开了start transaction之后，再在id=5的客户端上面(以后简称为id5)上面插入一条数据。第四步，在id5自身的会话上面再用select进行查询。第五步，在id6会话上面用select进行查询。

在id5上面看到的结果如下：

MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
+----+------+
2 rows in set (0.00 sec)

MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

在id6上面看到的结果如下：

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
+----+------+
2 rows in set (0.00 sec)

MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

结论： 
可以看到，如果事务级别设定成为Read Uncommitted(读未提交)，在id5的事务并未提交的状态下，id6的事务是可以将其未提交的事务查询到的。这种能够读取到未提交事务的现象，称为脏读

---

2. Read Committed (读取提交内容) 
将两个客户端的事务隔离级别均设定为Read Committed，并且查询一下test数据表里面的内容

MariaDB [hldb]> set @@session.tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

......
......

MariaDB [hldb]> set @@session.tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

第一步，在两个客户端上面开启start transaction。第二步，在id5上面添加一条记录，同时在id5, id6上面查询。第三步，在id5上面删除一条记录，同时在id5, id6上面查询。第四步，在id5上面提交事务，并且在id5, id6上面查询。

在id5客户端上面的操作结果如下所示：

第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

第二步：
MariaDB [hldb]> insert into test(nm) values('d');
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
| 23 | d    |
+----+------+
4 rows in set (0.00 sec)

第三步：
MariaDB [hldb]> delete from test where nm='b';
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 22 | c    |
| 23 | d    |
+----+------+
3 rows in set (0.00 sec)

第四步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 22 | c    |
| 23 | d    |
+----+------+
3 rows in set (0.00 sec)

在id6客户端上面的操作如下所示：

第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

第二步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

第三步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 21 | b    |
| 22 | c    |
+----+------+
3 rows in set (0.00 sec)

第四步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 22 | c    |
| 23 | d    |
+----+------+
3 rows in set (0.01 sec)

结论： 
在Read Committed(读取提交内容)的隔离级别下，解决了脏读现象，但是带来了另外一种现象：不可重复读。id5事务提交的前后，id6在同一个事务中，所查询到的内容不一致。

---

3. Repeatable Read(可重复读) 
首先将两个客户端的事务隔离级别都设置为Repeatable Read，并查询test数据表里面的内容：

MariaDB [hldb]> set @@session.tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

......
......
MariaDB [hldb]> set @@session.tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第一步，在两个客户端上面开启start transaction。第二步，在id5上面添加一条记录，同时在id5, id6上面查询。第三步，在id5上面删除一条记录，同时在id5, id6上面查询。第四步，在id5上面提交事务，并且在id5, id6上面查询。第五步，在id6上面提交事务，并且在id6上面查询。

在id5客户端上面的操作结果如下所示：

第一步：
MariaDB [hldb]> start transaction;               
Query OK, 0 rows affected (0.00 sec)

第二步：
MariaDB [hldb]> insert into test(nm) values('b');  
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
| 25 | b    |
+----+------+
2 rows in set (0.00 sec)

第三步：
MariaDB [hldb]> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 25 | b    |
+----+------+
1 row in set (0.00 sec)

第四步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 25 | b    |
+----+------+
1 row in set (0.00 sec)

在id6上面的操作结果如下所示：

第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

第二步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第三步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.01 sec)

第四步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第五步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 25 | b    |
+----+------+
1 row in set (0.00 sec)

结论： 
由上述实验可以看出，事务隔离级别Repeatable read(可重复读)和Read Committed(已提交读)的不同之处在于，在同一事务环境下，前后两次读取的内容是一致的，而不受其他事务是否提交的影响。 
注： 
在Repeatable read(可重复读)的条件下，有可能会出现Phantom Read(幻读)现象。该现象可以通过模拟update来实现： 
第一步，客户端两边都开启start transaction。第二步，在id6上面新增一个字段，并在id5和id6上面分别查询。第三步，在id6上面提交，并在id5和id6上面分别查询。第四步，在id5上面对所有行的nm字段进行更新，并在id5和id6上面分别查询。 
id6上面的结果如下所示：

第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第二步：
MariaDB [hldb]> insert into test(nm) values('b');
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

第三步：
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

在id5上面的操作如下所示：

第一步：
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第二步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第三步：
MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

第四步：
MariaDB [hldb]> update test set nm='c';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
|  1 | c    |
|  2 | c    |
+----+------+
2 rows in set (0.00 sec)

可以看到，通过更新命令，在未结束的事务里面竟然也读到了“新的数据”，这便是一种Phantom Read(幻读)。

---

4. Serializable(可序列化) 
首先将两个客户端的事务隔离级别都设置为Serializable，并查询test数据表里面的内容：

MariaDB [hldb]> set @@session.tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 26 | a    |
| 27 | b    |
+----+------+
2 rows in set (0.00 sec)

......
......


MariaDB [hldb]> set @@session.tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 26 | a    |
| 27 | b    |
+----+------+
2 rows in set (0.00 sec)

第一步，在两个客户端上面开启start transaction。第二步，在id5上面添加一条记录，同时在id5, id6上查询

在id5上面查询的结果如下：

MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)

MariaDB [hldb]> select * from test;
+----+------+
| id | nm   |
+----+------+
| 26 | a    |
| 27 | b    |
| 28 | c    |
+----+------+
3 rows in set (0.00 sec)

在id6上面查询的结果如下：

MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hldb]> select * from test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

由上述结果可以看到，在id5未提交事务之前，即使是select查询操作，在其他事务里面也是不允许执行的，只有等待id5提交事务之后，其他事务才可以进行更新或者修改的操作。

当前题目：mariadb事务隔离级别相关实验
文章出自：http://6mz.cn/article/pojjdp.html