数据库事务与锁机制学习

IT咸鱼

发布于 2025-05-20 18:45:53

4950

事务与锁是数据库保证数据一致性和处理高并发的核心技术。可以避免脏数据、处理并发冲突，并设计出高效安全的数据库应用。

一、为什么学事务与锁？

支付场景：用户A转账给用户B，必须同时成功或失败。
库存扣减：100人同时抢购10件商品，如何防止超卖？
报表生成：统计过程中数据被修改，如何保证统计准确性？

二、以MySQL为例

1. 事务基础操作

-- 开启事务（两种方式）
START TRANSACTION;  -- 或 BEGIN;

-- 插入订单记录
INSERT INTO orders (user_id, amount) VALUES (1, 100);

-- 更新账户余额
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 提交事务（数据永久生效）
COMMIT;

-- 若发生错误则回滚
ROLLBACK;

注意：MySQL的默认隔离级别是 REPEATABLE READ，可通过以下命令查看：

SELECT @@transaction_isolation;  -- 输出：REPEATABLE-READ

2. 隔离级别与问题复现

场景：两个会话同时操作同一条数据，观察不同隔离级别的表现。

-- 会话1：事务A修改数据但不提交
START TRANSACTION;
UPDATE products SET stock = 5 WHERE id = 1;  -- 原库存10

-- 会话2：事务B在不同隔离级别下的读取结果
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT stock FROM products WHERE id = 1;  -- 可能读到5（脏读）
COMMIT;

隔离级别对照表：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✔️	✔️	✔️
READ COMMITTED	✖️	✔️	✔️
REPEATABLE READ (默认)	✖️	✖️	✔️
SERIALIZABLE	✖️	✖️	✖️

3. 锁机制实战（解决超卖问题）

悲观锁方案：在查询时直接加锁，阻止其他事务修改。

-- 用户下单时锁定商品库存
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- 加排他锁

-- 检查库存并扣减
IF stock >= 1 THEN
  UPDATE products SET stock = stock - 1 WHERE id = 1;
END IF;

COMMIT;

乐观锁方案：通过版本号检测冲突，适合低竞争场景。

-- 商品表添加版本号字段
ALTER TABLE products ADD version INT DEFAULT 0;

-- 更新时校验版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 当前版本;

4. 死锁模拟与处理

-- 会话1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;  -- 锁住用户1
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;  -- 等待会话2释放锁

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE user_id = 2;  -- 锁住用户2
UPDATE accounts SET balance = balance + 200 WHERE user_id = 1;  -- 等待会话1释放锁

-- 死锁发生！MySQL会自动回滚其中一个事务
SHOW ENGINE INNODB STATUS;  -- 查看死锁日志

避免死锁建议：

事务中按固定顺序访问资源（如按用户ID排序）
减少事务执行时间（避免在事务内处理复杂逻辑）
设置合理的锁等待超时时间：SET innodb_lock_wait_timeout = 30;

四、学习效果验证

测试题

以下哪个隔离级别可以避免脏读？ A. READ UNCOMMITTED B. READ COMMITTED C. REPEATABLE READ D. SERIALIZABLE
当库存为1时，两个用户同时执行SELECT ... FOR UPDATE会发生什么？ A. 第一个用户加锁成功，第二个用户等待 B. 两个用户同时加锁成功 C. 直接触发死锁
乐观锁的实现通常依赖哪个机制？ A. 行级锁 B. 版本号或时间戳 C. 表锁

答案：是啥

建议在本地数据库中复现所有案例，观察不同参数下的行为差异。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-04-03，如有侵权请联系 cloudcommunity@tencent.com 删除

数据