前言

近年來隨著網路發展愈來愈快，高併發的場景愈來愈常見，高併發性能是指系統能夠有效地處理大量同時訪問和操作數據的能力，這在網路應用、金融系統、電子商務等領域愈來愈重要．隨著數據量的不斷增長和用戶需求的不斷擴展，我們需要確保我們的資料庫系統能夠承受高負載、高併發的壓力，同時保持良好的性能和穩定性。在這樣的背景下，優化資料庫的設計和架構、實施有效的併發控制策略成為至關重要的任務。

本篇是研究了一般 RDBMs 如何處理高併發問題的常見解法

Lock 機制

我們都知道寫程式的時候，如果有兩個 process/thread 要對同筆資料進行操作，為了避免資料的不一致，我們常會加上鎖的機制，臨時將該資源上鎖，僅允許一次只能被一個 process/thread 更動．同樣的，RDBMs 也有相同的機制來處理這種情形，這也是傳統資料庫處理高併發的最基本原理

SELECT … FOR UPDATE

為了避免兩個 transaction 同時對同筆資料進行操作，造成資料不一致的問題，一般常見的 RDBMs 都有提供底下這個 SQL 語法

BEGIN
SELECT ... FOR UPDATE
<各式針對該資料的操作, ex: UPDATE, DELETE...>
COMMIT

一旦開啟新的 transaction 並執行這段 SQL 語法，在還沒 COMMIT 以前，資料庫將會替當前這筆資料加上鎖，鎖住這些即將要被更動的資料，因為資料庫悲觀的認為他們隨時有可能被修正(悲觀鎖)，一旦上鎖後，下一個 tx 要進行操作時，都需要等待該鎖釋放後才能進行

至於這個鎖是行級鎖還是表級鎖，以及這個排他鎖會鎖哪些操作(SELECT? INSERT? UPDATE? DELETE?)，則因各種資料庫背後的機制而不同

以 MySQL(InnoDB)為例，如果 SELECT 的對象加上 WHERE 條件是主鍵或有加上索引，則僅會加上行級鎖，鎖住當前查詢到的資料．但如果 WHERE 條件不是主鍵或非索引，則整個資料表都會被鎖住．正因如此，一旦 SELECT ... FOR UPDATE 沒寫好，MySQL 是有可能把整個資料表都鎖住，造成其他 transaction 需要等待鎖釋放才能進行操作，進而增加 latency 而影響使用者體驗．(以上前提要是 MySQL 的 InnoDB 作為 storage engine，因為 MyISAM 預設都是表級鎖)

以 PostgreSQL 為例，則不會有上述提到的限制，因為 PostgreSQL 背後使用 MVCC(Multi-Version Concurrency Control)機制，每次的操作其實都是 append 一個新版本的記錄，在 repeatable-read 的 isolation 層級下，只需要去查詢當前 tx id 之前的資料就好，所以當 tx1 下了 SELECT ... FOR UPDATE 後，tx2 執行時並不會把整個資料表鎖住

情境模擬

分別用 docker 運行 MySQL(8.4.0) 和 PostgreSQL(14.6) 兩種資料庫，來模擬併發下的差異

分別進到兩個資料庫的 shell 建立一個簡單的表，該表的 id 欄位是主鍵，name 欄位加上 UNIQUE 的索引，age 欄位則沒有任何索引

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    age INT
);

新增三筆測試資料

INSERT INTO users (id, name, age) VALUES (1, 'Madi', 28);
INSERT INTO users (id, name, age) VALUES (2, 'Terry', 25);
INSERT INTO users (id, name, age) VALUES (3, 'Eric', 29);

MySQL

底下用 A,B 分別代表兩個 shell

BEGIN;   --A
SELECT * FROM users FOR UPDATE; --A

SELECT * FROM users;  --B, non block
UPDATE users SET age=20 WHERE id=1; --B, Blocking...
INSERT INTO users (id, name, age) VALUES (4, 'Gobert', 29);  --B, Blocking...
DELETE FROM users WHERE id=1;  --B, Blocking...

COMMIT;  --A,

可以看出以上四個操作，只有 SELECT 不會被 block 住，其他操作如 INSERT, UPDATE, DELETE 都會在 shell A 執行 COMMIT 前， shell B 因未獲得排他鎖而無法對資料異動

PostgreSQL

底下用 A,B 分別代表兩個 shell

BEGIN;   --A
SELECT * FROM users FOR UPDATE; --A

SELECT * FROM users;  --B, non block
UPDATE users SET age=20 WHERE id=1; --B, Blocking...
INSERT INTO users (id, name, age) VALUES (4, 'Gobert', 29);  --B, non block
DELETE FROM users WHERE id=1;  --B, Blocking...

COMMIT;  --A,

可以看出以上四個操作，除了 SELECT 不會被 block 住以外，INSERT 也不會被 block 住，這要歸功於 PostgreSQL 背後的 MVCC 機制，由於每個 transaction 都會生成一個版本，且不同 transaction 之間的操作不會互相阻塞，所以 INSERT 的數據並不在 SELECT ... FRO UPDATE 的鎖定範圍內，所以不會被 block 住，至於其他操作如 UPDATE, DELETE 則一樣會在 shell A 執行 COMMIT 前， shell B 因未獲得排他鎖而無法對資料異動

SELECT FOR UPDATE WHERE ...

WHERE 條件搭配主鍵

以 MySQL 為例，試看看使用 主鍵(id) 來作為搜尋條件，驗證看看是否只有行級鎖，而非表級鎖

BEGIN;   --A
SELECT * FROM users WHERE id=1 FOR UPDATE; --A, 限定 id=1 這筆資料

SELECT * FROM users WHERE id=1;  --B, non block
UPDATE users SET age=20 WHERE id=1; --B, Blocking...
INSERT INTO users (id, name, age) VALUES (4, 'Gobert', 29);  --B, non block
UPDATE users SET age=11 WHERE id=2; --B, 修改其他 row 的資料不會被 block 住
DELETE FROM users WHERE id=1;  --B, Blocking...

COMMIT;  --A,

由此可見當 SELECT ... WHERE id=1 FOR UPDATE 時，因為 id 是主鍵，會讓 MySQL 僅加上行級鎖，此時修改其他列的資料(第四個 SQL)或是新增一筆資料(第三個 SQL)都不會被阻塞，證明確實是行級鎖

WHERE 條件搭配索引欄位

再來一樣使用 MySQL，試看看使用 索引欄位(name) 來作為搜尋條件，驗證看看是否只有行級鎖，而非表級鎖

BEGIN;   --A
SELECT * FROM users WHERE name='madi' FOR UPDATE; --A, 限定 name='madi' 這筆資料

SELECT * FROM users WHERE name='madi';  --B, non block
UPDATE users SET age=20 WHERE name='madi'; --B, Blocking...
INSERT INTO users (id, name, age) VALUES (4, 'Gobert', 29);  --B, non block
UPDATE users SET age=11 WHERE id=2; --B, 修改其他 row 的資料不會被 block 住
DELETE FROM users WHERE name='madi';  --B, Blocking...

COMMIT;  --A,

由此可見當 SELECT ... WHERE name='madi' FOR UPDATE 時，因為 name 有加上索引，會讓 MySQL 僅加上行級鎖，此時修改其他列的資料(第四個 SQL)或是新增一筆資料(第三個 SQL)都不會被阻塞，證明確實是行級鎖

WHERE 條件搭配非索引欄位

再來一樣使用 MySQL，試看看使用 非索引欄位(age) 來作為搜尋條件，驗證看看是否會加上表級鎖，將整個表都鎖住

BEGIN;   --A
SELECT * FROM users WHERE age=29 FOR UPDATE; --A, 限定 name='madi' 這筆資料

SELECT * FROM users WHERE age=29;  --B, non block
UPDATE users SET name='hello' WHERE age=29; --B, Blocking...
INSERT INTO users (id, name, age) VALUES (4, 'Gobert', 29);  --B, blocking
UPDATE users SET age=11 WHERE id=2; --B, 修改其他 row 的資料也會被 block 住
DELETE FROM users WHERE name='madi';  --B, Blocking...

COMMIT;  --A,

由此可見當 SELECT ... WHERE age=29 FOR UPDATE 時，因為 age 欄位既不是主鍵也沒有索引，會讓 MySQL 加上表級鎖，整個表都無法對其進行修改刪除新增，此時修改其他列的資料(第四個 SQL)或是新增一筆資料(第三個 SQL)都會被阻塞，證明確實是表級鎖

結論

這次研究了一下高併發時 RDBMs 常見的解決方案，透過加上鎖來維持資料的一致性，並透過實作來驗證結論

  • 透過 SELECT ... FOR UPDATE 來為資料加上排他鎖，避免其他 tx 同時對其改動，造成資料不一致
  • 以 MySQL(InnoDB)為例，如果 SELECT 的對象加上 WHERE 條件是主鍵或有加上索引，則僅會加上行級鎖，鎖住當前查詢到的資料．但如果 WHERE 條件不是主鍵或非索引，則整個資料表都會被鎖住
  • 以 PostgreSQL 為例，因為 MVCC(Multi-Version Concurrency Control)機制，每次的操作其實都是 append 一個新版本的記錄，在 repeatable-read 的 isolation 層級下，都不會把整個資料表鎖住

資料庫概念博大精深，後續再持續研究，強迫自己定期學習

參考資料