如果有多人同時對同一張資料表進行操作，資料庫要怎麼知道該保留誰的儲存呢？

直觀的想法是：在某段時間內，只讓一個人進行操作，其他人則不能讀取或修改資料。對於等待操作的其他人來說，這段時間內資料表被鎖起來了！等到這個人的操作完成並釋放了鎖，下一個人才能獲得鎖並進行操作。

所以，資料庫鎖的作用是：

防止多個請求同時對同一資料列或資料表進行讀取或寫入，避免造成資料競爭和不一致，提升系統的穩定性與可靠性。

表鎖定 VS 行鎖定

資料庫鎖又可分為表鎖定跟行鎖定，下文所稱之事務指Transaction：

• 表鎖定：當一個事務需要修改整個資料表時，可以取得這張表的表鎖定。表鎖定會限制其他事務對該表的任何訪問或修改，可能導致併發性能下降。

• 行鎖定：當一個事務需要修改某些行時，可以取得這些行的行鎖定。行鎖定又分成「共享鎖(讀鎖)」及「排他鎖(寫鎖)」。

取得共享鎖的多個事務都可以讀取某資料行，但共享鎖會阻止其他事務修改此資料行；取得排他鎖的事務才可對資料行進行更新或刪除，排他鎖是唯一的，所以當前事務取得排他鎖後，其他事務不可以取得該資料行的共享鎖及排他鎖。

行鎖定只限制特定行的訪問或修改，不影響同表內其他行的存取與修改，這是它的優點。因此行鎖定適用於：

1. 高併發情境的資料庫操作，例如「線上交易系統」。
2. 只需要對特定行進行操作時，如更新或刪除單一使用者的資料或記錄。

使用的必要條件

如果需要使用行鎖，需先確認資料庫的設定，因為檢查的重點有2個：

• 資料庫支援行鎖定否
• 隔離級別正確否

如果資料庫不支援行鎖或正確的隔離級別未啟用，2個事務有可能會讀到相同的庫存量，然後都進入更新語句甚至執行。

資料庫支援行鎖定否

如果資料庫使用MySQL，須確定儲存引擎使用InnoDB，因為舊版的 MyISAM 引擎並不支援行鎖定。2種儲存引擎的特點比較如下表 (詳細說明可閱讀「參考資料5. MySQL資料庫引擎InnoDB與MyISAM有何差異?)：

雖然MyISAM的功能較少，但有個特有的優勢：單表損毀時，其他表的資料不會受到影響，InnoDB就沒有這個特性，所以需要搭配資料自動備份機制。但InnoDB也還有以下特點：

• 使用多版本並發控制（MVCC）來實現高度的並行性能
• 支持內部一致性約束
• 死鎖監控機制

由於前2點都是很大的學問，考量到篇幅，這篇先不多做解釋，對於第3點部分，本文稍後會介紹。

隔離級別

在資料庫中，事務隔離性是指：一個事務的執行不應受到其他事務的干擾。MySQL透過不同的隔離等級來控制事務間的干擾程度，同時使用鎖定來確保事務的隔離性。

在說明事務隔離的級別前，需要先理解事務隔離性不佳時會產生的問題，包含：

• 髒讀 (Dirty Read)：讀到了別人尚未提交的資料
• 幻讀（Phantom Read）：同樣條件兩次查詢的結果列數不同
• 不可重複讀（Non-repeatable Read）：同樣條件兩次查詢的結果值不同

對於以上問題，MySQL 事務隔離級別與對應能解決問題的關係整理如下表：

行鎖配合隔離級別

如果目的是防止超賣，建議使用預設隔離級別（Repeatable Read），因為它是在選取資料行時就進行上鎖，但Read Committed 在選取時只確保查詢最新提交的資料，寫入時才嘗試鎖定。

讓我們試著想想看：假使現在有2個事務在競買最後一個庫存，在隔離級別 Read Committed 下，可能發生什麼問題呢？

1. 事務A啟動後，update語句已進行了資料行選取。
2. 隨後事務B啟動，因為此時事務A的更新尚未提交，所以事務B也可選取到資料行。
3. 事務A提交後，完成扣庫，庫存此時為0
4. 事務B已在步驟2選取到資料行，所以此時可以執行完成扣庫，庫存最終為-1。

而預設隔離級別（Repeatable Read）之下，上述問題則不會發生。

使用行鎖可能發生的問題

行鎖需要額外的資料庫資源來管理鎖定狀態，所以在大量資料操作時，會降低效能。除此之外，當多個事務交叉操作同個資料行時，會有發生死鎖的風險。

死鎖

當兩個以上的事務相互等待對方持有的鎖釋出時，會導致所有事務都無法繼續執行。假設現在有2個事務，分別為事務A與事務B......

最終的結果是，事務A與事務B都在無限等待對方釋放鎖，形成交叉鎖定。

死鎖監控

實際上，由於 MySQL 自帶死鎖偵測機制，且預設開啟，所以會自動回滾一個事務，解除相互等待的狀況。被回滾的事務是回滾成本最小的，例如：事務已經執行的語句數量較少、事務持有的鎖數量較少、事務的執行時間較短等。當死鎖監控被關閉時，事務間會相互等待，直到鎖等待超時，該事務會自動回滾。

如果想要更深入了解死鎖，可以閱讀「參考資料6. MySQL InnoDB死锁原因及改善建议（InnoDB Deadlocks）」。

防範死鎖

在程式設計時，我們可以透過幾種方式來減少死鎖發生的情況：

• 盡量避免設計過長的事務：讓事務都能及時提交，避免因為長時間持有鎖增加死鎖的風險。
• 事務順序一致性：多個事務依照相同的順序存取資源，避免交叉鎖定。
• 合理設計索引：確保事務涉及的資料能透過索引快速定位，減少掃描的行數，事務能更快被提交。

實際寫法

假設隔離級別是Repeatable Read），假設客戶A跟客戶B都想要購買某項商品，進行扣庫時，庫存條件需大於0，執行以下SQL。

UPDATE product
SET stock = stock - 1
WHERE product_id = ? AND stock > 0;

於是模擬兩個事務執行SQL的情況：

然後，應用層一定要檢查資料庫修改後的影響行數，通常ORM框架執行update方法後的回傳值恰為這個意義。只有 rows affected = 1者，才能視為扣庫成功，代表消費者確實有買到這個商品；若返回值是0，代表沒有資料行受到影響，扣庫失敗，消費者未能買到商品。

在這個例子中，T5的影響行數為1，T7的影響行數是0，所以確實有買到商品的消費者是A。

參考資料

1. MySQL的Lock是什麼? Table Lock與Row Lock有哪些不同?
2. MySQL 有哪些鎖？
3. 資料庫系列 - 4: DB Lock
4. MySQL 是如何實現鎖的？什麼是行鎖、表鎖？鎖與索引的關係
5. MySQL資料庫引擎InnoDB與MyISAM有何差異?
6. MySQL InnoDB死锁原因及改善建议（InnoDB Deadlocks）
7. 面試題：MySQL 是如何實現事務的，以及我們在日常使用中需要注意什麼

延伸閱讀

1. 【MySQL】MVCC