时间：2021-07-01 10:21:17 帮助过：6人阅读
在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提交事务之后，其他事务才可以进行更新或者修改的操作。
本文出自 “技术成就梦想” 博客，请务必保留此出处http://jiangche00.blog.51cto.com/4377920/1945249
mariadb事务隔离级别相关实验
标签：mariadb mysql 事务