导读:MySQL支持SQL:1992标准中的所有事务隔离级别,使用SET TRANSACTION来设置不同的事务隔离级别或访问模式,我们一起实战下它的效果。
MySQL中可以使用SET TRANSACTION来影响事务特性,此语句可以指定一个或多个由逗号分隔的特征值列表,每个特征值设置事务隔离级别或访问模式。此语句在MySQL 5.7中的完整语法
语法很简单清晰,这里有几个关键概念需要理解清楚。
事务隔离是数据库的基础能力,ACID中的I指的就是事务隔离,通俗点讲就是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
创建会话2,关闭MySQL默认的事务自动提交模式(相关知识可以参考MySQL中的事务控制语句,地址:https://www./db/23348)。
创建会话3,关闭MySQL默认的事务自动提交模式(相关知识可以参考MySQL中的事务控制语句,地址:https://www./db/23348)。
A给B转100元。在session1中模拟。
root@database-one 09:02:45 [gftest] session2> root@database-one 09:12:23 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:04:10 [gftest] session3> root@database-one 09:14:12 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
此时,再分别去session2、session3进行查询。 root@database-one 09:12:28 [gftest] session2> root@database-one 09:18:15 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:14:22 [gftest] session3> root@database-one 09:18:24 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:18:20 [gftest] session2> root@database-one 09:26:58 [gftest] session2>commit; Query OK, 0 rows affected (0.00 sec)
root@database-one 09:27:05 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:18:26 [gftest] session3> root@database-one 09:27:17 [gftest] session3>rollback; Query OK, 0 rows affected (0.00 sec)
root@database-one 09:27:24 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
我们将数据还原,并调整三个会话的事务隔离级别均为READ UNCOMMITTED。
root@database-one 09:38:42 [gftest] session1>update testtx set money=6000 where name='A'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
root@database-one 09:39:20 [gftest] session1>update testtx set money=8000 where name='B'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@database-one 09:39:44 [gftest] session1>commit; Query OK, 0 rows affected (0.00 sec)
root@database-one 09:39:49 [gftest] session1>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.00 sec)
root@database-one 09:40:33 [gftest] session1>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:41:31 [gftest] session2>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.00 sec)
root@database-one 09:41:44 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:42:16 [gftest] session3>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.01 sec)
root@database-one 09:42:24 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
A给B转100元。在session1中模拟。
root@database-one 09:42:28 [gftest] session3> root@database-one 09:47:15 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:42:28 [gftest] session3> root@database-one 09:47:15 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
此时,再分别去session2、session3视角进行查询。
root@database-one 09:48:02 [gftest] session2> root@database-one 09:52:18 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 09:48:18 [gftest] session3> root@database-one 09:53:11 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
session2、session3均看到金额变化。因为他们虽然还在自己的事务中(由自己session第一个select * from testtx即隐式开启了事务),根据READ COMMITTED事务隔离的原则应该看到。
我们将数据还原,并调整三个会话的事务隔离级别均为READ COMMITTED。
root@database-one 10:02:49 [gftest] session1>update testtx set money=6000 where name='A'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@database-one 10:03:10 [gftest] session1>update testtx set money=8000 where name='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@database-one 10:03:20 [gftest] session1>commit; Query OK, 0 rows affected (0.00 sec)
root@database-one 10:03:30 [gftest] session1>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec)
root@database-one 10:03:49 [gftest] session1>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 10:02:52 [gftest] session2>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec)
root@database-one 10:04:58 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 10:05:35 [gftest] session3>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec)
root@database-one 10:05:37 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
A给B转100元。在session1中模拟。
session1看到了金额进行了变化,但还未进行提交。 此时,分别去session2、session3进行查询。
root@database-one 10:05:07 [gftest] session2> root@database-one 10:08:34 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 10:06:02 [gftest] session3> root@database-one 10:08:42 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
此时,再分别去session2、session3视角进行查询。
root@database-one 10:09:24 [gftest] session2> root@database-one 11:09:45 [gftest] session2>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
root@database-one 11:08:29 [gftest] session3> root@database-one 11:11:54 [gftest] session3>select * from testtx; +------+---------+ | name | money | +------+---------+ | A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+ 3 rows in set (0.00 sec)
session2、session3均看到金额变化。 SERIALIZABLE,这个级别类似于REPEATABLE
READ,但更严格。在非自动提交模式下,InnoDB隐式地将所有SELECT语句转换为SELECT … LOCK IN SHARE
MODE。在自动提交模式下,SELECT在自己的事务里,以事务的原则运行。 因为效果和REPEATABLE READ类似,我这里就不再演示了,有兴趣的同学可以自己验证。SERIALIZABLE执行的规则比REPEATABLE READ更为严格,主要用于特殊情况,如XA事务、解决并发和死锁问题等场景。
墨天轮原文链接:https://www./db/23447(复制到浏览器中打开或者点击“阅读原文”) |
|