– session 1
create schema test;
use test;
create table t2 (id int, c2 char(1), c3 int);
insert t2 select 1,‘k’,10;
insert t2 select 2,‘y’,20;
insert t2 select 3,‘z’,100;
show variables like ‘%iso%’; – REPEATABLE-READ
begin;
update t2 set c2=‘K’ where c3=10;
– session 2
show variables like ‘%iso%’;
begin;
update t2 set c2=‘Y’ where c3=20;
– session 1
set global transaction_isolation = ‘READ-COMMITTED’;
set global tx_isolation = ‘READ-COMMITTED’;
\r
show variables like ‘%iso%’;
mysql> show variables like ‘%iso%’;
±----------------------±---------------+
| Variable_name | Value |
±----------------------±---------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
±----------------------±---------------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t2 set c2=‘K’ where c3=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t2;
±-----±-----±-----+
| id | c2 | c3 |
±-----±-----±-----+
| 1 | K | 10 |
| 2 | y | 20 |
| 3 | z | 100 |
±-----±-----±-----+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql>
– session 2
mysql> show variables like ‘%iso%’;
±----------------------±---------------+
| Variable_name | Value |
±----------------------±---------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation | READ-COMMITTED |
±----------------------±---------------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t2 set c2=‘Y’ where c3=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t2;
±-----±-----±-----+
| id | c2 | c3 |
±-----±-----±-----+
| 1 | k | 10 |
| 2 | Y | 20 |
| 3 | z | 100 |
±-----±-----±-----+
3 rows in set (0.00 sec)
mysql> select * from t2;
±-----±-----±-----+
| id | c2 | c3 |
±-----±-----±-----+
| 1 | K | 10 |
| 2 | Y | 20 |
| 3 | z | 100 |
±-----±-----±-----+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
±-----±-----±-----+
| id | c2 | c3 |
±-----±-----±-----+
| 1 | K | 10 |
| 2 | Y | 20 |
| 3 | z | 100 |
±-----±-----±-----+
3 rows in set (0.00 sec)
mysql>
https://javaguide.cn/database/mysql/mysql-questions-01.html#sql-%E6%A0%87%E5%87%86%E5%AE%9A%E4%B9%89%E4%BA%86%E5%93%AA%E4%BA%9B%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
著作权归JavaGuide(javaguide.cn)所有
基于MIT协议
原文链接：https://javaguide.cn/database/mysql/mysql-questions-01.html
