第3课数据库_MySQL_事务_并发_锁

热度🔥:53    免费课程

数据库Mysql
授课语音

MySQL事务管理

1. 介绍

事务是指对数据库进行的一系列操作，要么全部成功执行，要么全部不执行。事务被视为不可分割的工作单位，具有ACID特性，其中包括原子性、一致性、隔离性和持久性。

MySQL事务实现

  • InnoDB存储引擎是MySQL的默认存储引擎，支持事务和崩溃恢复。
  • 事务的生命周期包括：
    • 开始事务：使用BEGIN或START TRANSACTION命令。
    • 执行事务：执行INSERT、UPDATE、SELECT等操作。
    • 提交事务：使用COMMIT命令，或者回滚事务：使用ROLLBACK命令。
  • ACID的底层机制：
    • 原子性：通过undo log（回滚日志）记录数据库操作的反向操作，以实现事务的原子性。
    • 一致性：通过约束、触发器等机制保证数据在事务执行前后的一致性。
    • 隔离性：通过锁机制和MVCC（多版本并发控制）实现不同隔离级别的事务隔离。
    • 持久性：事务提交后，数据的变化是持久的，即使发生故障也不会丢失，依靠redo log（重做日志）来实现。

并发事务的问题

在实际开发中，多个事务可能会并发运行（多个用户对同一数据进行操作），这可能导致以下问题：

  • 脏读：事务A修改数据，事务B读取了未提交的修改，如果事务A回滚，事务B读取到的数据就是“脏数据”。
  • 丢失修改：两个事务对同一数据进行修改，后一个事务的提交会覆盖前一个事务的修改。
  • 不可重复读：事务A读取数据后，事务B对数据进行了修改，再次读取时事务A看到的数据可能不同。
  • 幻读：事务A读取数据范围内的记录，事务B插入新记录，导致事务A再次读取时记录数量变化。

隔离级别

InnoDB存储引擎的默认隔离级别是可重复读（REPEATABLE READ）。

  • 读取未提交（READ UNCOMMITTED）：允许读取未提交的数据，可能导致脏读、不可重复读和幻读。
  • 读取已提交（READ COMMITTED）：只能读取已提交的数据，阻止脏读，但仍可能发生不可重复读和幻读。
  • 可重复读（REPEATABLE READ）：确保在一个事务内对同一数据的多次读取结果一致，阻止脏读和不可重复读，但可能发生幻读。
  • 串行化（SERIALIZABLE）：最高隔离级别，完全遵循ACID，但效率最低。

锁

  • 共享锁（S锁）：也称为读锁，多个事务可以同时获取，共享数据。
  • 排他锁（X锁）：独占锁，多个事务不能同时获取，保证数据的独占性。
  • 意向锁：表级锁，数据存储引擎维护，InnoDB在加锁前先获取意向锁，避免锁冲突。
    • 意向共享锁：事务加共享锁前，先获取意向共享锁。
    • 意向排他锁：事务加排他锁前，先获取意向排他锁。
  • 表锁：针对非索引字段加的锁，整张表加锁，性能较差。
  • 行锁：针对索引字段加的锁，对部分记录加锁。
    • 记录锁（Record Lock）：锁定当前行记录。
    • 间隙锁（Gap Lock）：锁定一个范围，不包括当前行记录。
    • 临键锁（Next-key Lock）：结合记录锁和间隙锁的实现，锁定范围包括当前行记录，可以解决幻读问题。

MVCC

多版本并发控制（MVCC）是一种通过维护数据多个版本实现并发控制的方法，能够在高并发环境下实现高效操作，避免传统锁机制的性能瓶颈。

  • 版本管理：每次事务修改数据时，数据库创建新版本，并与事务ID关联，旧版本数据仍然存在，与其他事务保持一致。
  • 快照隔离：事务开始时会获得数据的快照，使用快照进行后续操作。
  • 提交和回滚：事务提交时，新版本数据标记为可见，回滚时其他事务看不到最新版本的数据。
  • 删除过期版本：定期清理无用的旧版本数据，以防旧版本占用过多空间。

优缺点

  • 优点：

    • 提供并发读取性能，因为直接读取快照，不会被写操作阻塞。
    • 减少锁竞争，读取操作不需要等待写操作完成。
    • 提供一致视图，确保事务操作的一致性。

  • 缺点：

    • 存储开销，旧版本数据可能迅速积累。
    • 垃圾回收复杂，管理和清理过期版本数据是资源密集型任务。

MVCC与锁机制各有优缺点，MVCC适用于读操作频繁且高并发的事务场景，而锁机制更适合需要严格事务一致性和控制写操作的场景。实际应用中，两种机制相结合，InnoDB存储引擎使用MVCC优化读性能，同时结合行锁处理写操作和事务一致性。

2. 案例演示

2.1. 创建测试表

我们将创建三个表：test_table（用于测试脏读、丢失修改等问题）、range_table（用于测试幻读问题）、lock_table（用于测试行级锁机制）。

-- 创建测试表 test_table
CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    value INT NOT NULL COMMENT '测试值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间'
) COMMENT = '用于演示脏读、丢失修改和不可重复读';

-- 创建测试表 range_table
CREATE TABLE range_table (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    value INT NOT NULL COMMENT '测试值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) COMMENT = '用于演示幻读';

-- 创建测试表 lock_table
CREATE TABLE lock_table (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    value INT NOT NULL COMMENT '测试值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间'
) COMMENT = '用于演示行级锁机制';

2.2. 插入初始数据

-- 向 test_table 插入初始数据
INSERT INTO test_table (value) VALUES (100);

-- 向 range_table 插入初始数据
INSERT INTO range_table (value) VALUES (1), (2), (3);

-- 向 lock_table 插入初始数据
INSERT INTO lock_table (value) VALUES (10);

2.3. 演示脏读

脏读是指一个事务读取到另一个事务尚未提交的数据。在READ UNCOMMITTED隔离级别下，事务可以读取其他事务尚未提交的数据。

-- 终端 1：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE test_table SET value = 200 WHERE id = 1;  -- 事务A更新数据

-- 终端 2：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM test_table WHERE id = 1;  -- 事务B读取未提交的数据

-- 终端 1：提交事务
COMMIT;

-- 终端 2：查看结果（应看到 value = 200）
SELECT * FROM test_table WHERE id = 1;

-- 清理测试环境
ROLLBACK;

2.4. 演示丢失修改

丢失修改发生在两个事务同时修改同一数据时，最后提交的事务会覆盖前一个事务的修改。在READ COMMITTED隔离级别下，这种现象容易发生。

-- 终端 1：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE test_table SET value = 300 WHERE id = 1;  -- 事务A更新数据

-- 终端 2：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE test_table SET value = 400 WHERE id = 1;

  -- 事务B也更新数据

-- 终端 1：提交事务
COMMIT;

-- 终端 2：提交事务
COMMIT;

-- 查看结果（应看到 value = 400，事务B的更新覆盖了事务A的更新）
SELECT * FROM test_table WHERE id = 1;

-- 清理测试环境
TRUNCATE test_table;

2.5. 演示不可重复读

在REPEATABLE READ隔离级别下，事务在其生命周期内对同一数据的查询将返回相同的结果。以下示例演示了在一个事务中对同一数据进行两次读取，不会看到其他事务对数据的修改。

-- 终端 1：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM test_table WHERE id = 1;  -- 第一次读取数据

-- 终端 2：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE test_table SET value = 500 WHERE id = 1;  -- 修改数据
COMMIT;

-- 终端 1：再次读取数据
SELECT * FROM test_table WHERE id = 1;  -- 第二次读取（应看到和第一次相同的数据）

-- 终端 1：提交事务
COMMIT;

-- 查看结果（应看到两次读取的结果一致）
SELECT * FROM test_table WHERE id = 1;

-- 清理测试环境
TRUNCATE test_table;

2.6. 演示幻读

在REPEATABLE READ隔离级别下，一个事务在其执行期间读取的数据范围内，如果其他事务插入新数据，可能会出现幻读现象。以下示例展示了如何在读取范围内插入新数据导致幻读。

-- 终端 1：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM range_table WHERE value BETWEEN 1 AND 3;  -- 第一次读取

-- 终端 2：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO range_table (value) VALUES (4);  -- 插入新数据
COMMIT;

-- 终端 1：再次读取数据
SELECT * FROM range_table WHERE value BETWEEN 1 AND 4;  -- 第二次读取（可能会看到新插入的数据）

-- 终端 1：提交事务
COMMIT;

-- 查看结果（终端 1 可能会看到新插入的数据）
SELECT * FROM range_table WHERE value BETWEEN 1 AND 4;

-- 清理测试环境
TRUNCATE range_table;

2.7. 演示行级锁机制

行级锁可以帮助避免丢失修改和脏读。以下示例展示了如何使用行级锁来解决这些问题。

-- 终端 1：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 锁定数据行
SELECT * FROM lock_table WHERE id = 1 FOR UPDATE;  -- 事务A锁定数据行

-- 终端 2：尝试读取数据（被阻塞，直到终端 1 提交或回滚）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM lock_table WHERE id = 1;  -- 事务B等待锁释放

-- 终端 2：【尝试更新数据（被阻塞）】
UPDATE lock_table SET value = 20 WHERE id = 1;  -- 事务B等待锁释放

-- 终端 1：更新数据并提交事务
UPDATE lock_table SET value = 15 WHERE id = 1;  -- 事务A提交
COMMIT;

-- 终端 2：更新数据并提交事务
UPDATE lock_table SET value = 25 WHERE id = 1;  -- 事务B现在可以更新
COMMIT;

-- 查看结果（应显示 value = 25）
SELECT * FROM lock_table WHERE id = 1;

-- 清理测试环境
TRUNCATE lock_table;

2.8. 演示 MVCC 如何处理脏读、不可重复读和幻读

以下示例展示了MVCC如何处理不同的并发问题。

-- 创建测试表 mvcc_table
CREATE TABLE mvcc_table (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    value INT NOT NULL COMMENT '测试值',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间'
) COMMENT = '用于演示MVCC';

-- 向 mvcc_table 插入初始数据
INSERT INTO mvcc_table (value) VALUES (10), (20), (30);

-- 终端 1：设置隔离级别（可重复读）并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 读取数据：此时读取到的数据是事务开始时的数据快照
SELECT * FROM mvcc_table;

-- 更新数据：此操作在终端 1 的事务中进行
UPDATE mvcc_table SET value = 100 WHERE id = 1;

-- 不提交事务，保持事务开放
-- 终端 1 中继续进行其他操作，直到终端 2 完成其事务

-- 终端 2：设置隔离级别并开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 读取数据：终端 2 读取到的数据是事务开始时的数据快照，不受终端 1 未提交事务的影响
SELECT * FROM mvcc_table;

-- 更新数据：此操作在终端 2 的事务中进行
UPDATE mvcc_table SET value = 200 WHERE id = 2;

-- 提交事务：终端 2 提交其事务
COMMIT;

-- 终端 1：继续操作
-- 提交事务：终端 1 提交其事务
COMMIT;

-- 查看最终结果：终端 1 和终端 2 提交后的最终数据
SELECT * FROM mvcc_table;

-- 结果：
-- id = 1 的记录的 value 应该为 100，来自终端 1 的更新
-- id = 2 的记录的 value 应该为 200，来自终端 2 的更新

-- 清理测试环境
TRUNCATE mvcc_table;

以上是MySQL事务管理的全面讲解与案例演示，希望对您理解和掌握事务管理的机制和实际应用有所帮助。

去1:1私密咨询

系列课程：

Image
免费
Image
免费
Image
免费
Image
VIP会员
Image
VIP会员
Image
VIP会员
Image
VIP会员
Image
VIP会员
Image
VIP会员
Image
VIP会员
📱 下载客户端
微信小程序

小程序

安卓APP

安卓APP

iOS APP

iOS APP

📚 视频课程
🧱 体系课程
🎯 专项课程