当前位置： 首页 > news >正文

当并发控制遇上餐厅！让你彻底搞懂MySQL脏读、不可重复读、幻读和丢失更新

news 2024/10/25 0:00:29

目录

    • 1. 环境准备：创建“账户”餐厅
      • 表结构解析
      • 初始数据
    • 2. 并发异常问题大揭秘
      • 2.1 脏读（Dirty Read）
      • 2.2 不可重复读（Non-Repeatable Read）
      • 2.3 幻读（Phantom Read）
      • 2.4 丢失更新（Lost Update）
    • 3. 事务隔离级别全景图
      • InnoDB 的默认隔离级别
    • 4. 总结与建议
      • 参考

并发控制在数据库世界中就像是一家繁忙的餐厅，厨师、服务员和顾客需要紧密协作，才能确保每个人都满意，餐厅运转顺畅。然而，有时会出现一些“意外情况”——这就是所谓的并发异常问题。通过您提供的SQL代码，让我们深入探讨这些并发异常问题，看看它们如何在数据库中“捣乱”，以及如何有效应对。

1. 环境准备：创建“账户”餐厅

首先，创建一个名为 account_t 的表，就像餐厅的菜单，记录了不同账户的信息：

DROP TABLE IF EXISTS `account_t`;
CREATE TABLE `account_t` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(255) DEFAULT NULL,`money` INT(11) DEFAULT 0,PRIMARY KEY (`id`),KEY `idx_name` (`name`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;ROLLBACK;
INSERT INTO `account_t` VALUES (7, 'M', 1000), (1, 'C', 1000), (2, 'B', 1000), (3, 'A', 1000);

表结构解析

  • 表名：account_t（如同餐厅的“账户”列表）
  • 字段：
    • id：每个账户的唯一编号，类似菜单上的菜品编号。
    • name：账户名，相当于菜品名称，配有索引 idx_name 方便查找。
    • money：账户余额，默认值为0，类似每道菜的价格。

初始数据

插入了四条记录，就像菜单上有四道菜：

idnamemoney
7M1000
1C1000
2B1000
3A1000

2. 并发异常问题大揭秘

接下来，讨论数据库中的“四大并发异常”：脏读、不可重复读、幻读和丢失更新。这些异常问题如同餐厅中的各种混乱场景，影响着数据的一致性。

2.1 脏读（Dirty Read）

比喻：服务员小张刚记下了顾客A的订单，但还未传给厨房。此时，另一个服务员小李看到这个未确认的订单，开始准备菜品。如果小张后来取消订单，小李已经浪费材料了。

定义：一个事务读取了另一个事务未提交的数据修改。如果另一个事务回滚，读取到的数据就是“脏数据”。

隔离级别：READ UNCOMMITTED 允许脏读。

代码解析：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN-- 事务1：增加A和B的余额UPDATE account_t SET money = money + 100 WHERE name = 'A';UPDATE account_t SET money = money + 100 WHERE name = 'B';COMMIT;-- 事务2：读取A和B的余额-- SELECT money FROM account_t WHERE name = 'A';-- SELECT money FROM account_t WHERE name = 'B';-- COMMIT

情景说明：

  1. 事务1：正在给A和B的账户加钱，但尚未提交。
  2. 事务2：在事务1提交前读取A和B的余额。由于隔离级别为 READ UNCOMMITTED，事务2可以读取到事务1未提交的“脏数据”。

潜在问题：

  • 如果事务1回滚，事务2读取的数据将是无效的，导致数据不一致。

解决办法：

  • 使用更高的隔离级别，如 READ COMMITTED，以防止脏读。

2.2 不可重复读（Non-Repeatable Read）

比喻：顾客B在餐厅里点了一道菜，第一次查看账单时显示价格为100元。过一会儿，厨师决定调整价格，账单显示价格变成了90元。顾客B看到两次账单价格不一致，感到困惑。

定义：一个事务在同一事务中多次读取同一数据时，结果可能不同，因为其他事务在此期间修改并提交了该数据。

隔离级别：READ COMMITTED 允许不可重复读。

代码解析：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN-- 事务2：第一次读取A的余额SELECT money FROM account_t WHERE name = 'A';-- 事务1：减少A的余额UPDATE account_t SET money = money - 100 WHERE name = 'A';COMMIT;-- 事务2：再次读取A的余额-- SELECT money FROM account_t WHERE name = 'A';-- COMMIT

情景说明：

  1. 事务2：第一次读取A的余额，假设为1000元。
  2. 事务1：减少A的余额100元，余额变为900元并提交。
  3. 事务2：在同一事务中再次读取A的余额，发现余额变为900元。

潜在问题：

  • 事务2在同一事务中两次读取同一数据时，结果不一致，可能导致逻辑错误。

解决办法：

  • 使用 REPEATABLE READ 隔离级别，确保事务内多次读取相同数据时结果一致。

2.3 幻读（Phantom Read）

比喻：顾客C在查看餐厅菜单时，第一次看到菜单上有3道菜。过了一会儿，厨师突然新增了一道菜。顾客C再次查看菜单时，发现多了一道新菜，感到惊讶。

定义：一个事务在读取一组满足条件的记录时，另一个事务插入或删除了满足相同条件的记录，导致前一个事务的结果集在不同时间点读取时不一致。

隔离级别：REPEATABLE READ 旨在防止幻读，但具体表现取决于数据库的实现。

代码解析：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN-- 事务2：第一次读取id >= 2的记录-- SELECT * FROM account_t WHERE id >= 2;-- 事务1：插入一条新记录INSERT INTO account_t(id, name, money) VALUES (4, 'D', 1000);COMMIT;-- 事务2：再次读取id >= 2的记录-- SELECT * FROM account_t WHERE id >= 2;-- COMMIT;DELETE FROM account_t WHERE id = 4;

情景说明：

  1. 事务2：第一次读取 id >= 2 的记录，假设有id=2,3,7。
  2. 事务1：插入一条新的记录id=4。
  3. 事务2：在同一事务中再次读取 id >= 2 的记录，理论上不应看到新插入的id=4，但实际表现可能因数据库实现不同而异。

潜在问题：

  • 如果 REPEATABLE READ 没有完全防止幻读，事务2可能看到不同的结果集，导致逻辑错误。

解决办法：

  • 使用更高的隔离级别 SERIALIZABLE，或确保数据库正确使用锁机制防止幻读。

2.4 丢失更新（Lost Update）

比喻：两位服务员同时记下顾客D的订单，但各自记错了价格。第一个服务员将价格更新为900元，第二个服务员将价格更新为1100元。最终，只有最后一个更新生效，导致最初的价格调整丢失。

定义：两个事务同时读取同一数据并基于该数据进行更新，导致其中一个事务的更新被另一个事务覆盖，造成数据丢失。

隔离级别：REPEATABLE READ 本身不直接防止丢失更新，需要额外的锁机制或使用乐观锁。

代码解析：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN-- 事务1：读取A的余额SELECT money FROM account_t WHERE name = 'A';-- 事务2：读取A的余额-- SELECT money FROM account_t WHERE name = 'A';-- 事务2：更新A的余额为1100-- UPDATE account_t SET money = 1100 WHERE name = 'A';-- COMMIT;-- 事务1：更新A的余额为900UPDATE account_t SET money = 900 WHERE name = 'A';COMMIT;

情景说明：

  1. 事务1和事务2：同时读取A的余额，都是1000元。
  2. 事务2：将A的余额更新为1100元并提交。
  3. 事务1：将A的余额更新为900元并提交，覆盖了事务2的更新。

潜在问题：

  • 最终A的余额为900元，事务2的更新被丢失，导致数据不一致。

解决办法：

  • 使用显式锁，如 SELECT ... FOR UPDATE，在读取数据时锁定行，防止其他事务同时更新。
  • 实现乐观锁机制，如在表中添加版本号字段，通过版本号检查更新冲突。
  • 提高隔离级别至 SERIALIZABLE，强制事务顺序执行，避免更新覆盖。

3. 事务隔离级别全景图

为了更好地理解这些并发异常，以下是SQL标准中定义的四种事务隔离级别，它们如同餐厅管理的不同规则，影响着事务间的互动：

  1. READ UNCOMMITTED：

    • 特点：允许脏读、不可重复读和幻读。
    • 场景：适合对数据一致性要求不高，但需要高并发性能的应用。
    • 比喻：餐厅不严格管理订单，服务员可以随意查看和修改未确认的订单。

  2. READ COMMITTED：

    • 特点：防止脏读，但仍允许不可重复读和幻读。
    • 场景：常见的默认隔离级别，如Oracle数据库。
    • 比喻：服务员只能查看已经确认的订单，但仍可能在处理过程中看到价格变化。

  3. REPEATABLE READ：

    • 特点：防止脏读和不可重复读，但在某些实现下仍可能允许幻读。
    • 场景：MySQL的InnoDB默认隔离级别，通过间隙锁进一步防止幻读。
    • 比喻：一旦服务员确认了订单，其他服务员不能随意修改，但新增的订单（幻影订单）可能仍然会出现。

  4. SERIALIZABLE：

    • 特点：最高的隔离级别，完全防止脏读、不可重复读和幻读。
    • 场景：对数据一致性要求极高的应用，如金融系统。
    • 比喻：餐厅实行严格的订单处理，每次只能处理一个订单，确保绝对一致性，但可能牺牲了一部分效率。

InnoDB 的默认隔离级别

在MySQL中，InnoDB存储引擎的默认隔离级别是 REPEATABLE READ。通过使用行级锁和间隙锁（next-key locks），InnoDB有效地防止了大多数并发异常。然而，理解和正确配置隔离级别对于确保数据一致性和系统性能至关重要。

4. 总结与建议

并发异常问题如同餐厅中的各种突发情况，了解并正确应对这些问题，才能确保数据的一致性和系统的稳定性。以下是一些关键建议，帮助在数据库设计和应用开发中游刃有余：

  • 选择合适的隔离级别：根据应用需求，平衡数据一致性和系统性能。对于需要高度一致性的场景，选择更高的隔离级别。

  • 使用显式锁机制：在可能发生丢失更新的场景中，使用 SELECT ... FOR UPDATE 或其他锁机制，确保事务的排他性访问。

  • 实现乐观锁：在高并发场景下，使用乐观锁（如版本号控制）可以减少锁争用，提高系统性能，同时防止丢失更新。

  • 测试并发场景：在开发阶段，通过模拟并发事务，测试和验证系统在不同隔离级别下的行为，确保业务逻辑的正确性。

通过这些措施，能够有效地管理和控制并发事务，就像一位经验丰富的餐厅经理，确保餐厅高效运转，顾客满意而归。

参考

0voice · GitHub


http://www.mrgr.cn/news/57957.html

相关文章：

  • C++中new和delete关键字的概念、使用方法和注意事项
  • Elasticsearch入门：增删改查详解与实用场景
  • 基于Python实现“气象家园”自动签到
  • 【CSS in Depth 2 精译_054】8.2 CSS 层叠图层（cascade layer）的推荐组织方案
  • 存档库 | 《你经历了什么》
  • AFS系统
  • ppt怎么一键抠图？3个实用技巧，轻松做出高颜值PPT！
  • 时序知识图谱学习——思维框图总结
  • 力扣 —— 分发糖果
  • geoserver解析元数据获取图层相关参数
  • 零基础Java第九期：一维数组（二）和二维数组
  • Java题集练习3
  • 一文带你入门Flink CDC
  • 十、Linux 故障排除专业案例分享
  • java抽象类和接口
  • 【Fargo】14: sockaddr_in 、 sockaddr 、sockaddr_storage 区别及转换
  • 论文思路 标书 水文
  • css知识点梳理
  • Zypher Network Layer3 主网上线，“宝藏方舟”活动是亮点
  • 深度学习_循环神经网络_预测平安中国股价(文末附带数据集下载链接, 长期有效, 如果有大佬愿意帮忙, 我先在这磕一个,感谢)
  • Vue弹窗用也可以直接调用Js方法了
  • 博饼代码【Python】
  • V4L2驱动框架
  • Unreal Engine5安装Niagara UI Renderer插件
  • phpstorm中使用FTP功能和自动上传配置介绍
  • 部署前后端分离若依项目--CentOS7Docker版