數據庫事務與鎖機制：十個核心場景 + SQL 實戰案例
還在為數據庫事務不一致頭疼？明明加了鎖卻還是出現臟數據？別再讓這些問題拖慢項目進度了！今天這篇文章，我整理了 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% 的實際開發問題：
- 用對隔離級別，平衡性能和一致性
- 行鎖 + 間隙鎖解決并發更新和幻讀
- 樂觀鎖適合高并發讀場景，悲觀鎖適合寫密集場景
- 死鎖可以通過統一操作順序避免
