SQL中 各种事务级别 和 脏读 不可重复读 幻读问题代码示例

-- ---------------------------- 事务操作 ---------------------------- -- 数据准备 create table account( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', money int comment '余额' ) comment '账户表'; insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000); -- 恢复数据 update account set money = 2000 where name = '张三' or name = '李四'; -- 设置事务提交方式 select @@autocommit; -- mysql默认自动提交 set @@autocommit = 0; -- 设置为手动提交 -- 转账操作 (张三给李四转账1000) -- 1. 查询张三账户余额 select * from account where name = '张三'; -- 2. 将张三账户余额-1000 update account set money = money - 1000 where name = '张三'; 程序执行报错 ... -- 3. 将李四账户余额+1000 update account set money = money + 1000 where name = '李四'; -- 提交事务 commit; -- 回滚事务 rollback ; -- 恢复数据 update account set money = 2000 where name = '张三' or name = '李四'; -- 设置事务提交方式 select @@autocommit; -- mysql默认自动提交 set @@autocommit = 1; -- 设置为手动提交 -- 方式二 -- 转账操作 (张三给李四转账1000) start transaction ; -- 1. 查询张三账户余额 select * from account where name = '张三'; -- 2. 将张三账户余额-1000 update account set money = money - 1000 where name = '张三'; 程序执行报错 ... -- 3. 将李四账户余额+1000 update account set money = money + 1000 where name = '李四'; -- 提交事务 commit; -- 回滚事务 rollback; #-------------------------- -- 查看事务隔离级别 select @@transaction_isolation; -- 设置事务隔离级别 set session transaction isolation level read uncommitted ; set session transaction isolation level repeatable read ; -- read uncommitted 具有 脏读 问题 set session transaction isolation level read uncommitted ; -- 事务一 start transaction ; -- 0 select * from account; -- 1 select * from account; -- 3 此时发现涨三余额发生改变，也就是发生脏读 -- 事务二 start transaction ;-- 0 update account set money = money - 1000 where name = '张三'; -- 2 -- read committed 解决脏读 问题，但仍具有不可重复读问题 set session transaction isolation level read committed ; -- 事务一 start transaction ; -- 0 select * from account; -- 1 select * from account; -- 3 此时发现涨三余额 没有 发生改变，也就是避免了脏读 select * from account; -- 5 此时才发生改变 commit ; start transaction ; -- 6 开始演示不可重复读问题 select * from account; -- 7 select * from account; -- 10 此时事务二commit后，张三余额发生改变，事务一两次读取的数据不一致，也就是不可重复读问题 -- 事务二 start transaction ;-- 0 update account set money = money - 1000 where name = '张三'; -- 2 commit ; -- 4 start transaction ; -- 6 开始演示不可重复读问题 update account set money = money + 1000 where name = '张三'; -- 8 commit ; -- 9 -- repeatable read 在 read committed 基础上，又解决了 不可重复读 问题， 但存在 幻读 问题 set session transaction isolation level repeatable read ; -- 事务一 start transaction ; -- 0 select * from account; -- 1 select * from account; -- 4 此时发现，虽然事务二提交了，但是事务一两次读取的数据一致，也就是说，解决了不可重复读问题 commit ; start transaction ; -- 5 select * from account where id = 3; -- 6 显示为空 insert into account(id, name, money) VALUES(3, '大刀王五', 2000); -- 9 此时报错，显示主键为3重复了 select * from account where id = 3; -- 10 既然说主键3重复了，那我们再次查询，还是显示为空 这就是 幻读 commit ; -- 事务二 start transaction ;-- 0 update account set money = money + 1000 where name = '张三'; -- 2 commit ; -- 3 start transaction ; -- 5 insert into account(id, name, money) VALUES(3, '王五', 2000); -- 7 commit ; -- 8 -- serializable 在 repeatable read 基础上，又解决了 幻读 问题 set session transaction isolation level serializable ; -- 事务一 start transaction ; -- 0 select * from account where id = 4; -- 1 显示为空 commit ; -- 3 事务一提交后，事务二阻塞的步骤2 开始执行 -- 事务二 start transaction ;-- 0 insert into account(id, name, money) VALUES(4, '王五2', 2000); -- 2 光标阻塞，等待事务一 提交 commit ;