SELECT @@session.tx_isolation; SELECT @@tx_isolation; —查询 数据库的 事务等级
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; —序列化
start transaction;—开启一个事务
–建表 drop table AMOUNT; CREATE TABLE AMOUNT ( id varchar(10) NULL, money numeric NULL ) ; –插入数据 insert into amount(id,money) values(‘A’, 800); insert into amount(id,money) values(‘B’, 200); insert into amount(id,money) values(‘C’, 1000); –测试可重复读，插入数据 insert into amount(id,money) values(‘D’, 1000);
–设置事务 SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; SELECT @@tx_isolation; –开启事务 start transaction;
–脏读演示，读到其他事务未提交的数据 –案列1，事务一：A向B转200，事务二：查看B金额变化，事务一回滚事务 update amount set money = money - 200 where id = ‘A’; update amount set money = money + 200 where id = ‘B’;
–不可重复读演示，读到了其他事务提交的数据 –案列2，事务一：B向A转200，事务二：B向C转200转100 SET SESSION TRANSACTION ISOLATION LEVEL read committed;
–开启事务 start transaction; –两个事务都查一下数据(转账之前需要，查一下金额是否够满足转账) select * from amount; –事务一：B向A转200 update amount set money = money - 200 where id = ‘B’; update amount set money = money + 200 where id = ‘A’;
commit; –事务二：B向C转200转100 update amount set money = money - 100 where id = ‘B’; update amount set money = money + 100 where id = ‘C’; commit; –从事务二的角度来看，读到了事务一提交事务的数据，导致金额出现负数
–幻读演示 –案列3，事务一：B向A转200，事务二：B向C转200转100 SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
–开启事务 start transaction; –两个事务都查一下数据(转账之前需要，查一下金额是否够满足转账) select * from amount; –事务一：B向A转200 update amount set money = money - 200 where id = ‘B’; update amount set money = money + 200 where id = ‘A’;
commit;—提交 –事务二：B向C转200转100 update amount set money = money - 100 where id = ‘B’; update amount set money = money + 100 where id = ‘C’; commit; –从事务二的角度来看，读到了事务一提交事务的数据，导致金额出现负数
