首页 / MYSQL / MySQL事务隔离级别-案例驱动
MySQL事务隔离级别-案例驱动
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL事务隔离级别-案例驱动,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4354字,纯文字阅读大概需要7分钟。
内容图文
![MySQL事务隔离级别-案例驱动](/upload/InfoBanner/zyjiaocheng/524/45d7eec0c30446c88c974a58ed2d06ff.jpg)
step1 创建表和基础数据
CREATE TABLE `traning`.`account` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT ‘主键id‘,
`uid` INT NOT NULL COMMENT ‘用户uid‘,
`amount` INT NOT NULL COMMENT ‘金额‘,
PRIMARY KEY (`id`));
INSERT INTO `traning`.`account` (`uid`, `amount`) VALUES (‘1‘, ‘1000‘);
INSERT INTO `traning`.`account` (`uid`, `amount`) VALUES (‘2‘, ‘2000‘);
step2 修改隔离级别
场景1 读未提交导致脏读
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
关闭会话重新进入MySQL客户端。
Client2:show variables like ‘transaction_isolation‘;
Client1: start TRANSACTION;
Client2: start TRANSACTION;
Client2: select amount from account where uid=1; (1000)
update account set amount=amount-500 where uid=1;
Client1: select amount from account where uid=1; (读到别人没提交事务:500)
-- update account set amount=(amount+100) where uid=1;(数据被锁,不能更新)
Client2: rollback;(Client1再次读到的是1000,但是没有读)
-- Client1: select amount from account where uid=1; (500)
Client1: update account set amount=(500+100) where uid=1; (读到旧数据去进行更新操作,导致账户丢失500)
场景2 读已提交解决脏读问题
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
关闭会话重启。
Client1:start TRANSACTION;
Client2:start TRANSACTION;
Client2: select amount from account where uid=1; (1000)
update account set amount=amount-500 where uid=1; (500)
Client1:select amount from account where uid=1 (读取不到别人没提交的事务:1000);
update account set amount=1000-500 where uid=1;(数据被锁不能更新)
Client2: rollback;
Client1: update account set amount=(1000+100) where uid=1;
Client1: commit;
场景3 读已提交导致不可重复读问题
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
关闭会话重启。
Client1:start TRANSACTION;
Client2:start TRANSACTION;
Client1:select amount from account where uid=1; (1000);
Client2:update account set amount=amount-100 where uid=1;
Client2: commit;
Client1: select amount from account where uid=1(amount=900,发现自己钱少了100);
场景4 可重复读解决不可重复读问题
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Client1:start TRANSACTION;
Client2:start TRANSACTION;
Client2: select amount from account where uid=1;(1000)
Client1:select amount from account where uid=1; (1000);
Client2:commit;
Client1:select amount from account where uid=1;(amount=1000);
场景5 可重复读导致幻读问题
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Client1:start TRANSACTION;
Client2:start TRANSACTION;
Client2: select amount from account ; (2行数据)
Client1:select amount from account ; (2行数据);
Client2:INSERT INTO traning
.account
(uid
, amount
) VALUES (‘3‘, ‘3000‘);
Client2:commit;
Client1:select amount from account ;(3条数据);
Clinet1 :update account set amount=1 where id=3(Client读不到但是可以更新);
Clinet1 :select amount from account ;(4条数据-phantom read here!!!!影响了client1的判断和数据);
Clinet1 :commit;
场景6 可序列化解决幻读问题
Client1:SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Client2:SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Client1:start TRANSACTION;
Client2:start TRANSACTION;
Client2: select amount from account ; (2行数据)
Client1:select amount from account ; (2行数据);
Client2:INSERT INTO traning
.account
(uid
, amount
) VALUES (‘3‘, ‘3000‘); (插入不进去,数据被锁)
Lock wait timeout exceeded; try restarting transaction
以下是整理的图:
当然还有第一类丢失更新(脏写)和第二类丢失更新,第一类丢失更新永远不会发生,第二类丢失更新会在RU和RC情况下发生。
这是完整的图
包含了更多的隔离级别和可能发生数据异常情况:
MySQL事务隔离级别-案例驱动
标签:核心 incr rem where action 通过 amount upd 基础
本文系统来源:https://blog.51cto.com/thinklili/2500781
内容总结
以上是互联网集市为您收集整理的MySQL事务隔离级别-案例驱动全部内容,希望文章能够帮你解决MySQL事务隔离级别-案例驱动所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。