第 8 章：事务与并发控制

在多用户数据库环境中，多个用户可能同时访问和修改相同的数据。为了确保数据的一致性和完整性，MySQL 提供了事务和并发控制机制。事务是数据库操作的逻辑单位，而并发控制确保多个事务可以安全地同时执行。

本章内容概要

• 事务的概念与特性（ACID）
• 事务的隔离级别
• START TRANSACTION、COMMIT、ROLLBACK
• 保存点（SAVEPOINT）
• 并发控制与锁机制
• 死锁问题与解决
• 实战练习：银行转账事务处理

事务的概念与特性（ACID）

事务（Transaction）是数据库操作的逻辑单位，由一系列的数据库操作组成。这些操作要么全部执行成功，要么全部不执行，是一个不可分割的工作单位。

ACID 特性

事务具有四个基本特性，通常称为 ACID 特性：

1. 原子性（Atomicity）

事务是一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成。

-- 示例：转账操作必须全部成功或全部失败
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT; -- 或 ROLLBACK;

2. 一致性（Consistency）

事务执行前后，数据库从一个一致状态转换到另一个一致状态。

-- 示例：转账前后总金额应该保持不变
-- 转账前：账户1余额5000，账户2余额3000，总计8000
-- 转账后：账户1余额4000，账户2余额4000，总计8000

3. 隔离性（Isolation）

多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

-- 事务1和事务2可以同时执行，但彼此不会干扰
-- 事务1读取数据时，事务2不能修改相同的数据

4. 持久性（Durability）

一旦事务提交，它对数据库的改变是永久性的，即使系统故障也不会丢失。

-- 事务提交后，即使数据库服务器重启，数据也不会丢失
COMMIT;

事务控制语句

启动事务

-- 启动事务的方式
START TRANSACTION;
-- 或者
BEGIN;

提交事务

-- 提交事务，使所有更改永久生效
COMMIT;

回滚事务

-- 回滚事务，撤销所有未提交的更改
ROLLBACK;

实际事务操作示例

让我们通过一个银行转账的例子来演示事务操作：

USE student_management;

-- 创建银行账户表
CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO accounts (account_name, balance) VALUES
('张三', 5000.00),
('李四', 3000.00),
('王五', 2000.00);

-- 演示事务操作
START TRANSACTION;

-- 张三向李四转账1000元
UPDATE accounts SET balance = balance - 1000 WHERE account_name = '张三';
UPDATE accounts SET balance = balance + 1000 WHERE account_name = '李四';

-- 检查转账结果
SELECT account_name, balance FROM accounts WHERE account_name IN ('张三', '李四');

-- 提交事务
COMMIT;

-- 或者如果发现错误可以回滚
-- ROLLBACK;

保存点（SAVEPOINT）

保存点允许我们在事务中设置标记点，可以回滚到特定的保存点而不是整个事务。

保存点操作

-- 启动事务
START TRANSACTION;

-- 执行一些操作
INSERT INTO accounts (account_name, balance) VALUES ('新用户1', 1000.00);

-- 设置保存点
SAVEPOINT sp1;

-- 执行更多操作
INSERT INTO accounts (account_name, balance) VALUES ('新用户2', 2000.00);
UPDATE accounts SET balance = balance + 500 WHERE account_name = '张三';

-- 设置另一个保存点
SAVEPOINT sp2;

-- 执行更多操作
DELETE FROM accounts WHERE account_name = '王五';

-- 回滚到保存点sp2（撤销删除操作）
ROLLBACK TO sp2;

-- 继续执行其他操作
UPDATE accounts SET balance = balance - 100 WHERE account_name = '李四';

-- 回滚到保存点sp1（撤销sp1之后的所有操作）
ROLLBACK TO sp1;

-- 提交事务（只会提交sp1之前的操作）
COMMIT;

释放保存点

-- 释放保存点（删除保存点）
RELEASE SAVEPOINT sp1;

事务隔离级别

事务隔离级别定义了一个事务可能受其他并发事务影响的程度。

四种隔离级别

1. READ UNCOMMITTED（读未提交）

最低的隔离级别，允许读取其他事务未提交的数据（脏读）。

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

2. READ COMMITTED（读已提交）

允许读取其他事务已提交的数据，但不可重复读。

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

3. REPEATABLE READ（可重复读）

MySQL 默认的隔离级别，确保同一事务中多次读取结果一致。

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查看默认隔离级别
SELECT @@transaction_isolation;

4. SERIALIZABLE（串行化）

最高的隔离级别，完全串行化执行事务。

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

隔离级别对比

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	可能	可能	可能	最高
READ COMMITTED	不可能	可能	可能	较高
REPEATABLE READ	不可能	不可能	可能	中等
SERIALIZABLE	不可能	不可能	不可能	最低

查看和设置隔离级别

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

-- 查看会话隔离级别
SELECT @@session.transaction_isolation;

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

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

并发控制与锁机制

锁的类型

1. 共享锁（S锁）

也称为读锁，允许多个事务同时读取同一资源。

-- 手动加共享锁
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;

2. 排他锁（X锁）

也称为写锁，阻止其他事务读取或写入同一资源。

-- 手动加排他锁
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

锁的粒度

1. 表级锁

锁定整个表，开销小但并发性低。

-- MyISAM 存储引擎使用表级锁
LOCK TABLES accounts READ;
-- 执行读操作
UNLOCK TABLES;

2. 行级锁

只锁定需要的行，开销大但并发性高。

-- InnoDB 存储引擎使用行级锁
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 只锁定 account_id = 1 的行
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

死锁问题与解决

死锁是指两个或多个事务相互等待对方释放锁资源的情况。

死锁示例

-- 事务1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- 等待几秒后执行
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 事务2（在另一个会话中）
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2;
-- 等待几秒后执行
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;

死锁检测与处理

MySQL 会自动检测死锁并回滚其中一个事务：

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

避免死锁的方法

1. 按相同顺序访问资源 - 所有事务按相同顺序访问表和行
2. 减少事务持有锁的时间 - 尽快提交事务
3. 避免大事务 - 将大事务拆分为多个小事务
4. 使用较低的隔离级别 - 在满足业务需求的前提下使用较低的隔离级别

实战练习：银行转账事务处理

让我们创建一个完整的银行转账系统来练习事务处理。

1. 创建表结构

-- 创建银行账户表
CREATE TABLE bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    account_holder VARCHAR(50) NOT NULL,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('active', 'frozen', 'closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建交易记录表
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_account VARCHAR(20),
    to_account VARCHAR(20),
    amount DECIMAL(15,2) NOT NULL,
    transaction_type ENUM('transfer', 'deposit', 'withdraw') NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建交易日志表
CREATE TABLE transaction_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT,
    log_message TEXT,
    log_level ENUM('info', 'warning', 'error') DEFAULT 'info',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 插入测试数据

-- 插入测试账户
INSERT INTO bank_accounts (account_number, account_holder, balance) VALUES
('ACC001', '张三', 10000.00),
('ACC002', '李四', 5000.00),
('ACC003', '王五', 8000.00),
('ACC004', '赵六', 12000.00);

3. 创建转账存储过程

DELIMITER //

CREATE PROCEDURE sp_transfer_money(
    IN from_account_num VARCHAR(20),
    IN to_account_num VARCHAR(20),
    IN transfer_amount DECIMAL(15,2),
    IN transfer_desc TEXT
)
BEGIN
    DECLARE from_balance DECIMAL(15,2) DEFAULT 0;
    DECLARE from_status VARCHAR(10);
    DECLARE to_status VARCHAR(10);
    DECLARE trans_id INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 记录交易开始
    INSERT INTO transactions (from_account, to_account, amount, transaction_type, description)
    VALUES (from_account_num, to_account_num, transfer_amount, 'transfer', transfer_desc);
    
    SET trans_id = LAST_INSERT_ID();
    
    INSERT INTO transaction_logs (transaction_id, log_message, log_level)
    VALUES (trans_id, CONCAT('开始转账: ', from_account_num, ' -> ', to_account_num, ', 金额: ', transfer_amount), 'info');
    
    -- 检查转出账户状态
    SELECT balance, status INTO from_balance, from_status
    FROM bank_accounts 
    WHERE account_number = from_account_num;
    
    IF from_status != 'active' THEN
        INSERT INTO transaction_logs (transaction_id, log_message, log_level)
        VALUES (trans_id, '转出账户状态异常', 'error');
        UPDATE transactions SET status = 'failed' WHERE id = trans_id;
        COMMIT;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转出账户状态异常';
    END IF;
    
    -- 检查余额是否充足
    IF from_balance < transfer_amount THEN
        INSERT INTO transaction_logs (transaction_id, log_message, log_level)
        VALUES (trans_id, '余额不足', 'error');
        UPDATE transactions SET status = 'failed' WHERE id = trans_id;
        COMMIT;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 检查转入账户状态
    SELECT status INTO to_status
    FROM bank_accounts 
    WHERE account_number = to_account_num;
    
    IF to_status != 'active' THEN
        INSERT INTO transaction_logs (transaction_id, log_message, log_level)
        VALUES (trans_id, '转入账户状态异常', 'error');
        UPDATE transactions SET status = 'failed' WHERE id = trans_id;
        COMMIT;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转入账户状态异常';
    END IF;
    
    -- 执行转账
    UPDATE bank_accounts 
    SET balance = balance - transfer_amount 
    WHERE account_number = from_account_num;
    
    INSERT INTO transaction_logs (transaction_id, log_message, log_level)
    VALUES (trans_id, CONCAT('转出账户扣款完成: ', from_account_num), 'info');
    
    UPDATE bank_accounts 
    SET balance = balance + transfer_amount 
    WHERE account_number = to_account_num;
    
    INSERT INTO transaction_logs (transaction_id, log_message, log_level)
    VALUES (trans_id, CONCAT('转入账户收款完成: ', to_account_num), 'info');
    
    -- 更新交易状态
    UPDATE transactions SET status = 'completed' WHERE id = trans_id;
    
    INSERT INTO transaction_logs (transaction_id, log_message, log_level)
    VALUES (trans_id, '转账完成', 'info');
    
    -- 提交事务
    COMMIT;
    
    SELECT '转账成功' AS result;
END //

DELIMITER ;

4. 测试转账功能

-- 正常转账测试
CALL sp_transfer_money('ACC001', 'ACC002', 1000.00, '朋友还款');

-- 查看转账结果
SELECT account_number, account_holder, balance 
FROM bank_accounts 
WHERE account_number IN ('ACC001', 'ACC002');

-- 查看交易记录
SELECT * FROM transactions WHERE status = 'completed';
SELECT * FROM transaction_logs WHERE transaction_id = 1;

5. 创建存款和取款存储过程

-- 存款存储过程
DELIMITER //

CREATE PROCEDURE sp_deposit_money(
    IN account_num VARCHAR(20),
    IN deposit_amount DECIMAL(15,2),
    IN deposit_desc TEXT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO transactions (from_account, to_account, amount, transaction_type, description, status)
    VALUES (NULL, account_num, deposit_amount, 'deposit', deposit_desc, 'completed');
    
    UPDATE bank_accounts 
    SET balance = balance + deposit_amount 
    WHERE account_number = account_num;
    
    COMMIT;
    
    SELECT '存款成功' AS result;
END //

DELIMITER ;

-- 取款存储过程
DELIMITER //

CREATE PROCEDURE sp_withdraw_money(
    IN account_num VARCHAR(20),
    IN withdraw_amount DECIMAL(15,2),
    IN withdraw_desc TEXT
)
BEGIN
    DECLARE current_balance DECIMAL(15,2) DEFAULT 0;
    DECLARE account_status VARCHAR(10);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    SELECT balance, status INTO current_balance, account_status
    FROM bank_accounts 
    WHERE account_number = account_num;
    
    IF account_status != 'active' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '账户状态异常';
    END IF;
    
    IF current_balance < withdraw_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    INSERT INTO transactions (from_account, to_account, amount, transaction_type, description, status)
    VALUES (account_num, NULL, withdraw_amount, 'withdraw', withdraw_desc, 'completed');
    
    UPDATE bank_accounts 
    SET balance = balance - withdraw_amount 
    WHERE account_number = account_num;
    
    COMMIT;
    
    SELECT '取款成功' AS result;
END //

DELIMITER ;

6. 测试存款和取款

-- 测试存款
CALL sp_deposit_money('ACC001', 2000.00, '工资收入');

-- 测试取款
CALL sp_withdraw_money('ACC002', 500.00, '购买商品');

-- 查看最终结果
SELECT account_number, account_holder, balance 
FROM bank_accounts 
ORDER BY account_number;

事务最佳实践

1. 保持事务简短

-- 好的做法：简短的事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 避免长时间运行的事务

2. 合理使用隔离级别

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

3. 正确处理异常

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

4. 避免嵌套事务

-- MySQL 不支持真正的嵌套事务
-- 使用保存点代替
SAVEPOINT sp1;
-- 操作
ROLLBACK TO sp1;

小结

在本章中，我们学习了事务与并发控制的重要概念：

1. 事务基础 - ACID 特性和事务控制语句
2. 事务隔离级别 - 四种隔离级别及其特点
3. 保存点 - 在事务中设置标记点
4. 并发控制 - 锁机制和并发问题
5. 死锁处理 - 死锁检测和避免方法
6. 实战练习 - 银行转账系统的实现

掌握事务处理技能对于构建可靠的数据库应用至关重要。在下一章中，我们将学习用户权限管理与安全，这是保护数据库安全的重要内容。