MySQL Demo
Show Transaction Isolation Level
check session transaction level (mysql8+)
SELECT @@transaction_ISOLATION;
check global transaction level (mysql8+)
SELECT @@global.transaction_ISOLATION;
Set Transaction Isolation Level
# set global
SET GLOBAL transaction ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL transaction ISOLATION LEVEL READ COMMITTED;
SET GLOBAL transaction ISOLATION LEVEL REPEATABLE READ;
SET GLOBAL transaction ISOLATION LEVEL SERIALIZABLE;
# set session
SET session transaction ISOLATION LEVEL READ UNCOMMITTED;
SET session transaction ISOLATION LEVEL READ COMMITTED;
SET session transaction ISOLATION LEVEL REPEATABLE READ;
SET session transaction ISOLATION LEVEL SERIALIZABLE;
Dirty Read
# transaction 1
begin
# transaction 2
begin
# transaction 1
select * from post_like_tab where post_id = 1;
+----+---------+------------+
| id | post_id | like_count |
+----+---------+------------+
| 1 | 1 | 2 |
+----+---------+------------+
# transaction 2
update post_like_tab SET like_count = 4 where post_id = 1
# transaction 1
# transaction 1 这时候读到了 uncommitted change
+----+---------+------------+
| id | post_id | like_count |
+----+---------+------------+
| 1 | 1 | 4 |
+----+---------+------------+
# transaction 1
rollback
# 如果 isolation level 为 READ UNCOMMITTED，# transaction 1 就能看到update后的结果。只要设置为 READ_COMMITTED、REPEATABLE_READ 或 SERIALIZABLE，都可以避免这种情况
Non-repeatable Read
# transaction 1
begin
# transaction 2
begin
# transaction 1
select * from post_like_tab where post_id = 1;
+----+---------+------------+
| id | post_id | like_count |
+----+---------+------------+
| 1 | 1 | 3 |
+----+---------+------------+
# transaction 2
update post_like_tab SET like_count = 4 where post_id = 1
commit
# transaction 1
# transaction 1 这时候读到了与第一次读时不同的结果
+----+---------+------------+
| id | post_id | like_count |
+----+---------+------------+
| 1 | 1 | 4 |
+----+---------+------------+
# transaction 1
rollback
# 如果 isolation level 为 READ_COMMITTED，就会出现这种情况。只有设置为 REPEATABLE_READ 或 SERIALIZABLE，才可以避免这种情况
Phantom Read
# transaction 1
begin
# transaction 2
begin
# transaction 1
select * from post_like_tab;
+----+---------+------------+
| id | post_id | like_count |
+----+---------+------------+
| 1 | 1 | 3 |
+----+---------+------------+
# transaction 2
insert into post_like_tab (id, post_id, like_count) values (2, 2, 1)
commit
# transaction 1
# transaction 1 这时候读到了与第一次读时不同的结果，这称之为 Phantom Read，是 Non-repeatable Read 的一种特殊情况
+--------+---------+------------+
| id | post_id | like_count |
+--------+---------+------------+
| 1 | 1 | 3 |
|2 | 2 | 1 |
+--------+---------+------------+
# 如果 isolation level 为 REPEATABLE_READ，就会出现这种情况。只有设置为 SERIALIZABLE，才可以避免这种情况