數(shù)據(jù)庫事務(wù)與鎖機制：十個核心場景 + SQL 實戰(zhàn)案例
還在為數(shù)據(jù)庫事務(wù)不一致頭疼？明明加了鎖卻還是出現(xiàn)臟數(shù)據(jù)？別再讓這些問題拖慢項目進度了！今天這篇文章，我整理了 10 個數(shù)據(jù)庫事務(wù)與鎖機制的核心場景，每個場景都配上真實可運行的 SQL 案例，帶你從理論到實戰(zhàn)，徹底搞懂事務(wù) ACID 特性和各種鎖的用法，讓你的系統(tǒng)數(shù)據(jù)零錯誤！
一、事務(wù)基礎(chǔ)：從 ACID 到隔離級別
1. 什么是事務(wù)？用一個轉(zhuǎn)賬案例說清楚
事務(wù)就是一組不可分割的 SQL 操作，要么全成功，要么全失敗。比如轉(zhuǎn)賬時，A 賬戶扣錢和 B 賬戶加錢必須同時完成：
-- 開啟事務(wù)
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;為什么必須用事務(wù)？
如果沒有事務(wù)，當 A 扣錢后系統(tǒng)崩潰，B 賬戶沒加錢，就會導(dǎo)致錢憑空消失！
2. 事務(wù)隔離級別：解決并發(fā)問題的關(guān)鍵
MySQL 默認隔離級別是可重復(fù)讀，但不同級別解決的問題不同，用對了能避免臟讀、不可重復(fù)讀和幻讀：
隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
讀未提交（Read Uncommitted） | 可能 | 可能 | 可能 |
讀已提交（Read Committed） | 避免 | 可能 | 可能 |
可重復(fù)讀（Repeatable Read） | 避免 | 避免 | 可能 |
串行化（Serializable） | 避免 | 避免 | 避免 |
如何設(shè)置隔離級別？
-- 查看當前隔離級別
SELECT @@tx_isolation;
-- 設(shè)置會話隔離級別為讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;二、事務(wù)實戰(zhàn)：避免數(shù)據(jù)不一致的 3 個核心場景
3. 轉(zhuǎn)賬場景：用事務(wù)保證原子性
場景：A 向 B 轉(zhuǎn)賬 100 元，必須保證扣錢和加錢同時成功。
-- 初始化賬戶數(shù)據(jù)
CREATE TABLE account (
id INT PRIMARY KEY,
balance DECIMAL(10,2) NOT NULL
);
INSERT INTO account VALUES (1, 1000), (2, 1000);
-- 事務(wù)執(zhí)行轉(zhuǎn)賬
START TRANSACTION;
-- A扣錢
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- B加錢
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 檢查是否有錯誤，無錯誤提交
COMMIT;
-- 若出錯則回滾
-- ROLLBACK;如果中途出錯？
比如執(zhí)行完 A 扣錢后數(shù)據(jù)庫崩潰，事務(wù)會自動回滾，A 的余額會恢復(fù)，避免損失。
4. 訂單創(chuàng)建：事務(wù)嵌套多表操作
場景：創(chuàng)建訂單時，需同時操作訂單表和庫存表，任何一步失敗都要全部回滾。
START TRANSACTION;
-- 1. 創(chuàng)建訂單
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;
-- 檢查庫存扣減是否成功（影響行數(shù)為0則失敗）
IF ROW_COUNT() = 0 THEN
ROLLBACK; -- 庫存不足，回滾
ELSE
COMMIT; -- 全部成功，提交
END IF;關(guān)鍵技巧：用ROW_COUNT()判斷更新是否生效，避免超賣問題。
5. 并發(fā)查詢：隔離級別如何影響結(jié)果？
場景：兩個事務(wù)同時查詢并修改同一條數(shù)據(jù)，不同隔離級別會產(chǎn)生不同結(jié)果。
讀未提交（Read Uncommitted）：能看到其他事務(wù)未提交的數(shù)據(jù)（臟讀）
-- 事務(wù)1
START TRANSACTION;
UPDATE user SET balance = 1000 WHERE id = 1;
-- 事務(wù)2（此時能看到事務(wù)1未提交的1000）
SELECT balance FROM user WHERE id = 1; -- 結(jié)果1000
-- 事務(wù)1回滾
ROLLBACK;
-- 事務(wù)2再次查詢（數(shù)據(jù)變回原來的值，產(chǎn)生臟讀）
SELECT balance FROM user WHERE id = 1; -- 結(jié)果500讀已提交（Read Committed）：只能看到已提交的數(shù)據(jù)（解決臟讀，但有不可重復(fù)讀）
-- 事務(wù)1查詢
START TRANSACTION;
SELECT balance FROM user WHERE id = 1; -- 結(jié)果500
-- 事務(wù)2修改并提交
START TRANSACTION;
UPDATE user SET balance = 1000 WHERE id = 1;
COMMIT;
-- 事務(wù)1再次查詢（結(jié)果變了，不可重復(fù)讀）
SELECT balance FROM user WHERE id = 1; -- 結(jié)果1000生產(chǎn)建議：互聯(lián)網(wǎng)項目常用讀已提交，平衡一致性和性能；金融項目用可重復(fù)讀或串行化。
三、鎖機制實戰(zhàn)：解決并發(fā)沖突
6. 行鎖：鎖住單行數(shù)據(jù)，提高并發(fā)
場景：秒殺活動中，多個用戶同時搶購?fù)簧唐罚眯墟i防止超賣。
-- 事務(wù)1：用戶A搶購商品5
START TRANSACTION;
-- 悲觀鎖：for update 鎖住行
SELECT stock FROM product_stock
WHERE product_id = 5 FOR UPDATE; -- 假設(shè)庫存10
-- 扣減庫存
UPDATE product_stock
SET stock = stock - 1
WHERE product_id = 5;
COMMIT;
-- 事務(wù)2：用戶B同時搶購
START TRANSACTION;
-- 此時會等待事務(wù)1釋放鎖
SELECT stock FROM product_stock
WHERE product_id = 5 FOR UPDATE; -- 等事務(wù)1提交后，庫存顯示9
UPDATE product_stock
SET stock = stock - 1
WHERE product_id = 5;
COMMIT;原理：FOR UPDATE會對查詢的行加排他鎖，其他事務(wù)必須等待鎖釋放才能操作同一行。
7. 表鎖：整表鎖定，適合全表操作
場景：批量更新全表數(shù)據(jù)時，用表鎖避免其他事務(wù)干擾。
-- 加表級寫鎖
LOCK TABLES product_stock WRITE;
-- 批量更新
UPDATE product_stock SET stock = 0;
-- 釋放鎖
UNLOCK TABLES;注意：表鎖會阻塞所有讀寫操作，慎用！僅適合短時間的全表操作。
8. 間隙鎖：防止插入幻影數(shù)據(jù)
場景：查詢年齡大于 30 的用戶并修改，防止其他事務(wù)插入新的年齡大于 30 的用戶（幻讀）。
-- 事務(wù)1：查詢并鎖定間隙
START TRANSACTION;
SELECT * FROM user
WHERE age > 30 FOR UPDATE; -- InnoDB在可重復(fù)讀級別下會加間隙鎖
-- 事務(wù)2：嘗試插入年齡35的用戶（會被阻塞）
INSERT INTO user (name, age) VALUES ('張三', 35); -- 等待鎖釋放
-- 事務(wù)1提交后，事務(wù)2才能執(zhí)行
COMMIT;原理：間隙鎖會鎖定一個范圍（如 30 到正無窮），阻止在該范圍內(nèi)插入新數(shù)據(jù)，解決幻讀問題。
四、死鎖與優(yōu)化：從排查到解決
9. 死鎖產(chǎn)生與避免：兩個事務(wù)互相等待鎖
場景：事務(wù) 1 鎖住 A 行等待 B 行，事務(wù) 2 鎖住 B 行等待 A 行，導(dǎo)致死鎖。
-- 事務(wù)1
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1; -- 鎖id=1的行
-- 事務(wù)2
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 2; -- 鎖id=2的行
-- 事務(wù)1嘗試更新id=2（等待事務(wù)2釋放鎖）
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 事務(wù)2嘗試更新id=1（等待事務(wù)1釋放鎖，此時死鎖）
UPDATE account SET balance = balance + 100 WHERE id = 1;解決方法：
- 統(tǒng)一操作順序：所有事務(wù)都先操作 id 小的行
- 減少鎖持有時間：盡量在事務(wù)末尾執(zhí)行更新操作
- 設(shè)置鎖超時：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
-- 版本號不匹配，說明已被其他事務(wù)修改，重試或提示失敗
END IF;優(yōu)點：不用加鎖，通過版本號控制，適合高并發(fā)讀場景（如商品詳情）。
為什么事務(wù)與鎖必須一起學？
事務(wù)保證了數(shù)據(jù)的一致性，而鎖機制是事務(wù)并發(fā)執(zhí)行的基礎(chǔ)。不懂鎖的事務(wù)設(shè)計，就像給房子裝了門卻不裝鎖 —— 看似有保護，實則漏洞百出。這 10 個場景覆蓋了 90% 的實際開發(fā)問題：
- 用對隔離級別，平衡性能和一致性
- 行鎖 + 間隙鎖解決并發(fā)更新和幻讀
- 樂觀鎖適合高并發(fā)讀場景，悲觀鎖適合寫密集場景
- 死鎖可以通過統(tǒng)一操作順序避免
