MySQL-8.0 事务隔离级别

MySQL-8.0 事务隔离级别

查看数据库事务隔离级别

mysql> show variables like 'transaction%';
mysql> select @@transaction_isolation;
mysql> SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
mysql> SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;
1
2
3
4

修改事务隔离级别的方式

• 由低到高分别为

1. read uncommitted (读未提交)
2. read committed (读已提交)
3. repeatable read (可重复)
4. Serializable (可串行化)

1.只在本次会话有效

mysql> set session transaction isolation level read committed;
1

2.设置全局的事务隔离级别,该设置不会影响当前已经连接的会话,新会话,将使用新设置的事务隔离级别

mysql> set global transaction isolation level read committed;
1

3.修改配置文件,重启后生效

[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF
1
2
3

select id,name,count from ent_stock;
select id,name,count from ent_stock lock in share mode;
update ent_stock s set s.count = s.count-1 where s.name = '库存-1';
insert into ent_stock(id,name,count) values(uuid(),'库存-3',1000);
1
2
3
4

read uncommitted (读未提交)

初始数据：
开两个mysql窗口

设置事务级别

rollback：

read committed (读已提交)

设置事务级别

rollback：