MySQL5.7.18版本演示如下：
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.18 | +-----------+ 1 row in set (0.00 sec) mysql> set global transaction isolation level REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) mysql> set session transaction isolation level READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation; +-----------------------+------------------------+----------------+ | @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation | +-----------------------+------------------------+----------------+ | REPEATABLE-READ | READ-COMMITTED | READ-COMMITTED | +-----------------------+------------------------+----------------+ 1 row in set (0.00 sec)MySQL8.0.21版本演示如下：
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.21 | +-----------+ 1 row in set (0.01 sec) mysql> set global transaction isolation level REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) mysql> set session transaction isolation level READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.transaction_isolation, @@session.transaction_isolation, @@transaction_isolation; +--------------------------------+---------------------------------+-------------------------+ | @@global.transaction_isolation | @@session.transaction_isolation | @@transaction_isolation | +--------------------------------+---------------------------------+-------------------------+ | REPEATABLE-READ | READ-COMMITTED | READ-COMMITTED | +--------------------------------+---------------------------------+-------------------------+ 1 row in set (0.00 sec)注意：
transaction_isolation was added in MySQL 5.7.20 as a synonym for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.
Prior to MySQL 5.7.20, use tx_isolation and tx_read_only rather than transaction_isolation and transaction_read_only.
如果使用系统变量(system variables)来查看或者设置事务隔离级别，需要注意MySQL的版本。在MySQL5.7.20之前，应使用tx_isolation；在MySQL5.7.20之后，应使用transaction_isolation。
You can set transaction characteristics globally, for the current session, or for the next transaction only.
事务的隔离级别范围(Transaction Characteristic Scope)可以精确到全局(global)、当前会话(session)、甚至是仅针对下一个事务生效(the next transaction only)。
含global关键词时，事务隔离级别的设置应用于所有后续session，已存在的session不受影响
含session关键词时，事务隔离级别的设置应用于在当前session中执行的所有后续事务，不会影响当前正在进行的事务
不含global以及session关键词时，事务隔离级别的设置仅应用于在当前session中执行的下一个事务
数据准备为了演示脏读、不可重复读、幻读等问题，准备了一些初始化数据如下：
-- ---------------------------- -- create database -- ---------------------------- create database `transaction_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- switch database use `transaction_test`; -- ---------------------------- -- table structure for `tb_book` -- ---------------------------- CREATE TABLE `tb_book` ( `book_id` int(11) NOT NULL, `book_name` varchar(64) DEFAULT NULL, `author` varchar(32) DEFAULT NULL, PRIMARY KEY (`book_id`), UNIQUE KEY `uk_book_name` (`book_name`) USING BTREE ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; BEGIN; INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (1, '多情剑客无情剑', '古龙'); INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (2, '笑傲江湖', '金庸'); INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (3, '倚天屠龙记', '金庸'); INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (4, '射雕英雄传', '金庸'); INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (5, '绝代双骄', '古龙'); COMMIT; 脏读(read uncommitted)事务A读到了事务B已经修改但尚未提交的数据
操作：
session A事务隔离级别设置为read uncommitted并开启事务，首次查询book_id为1的记录；
然后session B开启事务，并修改book_id为1的记录，不提交事务，在session A中再次查询book_id为1的记录；
最后让session B中的事务回滚，再在session A中查询book_id为1的记录。
session A：
mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tb_book where book_id = 1; +---------+-----------------------+--------+ | book_id | book_name | author | +---------+-----------------------+--------+ | 1 | 多情剑客无情剑 | 古龙 | +---------+-----------------------+--------+ 1 row in set (0.00 sec) mysql> select * from tb_book where book_id = 1; +---------+-----------------------+--------+ | book_id | book_name | author | +---------+-----------------------+--------+ | 1 | 多情刀客无情刀 | 古龙 | +---------+-----------------------+--------+ 1 row in set (0.00 sec) mysql> select * from tb_book where book_id = 1; +---------+-----------------------+--------+ | book_id | book_name | author | +---------+-----------------------+--------+ | 1 | 多情剑客无情剑 | 古龙 | +---------+-----------------------+--------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)