MySQL 事务与锁机制入门

转账时钱扣了但对方没收到？——事务 就是解决这类问题的关键机制。

一、什么是事务？

事务（Transaction） 是一组 SQL 操作的集合，要么全部执行成功，要么全部失败回滚。

经典案例：转账

-- 如果没有事务，这条执行成功后第二条失败，钱就丢了
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 扣钱
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 加钱

用事务解决

START TRANSACTION;  -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- 提交（两条都成功）
-- 或者 ROLLBACK;  -- 回滚（两条都取消）

二、事务的 ACID 特性

三、事务的隔离级别

4 种隔离级别

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

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

MySQL 默认 隔离级别是 REPEATABLE READ（可重复读）

问题解释

四、锁机制

锁的分类

-- 行锁：只锁住某一行（InnoDB）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 表锁：锁住整张表
LOCK TABLES users READ;   -- 读锁
LOCK TABLES users WRITE;  -- 写锁

-- 乐观锁 vs 悲观锁（应用层面）

行锁实战

-- 事务A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 加行锁
-- 此时事务B无法修改 id=1 的行
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 释放锁

死锁示例

-- 事务A                                   事务B
START TRANSACTION;                       START TRANSACTION;
UPDATE accounts SET balance=900 WHERE id=1;  UPDATE accounts SET balance=900 WHERE id=2;
UPDATE accounts SET balance=1100 WHERE id=2;  UPDATE accounts SET balance=1100 WHERE id=1;
-- 事务A等待事务B释放id=2                     -- 事务B等待事务A释放id=1
-- MySQL 自动检测死锁，回滚其中一个事务

提示 避免死锁的技巧：所有事务按相同顺序访问资源。

五、事务实战

银行转账完整示例

DELIMITER //
CREATE PROCEDURE transfer(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败，已回滚' AS message;
    END;
    
    START TRANSACTION;
    
    -- 检查余额
    SELECT balance INTO @bal FROM accounts WHERE id = from_id FOR UPDATE;
    IF @bal < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 扣钱
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    -- 加钱
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    
    COMMIT;
    SELECT '转账成功' AS message;
END //
DELIMITER ;

-- 使用
CALL transfer(1, 2, 100.00);

订单与库存（避免超卖）

START TRANSACTION;

-- 锁定库存
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 检查库存
IF stock > 0 THEN
    UPDATE products SET stock = stock - 1 WHERE id = 1;
    INSERT INTO orders (user_id, product_id) VALUES (1, 1);
    COMMIT;
ELSE
    ROLLBACK;
    SELECT '库存不足' AS message;
END IF;

六、事务使用注意事项

不要做的事情

-- × 事务中混入非事务表
START TRANSACTION;
UPDATE users SET name = '张三' WHERE id = 1;  -- InnoDB 支持事务
UPDATE log SET msg = '修改用户' WHERE id = 1; -- MyISAM 不支持事务，无效
COMMIT;

-- × 事务中执行 DDL（自动提交）
START TRANSACTION;
ALTER TABLE users ADD COLUMN age INT;  -- 隐式提交当前事务
UPDATE users SET name = '李四' WHERE id = 2;  -- 这已经是新事务了
COMMIT;

事务的最佳实践

本篇小结

√ 理解了事务的 ACID 四大特性
√ 掌握了 4 种隔离级别及其解决的问题
√ 学会了行锁和表锁的使用
√ 知道了死锁的原因和预防方法
√ 实战了转账和库存等经典场景

下一篇我们将学习 备份与恢复！