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事务隔离级别相关实验


  • 相关实验 
    下面分别针对不同的隔离状态进行测试:
    准备的环境如下所示:
    在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事务隔离级别相关实验
分享URL:http://scyanting.com/article/pojjdp.html