跳至主要內容

核心-事务处理机制

清峰大约 6 分钟

核心-事务处理机制

业务场景引入

在银行转账系统中，张三向李四转账1000元的操作包含：

  1. 从张三账户扣除1000元
  2. 向李四账户增加1000元

这两个操作必须要么全部成功，要么全部失败。如果扣款成功但加款失败，就会造成资金丢失。这正是事务处理要解决的核心问题。

事务ACID特性

原子性（Atomicity）

-- 转账事务示例
START TRANSACTION;

-- 1. 检查张三账户余额
SELECT balance FROM accounts WHERE user_id = 1001 FOR UPDATE;

-- 2. 扣除张三账户金额
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1001;

-- 3. 增加李四账户金额
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 1002;

-- 4. 记录转账日志
INSERT INTO transfer_logs (from_user, to_user, amount, transfer_time)
VALUES (1001, 1002, 1000, NOW());

-- 全部成功则提交
COMMIT;
-- 任何步骤失败则回滚
-- ROLLBACK;

一致性（Consistency）

-- 库存扣减的一致性保证
CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    product_name VARCHAR(200),
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),  -- 约束：库存不能为负
    reserved_quantity INT DEFAULT 0,
    available_quantity INT GENERATED ALWAYS AS (stock_quantity - reserved_quantity)
);

-- 下单事务确保库存一致性
DELIMITER $$
CREATE PROCEDURE PlaceOrder(
    IN p_product_id BIGINT,
    IN p_quantity INT,
    IN p_user_id BIGINT
)
BEGIN
    DECLARE v_available INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    
    -- 锁定产品记录并检查库存
    SELECT available_quantity INTO v_available
    FROM products 
    WHERE product_id = p_product_id FOR UPDATE;
    
    IF v_available < p_quantity THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
    
    -- 扣减库存
    UPDATE products 
    SET reserved_quantity = reserved_quantity + p_quantity
    WHERE product_id = p_product_id;
    
    -- 创建订单
    INSERT INTO orders (user_id, product_id, quantity, order_status)
    VALUES (p_user_id, p_product_id, p_quantity, 'CONFIRMED');
    
    COMMIT;
END$$
DELIMITER ;

隔离性（Isolation）

MySQL支持四种隔离级别：

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

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

-- 不同隔离级别的演示

-- 1. READ UNCOMMITTED（读未提交）- 会出现脏读
-- 会话A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = 5000 WHERE user_id = 1001;
-- 不提交

-- 会话B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1001;  -- 读到5000（脏读）

-- 2. READ COMMITTED（读已提交）- MySQL默认级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. REPEATABLE READ（可重复读）- InnoDB默认级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1001;  -- 第一次读取
-- 其他会话修改并提交数据
SELECT balance FROM accounts WHERE user_id = 1001;  -- 第二次读取，结果相同

-- 4. SERIALIZABLE（串行化）- 最高隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

持久性（Durability）

-- 查看InnoDB持久性配置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 0: 每秒刷新，性能最好但可能丢失1秒数据
-- 1: 每次事务提交刷新，最安全
-- 2: 每次提交写入OS缓存，每秒刷新到磁盘

-- 查看二进制日志配置
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'sync_binlog';

事务控制语句

显式事务控制

-- 标准事务语法
START TRANSACTION;
-- 或者使用 BEGIN;

-- 事务操作
INSERT INTO orders (user_id, total_amount) VALUES (1001, 299.99);
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2001;

-- 提交事务
COMMIT;

-- 或回滚事务
-- ROLLBACK;

-- 带保存点的事务
START TRANSACTION;

INSERT INTO orders (user_id, total_amount) VALUES (1001, 299.99);
SAVEPOINT order_created;

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2001;
SAVEPOINT stock_updated;

-- 部分回滚到保存点
ROLLBACK TO SAVEPOINT order_created;

-- 释放保存点
RELEASE SAVEPOINT order_created;

COMMIT;

自动提交模式

-- 查看自动提交状态
SELECT @@autocommit;

-- 关闭自动提交
SET autocommit = 0;

-- 这时每个SQL都需要手动提交
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1001;
COMMIT;

-- 重新开启自动提交
SET autocommit = 1;

锁机制详解

行级锁

-- 共享锁（S锁）
SELECT balance FROM accounts WHERE user_id = 1001 LOCK IN SHARE MODE;

-- 排他锁（X锁）
SELECT balance FROM accounts WHERE user_id = 1001 FOR UPDATE;

-- 在事务中使用锁
START TRANSACTION;
SELECT balance FROM accounts WHERE user_id = 1001 FOR UPDATE;
-- 此时其他会话无法修改该行，必须等待
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1001;
COMMIT;

表级锁

-- 表级读锁
LOCK TABLES products READ;
SELECT * FROM products;  -- 可以读
-- UPDATE products SET price = 100;  -- 报错，无法写
UNLOCK TABLES;

-- 表级写锁
LOCK TABLES products WRITE;
SELECT * FROM products;   -- 可以读
UPDATE products SET price = price * 1.1;  -- 可以写
UNLOCK TABLES;

死锁检测与处理

-- 模拟死锁场景

-- 会话A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1001;
-- 等待一段时间
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1002;

-- 会话B（同时执行）
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE user_id = 1002;
-- 等待一段时间
UPDATE accounts SET balance = balance + 50 WHERE user_id = 1001;  -- 死锁发生

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

-- 死锁预防：统一锁顺序
START TRANSACTION;
-- 总是按用户ID顺序加锁
SELECT * FROM accounts WHERE user_id IN (1001, 1002) ORDER BY user_id FOR UPDATE;
-- 执行业务逻辑
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1001;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1002;
COMMIT;

并发控制实战

秒杀系统并发控制

-- 秒杀商品表
CREATE TABLE seckill_products (
    seckill_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    seckill_price DECIMAL(10,2) NOT NULL,
    total_quantity INT NOT NULL,
    sold_quantity INT DEFAULT 0,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    status ENUM('UPCOMING', 'ACTIVE', 'ENDED') DEFAULT 'UPCOMING',
    
    INDEX idx_product_time (product_id, start_time, end_time)
) ENGINE=InnoDB;

-- 秒杀订单处理
DELIMITER $$
CREATE PROCEDURE ProcessSeckill(
    IN p_seckill_id BIGINT,
    IN p_user_id BIGINT,
    IN p_quantity INT,
    OUT p_result_code INT,
    OUT p_order_id BIGINT
)
BEGIN
    DECLARE v_available INT DEFAULT 0;
    DECLARE v_now TIMESTAMP DEFAULT NOW();
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_code = -1;
    END;

    START TRANSACTION;
    
    -- 检查秒杀状态和库存（悲观锁）
    SELECT (total_quantity - sold_quantity), start_time, end_time, status
    INTO v_available, @start_time, @end_time, @status
    FROM seckill_products 
    WHERE seckill_id = p_seckill_id FOR UPDATE;
    
    -- 检查时间和状态
    IF @status != 'ACTIVE' OR v_now < @start_time OR v_now > @end_time THEN
        SET p_result_code = 1; -- 活动未开始或已结束
        ROLLBACK;
    ELSEIF v_available < p_quantity THEN
        SET p_result_code = 2; -- 库存不足
        ROLLBACK;
    ELSE
        -- 更新销售数量
        UPDATE seckill_products 
        SET sold_quantity = sold_quantity + p_quantity
        WHERE seckill_id = p_seckill_id;
        
        -- 创建订单
        INSERT INTO seckill_orders (seckill_id, user_id, quantity, order_time)
        VALUES (p_seckill_id, p_user_id, p_quantity, v_now);
        
        SET p_order_id = LAST_INSERT_ID();
        SET p_result_code = 0; -- 成功
        
        COMMIT;
    END IF;
END$$
DELIMITER ;

乐观锁实现

-- 带版本号的乐观锁
CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    quantity INT NOT NULL,
    version INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 乐观锁更新库存
UPDATE inventory 
SET quantity = quantity - 1, 
    version = version + 1 
WHERE product_id = 1001 
  AND version = 5  -- 使用version作为乐观锁条件
  AND quantity >= 1;

-- 检查影响行数判断是否成功
SELECT ROW_COUNT(); -- 返回1表示成功，0表示失败（版本冲突或库存不足）

事务性能优化

事务大小控制

-- ❌ 避免长事务
START TRANSACTION;
-- 处理大量数据，事务时间过长
UPDATE products SET status = 'ACTIVE' WHERE category_id = 1; -- 影响百万行
COMMIT;

-- ✅ 分批处理
DELIMITER $$
CREATE PROCEDURE BatchUpdateProducts(IN p_category_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE affected_rows INT DEFAULT 0;
    
    REPEAT
        START TRANSACTION;
        
        UPDATE products 
        SET status = 'ACTIVE' 
        WHERE category_id = p_category_id 
          AND status != 'ACTIVE'
        LIMIT batch_size;
        
        SET affected_rows = ROW_COUNT();
        COMMIT;
        
        -- 短暂休息，释放资源
        SELECT SLEEP(0.1);
        
    UNTIL affected_rows < batch_size END REPEAT;
END$$
DELIMITER ;

减少锁竞争

-- ❌ 锁竞争激烈的计数器
UPDATE counters SET count = count + 1 WHERE counter_name = 'page_view';

-- ✅ 分片计数器减少竞争
CREATE TABLE counter_shards (
    counter_name VARCHAR(50),
    shard_id TINYINT,
    count BIGINT DEFAULT 0,
    PRIMARY KEY (counter_name, shard_id)
);

-- 随机选择分片更新
SET @shard = FLOOR(RAND() * 10);
UPDATE counter_shards 
SET count = count + 1 
WHERE counter_name = 'page_view' AND shard_id = @shard;

-- 查询总计数
SELECT SUM(count) as total_count 
FROM counter_shards 
WHERE counter_name = 'page_view';

事务监控

-- 查看当前事务状态
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_requested_lock_id,
    trx_wait_started,
    trx_weight,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx;

-- 查看锁等待情况
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看事务执行时间
SELECT 
    trx_id,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_seconds,
    trx_tables_locked,
    trx_rows_locked
FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING'
ORDER BY duration_seconds DESC;

总结与最佳实践

事务设计原则

  1. 最小化事务范围：只在必要时使用事务，减少锁定时间
  2. 避免长事务：分批处理大量数据，避免长时间锁定
  3. 统一锁顺序：防止死锁，按固定顺序获取锁
  4. 选择合适隔离级别：平衡数据一致性和性能

常见问题避免

  • 避免在事务中执行耗时操作（网络调用、文件IO）
  • 合理使用索引减少锁定范围
  • 监控长事务和死锁情况
  • 使用连接池管理数据库连接

事务是保证数据一致性的核心机制，正确理解和使用事务是构建可靠数据库应用的基础。

默认页脚
Copyright © 2025 清峰