开发者空间里玩转MySQL之事务与锁-优快云博客

1 概述

1.1 案例介绍

事务与锁是数据库系统的核心机制，MySQL通过完善的事务支持和多粒度锁机制，确保数据一致性和并发控制，是构建高可靠、高性能应用的关键技术。

本案例在开发者环境桌面中，通过标准SQL语句演示MySQL事务控制与锁机制的实际应用，帮助开发者掌握并发场景下的数据安全操作

1.2 适用对象

个人开发者
高校学生

1.3 案例时间

本案例总时长预计40分钟。

1.4 案例流程

{{{width="44%" height="auto"}}}

说明：

领取空间开发桌面；
在空间开发桌面终端进入MySQL；
进行数据库的开发者空间进行MySQL之事务与锁的操作；

1.5 资源总览

本案例预计花费总计0元。

资源名称	规格	单价（元）	时长（分钟）
开发者空间 - 空间开发桌面	X86\| 2 vCPUs 4GB \| Ubuntu 22.04 Server 定制版	0	40

2 MySQL之事务

2.1 事务的基本概念

2.1.1 事务的概念

定义：事务是数据库操作的逻辑单元，包含一组 SQL 语句，要么全部执行成功（提交），要么全部失败回滚（Rollback）。

2.1.2 事务的特性（ACID）

特性	说明	MySQL 实现方式
原子性（Atomicity）	事务是不可分割的最小单元，要么全部成功，要么全部回滚。	通过Undo Log（回滚日志）实现
一致性（Consistency）	事务执行前后，数据库从一个一致状态变为另一个一致状态。	由应用层+数据库约束（如主键、外键、唯一索引）共同保证
隔离性（Isolation）	并发事务之间互不干扰。	通过锁机制和MVCC（多版本并发控制）实现
持久性（Durability）	事务提交后，修改永久保存。	通过Redo Log（重做日志）和 Double Write Buffer 实现

补充说明

原子性（Atomicity）：

依赖Undo Log，记录事务修改前的数据，用于回滚
事务失败时，MySQL自动回滚所有操作

一致性（Consistency）：

不仅依赖数据库，业务逻辑也要保证
通过约束（CHECK、UNIQUE、FOREIGN KEY）防止非法数据

隔离性（Isolation）由4种隔离级别控制：

READ UNCOMMITTED（可能脏读）
READ COMMITTED（避免脏读，但可能不可重复读）
REPEATABLE READ（MySQL 默认，避免脏读、不可重复读，但可能幻读）
SERIALIZABLE（完全串行化，避免所有并发问题，但性能最低）

持久性（Durability）：

Redo Log记录事务修改后的数据，崩溃恢复时重放日志
Double Write Buffer防止数据页写入不完整（Partial Write）

2.1.3 事务的适用场景

金融交易类场景：银行账户转账操作支付、系统处理流程、账户余额变更操作、资金结算对账过程
订单处理类场景、电商订单创建流程、库存扣减与恢复、订单状态变更、支付记录更新
用户管理类场景、用户注册完整流程、用户权限批量调整、用户信息级联更新、用户状态变更
数据批处理类场景、批量数据迁移、报表数据准备、定期数据归档、大规模数据修正
系统配置类场景、多表配置同步更新、系统参数调整、权限批量变更、业务规则更新

2.1.4 不适合使用事务的场景

简单查询操作
实时性要求极高的操作
超大批量数据处理
不需要原子性的独立操作

2.2 事务控制语句

MySQL提供的事务控制语句包括：

START TRANSACTION/BEGIN：开始事务
COMMIT：提交事务
ROLLBACK：回滚事务-- 完整事务流程语法示例

START TRANSACTION;
-- 执行SQL操作1
-- 执行SQL操作2
COMMIT; -- 或 ROLLBACK;

语句示例：

DELIMITER //
CREATE PROCEDURE update_student_age()
BEGIN
    -- 声明异常处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        SELECT '年龄更新失败，已回滚' AS result;
    END;

    -- 开始事务
    START TRANSACTION;

    -- 尝试更新年龄（可能触发 CHECK 约束）
    UPDATE student 
    SET age = -1
    WHERE student_id = '2230340102';

    -- 如果成功，更新入学日期
    UPDATE student 
    SET enrollment_date = '2024-09-10'
    WHERE student_id = '2230340102';

    -- 提交事务
    COMMIT;
    SELECT '年龄和入学日期更新成功' AS result;
END //
DELIMITER ;

-- 执行存储过程
CALL update_student_age();

操作结果：

语句示例：

DELIMITER //

CREATE PROCEDURE enroll_student(
    IN p_student_id VARCHAR(20),
    IN p_course_id VARCHAR(10),
    IN p_semester VARCHAR(20)
)
BEGIN
    DECLARE v_capacity INT;
    DECLARE v_enrollment INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '选课失败：系统错误' AS result;
    END;

    -- 开始事务
    START TRANSACTION;

    -- 1. 检查课程容量
    SELECT course_capacity, current_enrollment 
    INTO v_capacity, v_enrollment
    FROM course WHERE course_id = p_course_id FOR UPDATE;

    IF v_enrollment >= v_capacity THEN
        ROLLBACK;
        SELECT '选课失败：课程已满' AS result;
    ELSE
        -- 2. 创建选课记录
        INSERT INTO enrollment (student_id, course_id, semester)
        VALUES (p_student_id, p_course_id, p_semester);

        IF ROW_COUNT() = 0 THEN
            ROLLBACK;
            SELECT '选课失败：无法创建选课记录' AS result;
        ELSE
            -- 3. 更新课程人数
            UPDATE course 
            SET current_enrollment = current_enrollment + 1
            WHERE course_id = p_course_id;

            IF ROW_COUNT() = 0 THEN
                ROLLBACK;
                SELECT '选课失败：无法更新课程人数' AS result;
            ELSE
                COMMIT;
                SELECT '选课成功！' AS result;
            END IF;
        END IF;
    END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL enroll_student('2230340119', '3019', '2025 Spring');

操作结果：

2.3 事务的并发处理

前面讲过事物的隔离级别：

MySQL支持四种隔离级别：

READ UNCOMMITTED（读未提交）
READ COMMITTED（读已提交）
REPEATABLE READ（可重复读，MySQL默认）
SERIALIZABLE（串行化）

示例语法：

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别名称;
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别名称;
-- 在事务开始时指定隔离级别
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- 或
SET TRANSACTION ISOLATION LEVEL 隔离级别名称;
START TRANSACTION;

完整示例：

-- 设置会话隔离级别为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 开始事务
START TRANSACTION;
-- 执行事务操作...
COMMIT;

语句示例：

-- 会话1：演示脏读
-- 设置隔离级别，开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- 查看原始数据
SELECT * FROM student WHERE student_id = '2450030104';

-- 会话2（在另一个终端窗口执行）：
-- 设置隔离级别，开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- 执行更新操作（不提交）
UPDATE student SET age = 25 WHERE student_id = '2450030104';

-- 在会话1中查看未提交的数据（脏读）
SELECT * FROM student WHERE student_id = '2450030104';

-- 会话2回滚后，再次查询，在会话2执行 
ROLLBACK;

-- 会话1和会话2分别查看
SELECT * FROM student WHERE student_id = '2450030104';

-- 会话1提交事务
COMMIT;

操作结果：

示例2：READ COMMITTED 隔离级别演示（不可重复读现象）

-- 会话1：演示不可重复读
-- 设置隔离级别，开始事务
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- 第一次查询
SELECT * FROM course WHERE course_id = '3016';

-- 会话2（在另一个终端窗口执行）：
-- 设置隔离级别，开始事务
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- START TRANSACTION;
-- 执行更新操作并提交
-- UPDATE course SET current_enrollment = current_enrollment + 1 WHERE course_id = '3016';
-- COMMIT;

-- 在会话1中再次查询（发现数据已变化）
SELECT * FROM course WHERE course_id = '3016';
-- 会话1提交事务
COMMIT;

操作结果：

示例3：REPEATABLE READ 隔离级别演示（幻读现象）

-- 会话1：演示幻读
-- 设置隔离级别，开始事务
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 第一次查询
SELECT COUNT(*) FROM enrollment WHERE course_id = '4001';

-- 会话2（在另一个终端窗口执行）：
-- 设置隔离级别，开始事务
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- START TRANSACTION;
-- 插入并提交
-- INSERT INTO enrollment (student_id, course_id, semester) VALUES ('2450030104', '4001', '2025 Spring');
-- COMMIT;


-- 在会话1中再次查询（记录数不变，但实际数据已变化）
SELECT COUNT(*) FROM enrollment WHERE course_id = '4001';

-- 尝试插入相同数据（会发现冲突）
INSERT INTO enrollment (student_id, course_id, semester) 
VALUES ('2450030104', '4001', '2025 Spring');
COMMIT;

操作结果：

示例4：SERIALIZABLE 隔离级别演示

-- 会话1：演示串行化隔离级别
-- 设置隔离级别，开始事务
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

-- 查询课程信息（会加共享锁）
SELECT * FROM course WHERE course_id = '4001' FOR SHARE;

-- 会话2（在另一个终端窗口执行）：
-- 设置隔离级别，开始事务
-- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- START TRANSACTION;
-- 尝试更新会被阻塞，直到会话1提交
-- UPDATE course SET current_enrollment = current_enrollment + 1 WHERE course_id = '4001';

-- 在会话1中执行更新（成功）
UPDATE course SET credits = 5.0 WHERE course_id = '4001';

COMMIT;

-- 会话2的更新现在可以继续执行
-- UPDATE course SET current_enrollment = current_enrollment + 1 WHERE course_id = '4001';

操作结果：

阻塞阶段

阻塞结束

执行说明：

这些示例需要两个MySQL会话（终端窗口）来演示并发效果

执行前请确保：

已创建所有表结构
有测试数据

注意事项：

隔离级别名称必须使用上述四种标准名称之一
更改全局隔离级别需要SUPER权限
会话级别设置仅影响当前连接
事务中修改隔离级别需在START TRANSACTION之前
隔离级别越高，并发性能越低

2.4 事务实际使用中的注意事项

1. 控制事务持续时间

尽量缩短事务执行时间，避免长时间持有锁，影响并发性能
拆分大事务，将复杂操作分解为多个小事务，减少锁竞争
避免在事务中执行耗时操作（如网络请求、文件IO）

2. 合理设置隔离级别

默认使用 REPEATABLE READ（MySQL 默认），适用于大多数业务场景
高并发写入场景（如秒杀）可考虑 READ COMMITTED，减少锁冲突
严格一致性要求（如金融交易）可使用 SERIALIZABLE，但性能较低

3. 避免事务中包含用户交互

事务内不应等待用户输入（如确认弹窗），否则可能导致长事务
先收集用户数据，再在事务中处理，确保事务快速提交

4. 监控长事务执行情况

监控运行中的事务

设置告警（如事务执行超过 N 秒时触发）
优化慢查询，避免事务因SQL性能问题而长时间运行

3 MySQL之锁

3.1 锁机制

MySQL锁机制是数据库并发控制的核心，确保事务的隔离性和数据一致性。主要分为：

共享锁(S锁)：允许并发读，阻止其他事务获取排他锁
排他锁(X锁)：阻止其他事务获取任何类型的锁
意向锁：表级锁，表明事务准备在表中的行上获取什么类型的锁

示例语法：

-- 共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

-- 排他锁
SELECT * FROM table_name WHERE ... FOR UPDATE;

适用场景：

共享锁：读取数据且不希望被其他事务修改时
排他锁：修改数据时需要完全独占资源时

语句示例：

示例1：共享锁(S锁)应用 - 安全读取选课信息


-- 会话1：获取选课记录的共享锁（允许其他事务并发读取）
START TRANSACTION;
-- 查询学生STU001的选课情况（加共享锁）
SELECT e.enrollment_id, c.course_name, e.grade 
FROM enrollment e
JOIN course c ON e.course_id = c.course_id
WHERE e.student_id = '2250090118' 
LOCK IN SHARE MODE;

-- 保持事务不提交，观察会话2的读取情况
SELECT '会话1持有共享锁，请在其他会话尝试读取或修改' AS instruction;
-- 这里跳转会话2操作

-- 会话1提交事务
COMMIT;


-- 会话2：尝试读取被加锁的数据
-- START TRANSACTION;
-- 可以成功读取（共享锁允许并发读）
-- SELECT * FROM enrollment WHERE student_id = '2250090118';
-- 尝试修改会等待（直到会话1提交）
-- UPDATE enrollment SET grade = 85.5 WHERE student_id = '2250090118';

-- 会话2提交
-- COMMIT;

操作结果：

示例2：排他锁(X锁)应用 - 选课名额控制

-- 会话1：选课操作（使用排他锁确保名额控制）
START TRANSACTION;

-- 原子性更新选课人数（只有在有名额时才增加）
UPDATE course 
SET current_enrollment = IF(current_enrollment < course_capacity, current_enrollment + 1, current_enrollment)
WHERE course_id = '4001';

-- 检查是否更新成功
SET @success = (SELECT ROW_COUNT());

-- 根据更新结果决定是否插入选课记录
INSERT INTO enrollment (student_id, course_id, semester)
SELECT '2250110212', '4001', '2025 Spring'
WHERE @success > 0;

-- 显示结果
SELECT IF(@success > 0, '选课成功', '课程已满，选课失败') AS result;

-- 保持事务不立即提交，观察会话2的行为
SELECT '会话1持有排他锁，请在其他会话尝试操作相同课程' AS instruction;

-- 提交事务
COMMIT;


-- 会话2：尝试同时选同一门课程
-- START TRANSACTION;

-- 会等待会话1释放锁（如果会话1未提交）
-- SELECT course_capacity, current_enrollment 
-- FROM course 
-- WHERE course_id = '4001' 
-- FOR UPDATE;

-- 只有当会话1提交后，这里才会继续执行
-- ...后续操作...

-- COMMIT;

操作结果：

示例3：死锁场景演示 - 交叉更新资源

-- 会话1
START TRANSACTION;
UPDATE student SET age = 30 WHERE student_id = '2320030104';
SELECT '已锁定学生2320030104，请在其他会话更新课程2014' AS instruction;
-- 暂停在此处，不要继续执行


-- 会话2
START TRANSACTION;
UPDATE course SET description = '高级数据库课程上册' WHERE course_id = '2014';
SELECT '已锁定课程2014，请在其他会话更新学生2320030104' AS instruction;
-- 暂停在此处，不要继续执行

-- 会话1
UPDATE course SET current_enrollment = current_enrollment - 1 WHERE course_id = '2014';
-- 这里会卡住，等待窗口2

-- 会话2
UPDATE student SET class = '计算机3班' WHERE student_id = '2320030104';
-- 这里会检测到死锁，其中一个事务会被回滚

-- 会话1和会话2分别提交
COMMIT;

-- 死锁日志
SHOW ENGINE INNODB STATUS;

操作结果：

执行说明：

每个示例都包含两个会话的代码，需要分别在两个MySQL客户端中执行

执行顺序要严格按照注释提示的步骤

注意事项：

锁会降低系统并发性能
长时间持有锁可能导致死锁
合理使用索引可以减少锁定的数据范围

3.2 锁的级别

MySQL支持不同粒度的锁：

表级锁：锁定整个表
行级锁：锁定表中的特定行
页级锁：锁定数据页（InnoDB特有）
间隙锁：锁定索引记录之间的间隙

示例语法：

-- 表级锁
LOCK TABLES table_name READ/WRITE;
UNLOCK TABLES;

-- 行级锁
SELECT * FROM table_name WHERE ... FOR UPDATE;

-- 间隙锁（自动应用）
SELECT * FROM table_name WHERE id > 100 FOR UPDATE;

适用场景：

表级锁：批量操作、DDL语句、MyISAM引擎
行级锁：高并发OLTP系统、InnoDB引擎
间隙锁：防止幻读，范围查询时

表级锁示例

-- 会话1：锁定整个user表
LOCK TABLES user WRITE;  -- 获取user表的写锁
-- 执行需要独占整个表的操作
UPDATE user SET password = CONCAT('hashed_', password) 
WHERE username LIKE 'test%';
-- 保持锁定状态，观察会话2的行为
SELECT '已锁定user表，请在其他会话尝试操作user表' AS instruction;
-- 这里暂停，不要立即释放锁

-- 会话2：尝试操作被锁定的表（会等待）
-- 在另一个终端/窗口执行：
SELECT * FROM user WHERE user_id = '1001';
-- 这个查询会被阻塞，直到会话1释放表锁

-- 返回会话1释放锁
UNLOCK TABLES;
SELECT '已释放user表锁' AS message;

操作结果：

行级锁示例

-- 会话1：锁定student表的特定行
START TRANSACTION;
-- 获取student_id='2120010107'的行级排他锁
SELECT * FROM student WHERE student_id = '2120010107' FOR UPDATE;
-- 执行行级更新
UPDATE student SET class = '计算机2班' WHERE student_id = '2120010107';
-- 保持事务不提交，观察会话2的行为
SELECT '已锁定student_id=2120010107的行，请在其他会话尝试操作此行' AS instruction;
-- 这里暂停，不要立即提交

-- 会话2：尝试操作被锁定的行（会等待）
-- 在另一个终端/窗口执行：
START TRANSACTION;
UPDATE student SET age = 40 WHERE student_id = '2120010107';
-- 这个更新会被阻塞，直到会话1提交

-- 返回会话1提交事务
COMMIT;
SELECT '已提交事务，释放行锁' AS message;

操作结果：

页级锁示例

-- 注意：InnoDB实际上不直接暴露页级锁给用户，这是引擎内部机制
-- 但我们可以通过批量操作来观察页级锁的效果

-- 会话1：通过批量更新触发页级锁
START TRANSACTION;
-- 更新大量数据，InnoDB可能会锁定整个数据页
UPDATE enrollment SET grade = grade + 1 
WHERE enrollment_id BETWEEN 2500000001 AND 2500000400;
-- 保持事务不提交，观察会话2的行为
SELECT '已锁定包含enrollment_id 2500000001-2500000400的数据页，请在其他会话尝试操作此范围内的记录' AS instruction;
-- 这里暂停，不要立即提交

-- 会话2：尝试操作同一数据页上的记录（会等待）
-- 在另一个终端/窗口执行：
START TRANSACTION;
UPDATE enrollment SET grade = 90 WHERE enrollment_id = 2500000300;
-- 这个更新会被阻塞，因为涉及同一数据页

-- 返回会话1提交事务
COMMIT;
SELECT '已提交事务，释放页锁' AS message;

操作结果：

间隙锁防止幻读 - 批量处理成绩

-- 会话1
START TRANSACTION;
-- 锁定B到B+成绩区间（70-85分）的记录
-- 这会锁定现有记录和这个范围内的"间隙"
SELECT * FROM enrollment 
WHERE grade BETWEEN 70 AND 85
FOR UPDATE;
-- 保持事务不提交，观察会话2的行为
SELECT '已锁定70-85分的成绩区间，请尝试插入或修改此区间的成绩' AS instruction;

-- 会话2
START TRANSACTION;
-- 尝试插入75分的记录（会被阻塞，直到会话1提交）
INSERT INTO enrollment (student_id, course_id, semester, grade)
VALUES ('2330340108', '4030', '2025 Spring', 75);

-- 这条语句会被阻塞，不会立即执行

-- 会话1
-- 执行批量更新
UPDATE enrollment SET grade = grade + 2 
WHERE grade BETWEEN 70 AND 85;
COMMIT;

操作结果：

执行说明：

每个示例都包含两个会话的代码，需要分别在两个MySQL客户端中执行
执行顺序要严格按照注释提示的步骤

注意事项：

表级锁开销小但并发度低
行级锁开销大但并发度高
间隙锁可能导致更多的锁冲突

3.3 InnoDB存储引擎中的死锁

死锁是指两个或多个事务互相持有对方需要的锁，导致循环等待。InnoDB处理死锁的方式：

自动检测死锁
选择代价小的事务进行回滚
设置锁等待超时参数

示例语法：

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

-- 设置锁等待超时(秒)
SET innodb_lock_wait_timeout = 50;

适用场景：

多事务并发访问相同资源
复杂的事务处理流程
批量数据处理系统

注意事项：

按照固定顺序访问表和行可避免大部分死锁
保持事务短小精悍
为查询添加合适的索引
合理设置锁等待超时时间

3.4 最佳实践建议

锁优化原则：

尽量使用行级锁而非表级锁
只锁定必要的行
减少锁持有时间

避免死锁策略：

-- 按照固定顺序访问表
START TRANSACTION;
-- 总是先访问A表再访问B表
UPDATE table_a SET ... WHERE ...;
UPDATE table_b SET ... WHERE ...;
COMMIT;

监控锁使用情况：

-- 查看当前锁信息
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

隔离级别选择：

-- 根据业务需求选择合适的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL 8+在锁机制方面做了许多优化，如新增了SKIP LOCKED和NOWAIT语法，可以更灵活地处理锁等待情况。合理使用锁机制可以在保证数据一致性的同时，最大限度地提高系统并发性能。