数据库事务与锁机制：十个核心场景 + SQL 实战案例

2025-08-16 12:34:16 栏目：宝塔面板 1 阅读

还在为数据库事务不一致头疼？明明加了锁却还是出现脏数据？别再让这些问题拖慢项目进度了！今天这篇文章，我整理了 10 个数据库事务与锁机制的核心场景，每个场景都配上真实可运行的 SQL 案例，带你从理论到实战，彻底搞懂事务 ACID 特性和各种锁的用法，让你的系统数据零错误！

一、事务基础：从 ACID 到隔离级别

1. 什么是事务？用一个转账案例说清楚

事务就是一组不可分割的 SQL 操作，要么全成功，要么全失败。比如转账时，A 账户扣钱和 B 账户加钱必须同时完成：

-- 开启事务


START TRANSACTION;


-- A账户扣100元


UPDATE account SET balance = balance - 100 WHERE id = 1;


-- B账户加100元


UPDATE account SET balance = balance + 100 WHERE id = 2;


-- 全部成功则提交


COMMIT;


-- 若有错误则回滚


-- ROLLBACK;

为什么必须用事务？

如果没有事务，当 A 扣钱后系统崩溃，B 账户没加钱，就会导致钱凭空消失！

2. 事务隔离级别：解决并发问题的关键

MySQL 默认隔离级别是可重复读，但不同级别解决的问题不同，用对了能避免脏读、不可重复读和幻读：

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	可能	可能	可能
读已提交（Read Committed）	避免	可能	可能
可重复读（Repeatable Read）	避免	避免	可能
串行化（Serializable）	避免	避免	避免

如何设置隔离级别？

-- 查看当前隔离级别


SELECT @@tx_isolation;


-- 设置会话隔离级别为读已提交


SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

二、事务实战：避免数据不一致的 3 个核心场景

3. 转账场景：用事务保证原子性

场景：A 向 B 转账 100 元，必须保证扣钱和加钱同时成功。

-- 初始化账户数据


CREATE TABLE account (


   id INT PRIMARY KEY,


   balance DECIMAL(10,2) NOT NULL

);


INSERT INTO account VALUES (1, 1000), (2, 1000);


-- 事务执行转账


START TRANSACTION;


-- A扣钱


UPDATE account SET balance = balance - 100 WHERE id = 1;


-- B加钱


UPDATE account SET balance = balance + 100 WHERE id = 2;


-- 检查是否有错误，无错误提交


COMMIT;


-- 若出错则回滚


-- ROLLBACK;

如果中途出错？

比如执行完 A 扣钱后数据库崩溃，事务会自动回滚，A 的余额会恢复，避免损失。

4. 订单创建：事务嵌套多表操作

场景：创建订单时，需同时操作订单表和库存表，任何一步失败都要全部回滚。

START TRANSACTION;


-- 1. 创建订单


INSERT INTO orders (order_no, user_id, total_amount)


VALUES ('20250703001', 1001, 299.00);


-- 2. 扣减库存


UPDATE product_stock


SET stock = stock - 1


WHERE product_id = 5 AND stock >= 1;


-- 检查库存扣减是否成功（影响行数为0则失败）


IF ROW_COUNT() = 0 THEN

   ROLLBACK; -- 库存不足，回滚


ELSE

   COMMIT; -- 全部成功，提交


END IF;

关键技巧：用ROW_COUNT()判断更新是否生效，避免超卖问题。

5. 并发查询：隔离级别如何影响结果？

场景：两个事务同时查询并修改同一条数据，不同隔离级别会产生不同结果。

读未提交（Read Uncommitted）：能看到其他事务未提交的数据（脏读）

-- 事务1

START TRANSACTION;


UPDATE user SET balance = 1000 WHERE id = 1;


-- 事务2（此时能看到事务1未提交的1000）


SELECT balance FROM user WHERE id = 1; -- 结果1000

-- 事务1回滚


ROLLBACK;


-- 事务2再次查询（数据变回原来的值，产生脏读）


SELECT balance FROM user WHERE id = 1; -- 结果500

读已提交（Read Committed）：只能看到已提交的数据（解决脏读，但有不可重复读）

-- 事务1查询


START TRANSACTION;


SELECT balance FROM user WHERE id = 1; -- 结果500

-- 事务2修改并提交


START TRANSACTION;


UPDATE user SET balance = 1000 WHERE id = 1;


COMMIT;


-- 事务1再次查询（结果变了，不可重复读）


SELECT balance FROM user WHERE id = 1; -- 结果1000

生产建议：互联网项目常用读已提交，平衡一致性和性能；金融项目用可重复读或串行化。

三、锁机制实战：解决并发冲突

6. 行锁：锁住单行数据，提高并发

场景：秒杀活动中，多个用户同时抢购同一商品，用行锁防止超卖。

-- 事务1：用户A抢购商品5

START TRANSACTION;


-- 悲观锁：for update 锁住行


SELECT stock FROM product_stock


WHERE product_id = 5 FOR UPDATE; -- 假设库存10

-- 扣减库存


UPDATE product_stock


SET stock = stock - 1


WHERE product_id = 5;


COMMIT;


-- 事务2：用户B同时抢购


START TRANSACTION;


-- 此时会等待事务1释放锁


SELECT stock FROM product_stock


WHERE product_id = 5 FOR UPDATE; -- 等事务1提交后，库存显示9

UPDATE product_stock


SET stock = stock - 1


WHERE product_id = 5;


COMMIT;

原理：FOR UPDATE会对查询的行加排他锁，其他事务必须等待锁释放才能操作同一行。

7. 表锁：整表锁定，适合全表操作

场景：批量更新全表数据时，用表锁避免其他事务干扰。

-- 加表级写锁


LOCK TABLES product_stock WRITE;


-- 批量更新


UPDATE product_stock SET stock = 0;


-- 释放锁


UNLOCK TABLES;

注意：表锁会阻塞所有读写操作，慎用！仅适合短时间的全表操作。

8. 间隙锁：防止插入幻影数据

场景：查询年龄大于 30 的用户并修改，防止其他事务插入新的年龄大于 30 的用户（幻读）。

-- 事务1：查询并锁定间隙


START TRANSACTION;


SELECT * FROM user


WHERE age > 30 FOR UPDATE; -- InnoDB在可重复读级别下会加间隙锁


-- 事务2：尝试插入年龄35的用户（会被阻塞）


INSERT INTO user (name, age) VALUES ('张三', 35); -- 等待锁释放


-- 事务1提交后，事务2才能执行


COMMIT;

原理：间隙锁会锁定一个范围（如 30 到正无穷），阻止在该范围内插入新数据，解决幻读问题。

四、死锁与优化：从排查到解决

9. 死锁产生与避免：两个事务互相等待锁

场景：事务 1 锁住 A 行等待 B 行，事务 2 锁住 B 行等待 A 行，导致死锁。

-- 事务1

START TRANSACTION;


UPDATE account SET balance = balance - 100 WHERE id = 1; -- 锁id=1的行


-- 事务2

START TRANSACTION;


UPDATE account SET balance = balance - 100 WHERE id = 2; -- 锁id=2的行


-- 事务1尝试更新id=2（等待事务2释放锁）


UPDATE account SET balance = balance + 100 WHERE id = 2;


-- 事务2尝试更新id=1（等待事务1释放锁，此时死锁）


UPDATE account SET balance = balance + 100 WHERE id = 1;

解决方法：

统一操作顺序：所有事务都先操作 id 小的行
减少锁持有时间：尽量在事务末尾执行更新操作
设置锁超时：SET innodb_lock_wait_timeout = 5;（5 秒超时）

10. 乐观锁：适合读多写少的场景

场景：商品详情页频繁查询，偶尔更新库存，用乐观锁减少锁竞争。

-- 表中增加version字段


CREATE TABLE product_stock (


   product_id INT PRIMARY KEY,


   stock INT NOT NULL,


   version INT NOT NULL DEFAULT 0 -- 版本号


);


-- 更新时检查版本号


UPDATE product_stock


SET stock = stock - 1, version = version + 1

WHERE product_id = 5 AND version = 0; -- 只有版本号匹配才更新


-- 判断是否更新成功


IF ROW_COUNT() = 0 THEN

   -- 版本号不匹配，说明已被其他事务修改，重试或提示失败


END IF;

优点：不用加锁，通过版本号控制，适合高并发读场景（如商品详情）。

为什么事务与锁必须一起学？

事务保证了数据的一致性，而锁机制是事务并发执行的基础。不懂锁的事务设计，就像给房子装了门却不装锁 —— 看似有保护，实则漏洞百出。这 10 个场景覆盖了 90% 的实际开发问题：