mysql隔离级别模拟

参考: 

https://www.cnblogs.com/wyaokai/p/10921323.html --这个说的比较清楚

https://www.cnblogs.com/MrSi/p/9439336.html

http://blog.itpub.net/15412087/viewspace-2152465/

https://www.cnblogs.com/satng/p/7759899.html

# ddl
CREATE TABLE `tran_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

------------------------

# 未提交读1
set global transaction isolation level read uncommitted;
select @@global.tx_isolation;

# step 1
start TRANSACTION;

# step 2 
update tran_test set balance = 559 where id = 1;
select * from tran_test;

# step 4
commit;

# 未提交读2
select @@global.tx_isolation;

# step 1
start TRANSACTION;

# step 3,查到事物1未提交的数据
# step 5,事物1提交后正常查到
select * from tran_test;

commit;

------------------------

# 提交读1
set global transaction isolation level read committed;
select @@global.tx_isolation;

# step 1
start TRANSACTION;

# step 2
update tran_test set balance = 560 where id = 1;
select * from tran_test;

# step 4，新建窗口数据更新
commit;

# 提交读2
select @@global.tx_isolation;

# step 1
start TRANSACTION;

# step 3,查到旧数据
# step 4,查到事物1提交后的新数据
select * from tran_test;

commit;

------------------------

# 可重复读1
set global transaction isolation level repeatable read;

# step 1
start TRANSACTION;

# step 3
update tran_test set balance = 799 where id = 1;
select * from tran_test;

# step 5
commit;

# 可重复读2

# step 1
start TRANSACTION;

# step 2, 4, 6, 8
select * from tran_test;

#step 7
commit;

------------------------

#串行化1
set global transaction isolation level repeatable read;

-- step 1
start transaction;

-- step2
update tran_test set balance = 510 where id = 1;
select * from tran_test;

-- step3
commit;

#串行化2

-- step 1
start transaction;

-- step2 会一直等待，直到前面的事物提交
select * from tran_test;