在之前的文章已經談過很多關於MySQL資料庫鎖定的觀念及實作，這篇把所有的觀念一次整合在一起，也比較容易完全理解MySQL的鎖定如何應用在實際的系統設計上。
鎖定的定義 :
在 MySQL 中，資料庫的鎖定是一種用於管理多用戶或程序同時訪問同一資料庫資源時的同步機制。鎖定可以幫助預防資料不一致和更新衝突，保護交易的完整性。
既然是鎖定，當然就會讓某一方等待，如果鎖得不好，就會發生死鎖 (deadlock)。
因此鎖定就是為了達成資料的一致性及完整性，犧牲某些效率的做法。你不可能既要資料的一致性及完整性，又要求任何連線都不能等待。在非常大量連線的情況下，這些等待都可能造成程式超時 (timeout) 。
例如php程式的預設超時 (default timeout) 是30秒，MySQL的預設鎖定等待超時 (lock wait timeout)是50秒，如果因為等待而超過，程式就會停止並顯示錯誤訊息。如果拉長預設超時，當遇到死鎖或是大家都在等待，就更可能吃光系統的資源，讓正在執行的更緩慢。
因此，多人連線的系統，正確的處理交易以及正確的使用鎖定是很重要的，並且正確性與效能是同樣重要的事情。
有些系統效能比正確性還重要 (例如臉書的數據處理)，有些系統正確性比效能還重要 (例如銀行的數據處理)。
效能比正確性還重要時，太多的鎖定會影響效能，就要避免不必要的鎖定，資料的正確性再用另外的方式彙整，所以你經常會看到臉書的粉絲數量忽高忽低。 正確性比效能還重要時，就要用悲觀鎖定策略，任何可能出錯的地方都要鎖定，寧可延遲也不能錯誤。
(1) LOCK TABLES ... READ
這是整個資料表單都鎖起來的語法 (Table Lock)，拒絕其他連線對該資料表的某些操作。
LOCK TABLES table-name READ (鎖起來讀):
對指定的表單施加一個全表的共享鎖（read lock）。這個鎖允許多個交易對表單進行讀取操作，但阻止任何交易(包括本身交易)對表單進行寫入操作，直到鎖被釋放。並且本身交易也不能在表單被釋放前去操作其他表單。
[範例]
連線#1 LOCK TABLES t1 READ; (把表單t1鎖起來讀取資料)
連線#1 SELECT * FROM t1; 可以執行
連線#1 執行 SELECT * FROM 其他表單，就無法執行，必須UNLOCK TABLES後，才能正常操作其他表單。
連線#1 UPDATE t1 SET f2=0 WHERE f1=1; 也是不行的，因為是鎖起來讀取資料。
如下圖
連線#2 SELECT * FROM t1; 可以執行 但是 UPDATE t1 就無法執行，會進入等待，因為被連線#1鎖起來讀取資料。
如下圖
show open tables where in_use > 0 ;
如果要知道預設鎖定等待超時是多少，可以使用 :
show variables like 'innodb_lock_wait_timeout';
(2) LOCK TABLES ... WRITE
LOCK TABLES table-name WRITE (鎖起來寫) :
對指定的表單施加一個全表的排他鎖（exclusive lock）。這個鎖會阻止其他所有用戶對這個表單的讀取和寫入操作，只有施加鎖的連接可以對這個表格進行讀取和修改，直到鎖被釋放。並且本身交易也不能在表單被釋放前去操作其他表單。
解開鎖定同樣使用 UNLOCK TABLES;
[範例]
連線#1 LOCK TABLES t1 WRITE;
連線#1 SELECT * FROM t1; 可以執行
連線#1 SELECT * FROM 其他表單; 不可以執行
連線#1 UPDATE t1 SET f2=0 WHERE f1=1; 可以執行
如下圖
連線#2 SELECT * FROM t1; 不可以執行，會進入等待。
連線#2 UPDATE t1 SET f2=0 WHERE f1=1; 當然更不可以執行，也會進入等待。
如下圖
這種表單鎖定的使用需要非常小心，因為它會阻止其他所有用戶的讀寫訪問，可能會對系統性能產生顯著影響。 在設計應用時，應該盡量避免過度依賴 LOCK TABLES，特別是在多人連線的環境中，這樣的鎖定可能會導致大規模的性能瓶頸。通常，使用更細粒度的鎖定機制（如行級鎖）或適當的事務隔離級別，可以更好地平衡數據一致性和系統性能。
(3) SELECT ... FOR UPDATE
SELECT ... FOR UPDATE 屬於獨佔鎖（Exclusive Locks）獨佔鎖允許事務對資料進行寫入操作，並且在鎖定期間不允許其他事務讀取或寫入該資料。這確保了當一個事務正在更新或修改資料時，沒有其他事務可以同時訪問這些資料，從而防止數據不一致。
特點：獨佔鎖保護數據在修改期間不被其他事務訪問或更改。
使用時機：當需要修改資料庫中的數據，並保證修改操作不被其他事務干擾時使用。
這是屬於行鎖定 Row Lock ，只鎖定資料表單的特定資料，拒絕其他連線對該資料的某些操作。
「行鎖定」這個詞需要再解釋一下，「行鎖定」的英文是row lock，在台灣row的翻譯應該是「列」，但是有些地方把row翻譯為「行」，因此有時會讓人搞得滿頭霧水。
因此我們寧願使用 row lock 而不管是「行」還是「列」，反正就是指鎖定特定一筆資料。
Select ... for update 實際範例，可以看這篇 :
實作練習 : 交易 (事務) 操作 commit 以及 rollback
要說明row lock 前，要先知道，MyISAM與InnoDB的差異。並不是MySQL中的所有資料表鎖定的功能都是一樣的，因為還需要考慮儲存引擎 (storage engine)。
MyISAM與InnoDB是MySQL兩種常用的儲存引擎，MyISAM的資料表只能做表單鎖定 (table lock)，而沒有行鎖定 (row lock)；InnoDB則支援表單鎖定與行鎖定。
深入閱讀 :
MySQL的Lock是什麼? Table Lock與Row Lock有哪些不同?
MySQL資料庫引擎InnoDB與MyISAM有何差異?
MySQL Lock : Table Lock與Row Lock
那麼我們如何知道表單是MyISAM還是InnoDB呢?
可以使用以下的語法 :
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_NAME = 'your_database_name';
如下圖
MyISAM與InnoDB差異的範例 :
這個語句用於鎖定被選取的「資料行」以進行更新操作。當你執行一個包含 FOR UPDATE 的 SELECT 語句時，該語句會對相關的資料行加上排他鎖（exclusive lock）。這樣其他事務（Transaction）就不能讀取這些被鎖定的資料行，直到目前的事務提交（commit）或回滾（rollback）。
我們用這個例子來實作，t0表單是MyISAM，t1表單是InnoDB。
連線#1
Begin;
select * from t0 where f1=1 for update;
如下圖 :
連線#2
Begin;
select * from t0 where f1=1 for update;
表單t0並不會被鎖定，因為MyISAM的t0沒有「行鎖定」的功能。
如下圖 :
連線#1繼續執行
select * from t1 where f1=1 for update;
連線#2繼續執行
select * from t1 where f1=1 for update;
這時就會進入等待狀態，因為t1被連線#1「行鎖定」了。
如下圖
使用 SELECT ... FOR UPDATE 的情形，因為是排他鎖，如果兩個連線都針對同個表單，基本上就是等待，不會deadlock。但是若兩個連線針對兩個不同表單去SELECT ... FOR UPDATE，就可能出現deadlock。
(4) SELECT ... LOCK IN SHARE MODE
共享鎖（Shared Locks） : 共享鎖允許多個讀取事務同時讀取同一資料，但防止該資料在被共享鎖定時被修改。共享鎖主要用於讀取操作，因為它們允許多個事務同時讀取相同的資料而不互相衝突。
特點：共享鎖讓多個使用者可以同時讀取數據，但當數據被共享鎖定時，不能執行寫入操作。
使用時機：當需要從資料庫讀取數據，並且希望在讀取期間數據不被修改時使用。
這個語句在讀取數據時用來鎖定所涉及的「資料行」的語句。這種鎖定方式允許其他事務讀取被鎖定的資料行，但禁止其他事務對這些資料行進行修改，直到你的事務提交（commit）或回滾（rollback）。這種鎖被稱為共享鎖（shared lock）。 LOCK IN SHARE MODE 常用於需要確保在事務處理期間數據不被修改的情況，同時又允許其他事務讀取這些數據。這適合在需要維護數據一致性的場景下讀取數據，而不完全阻止其他事務的訪問。
同樣的，這個語句在 MyISAM 也沒有效果，就不再另外舉例。
兩個連線針對InnoDB的鎖定，範例如下 ...
連線#1
Begin;
SELECT * FROM t1 WHERE f1=1 LOCK IN SHARE MODE;
連線#2
SELECT * FROM t1;
可以執行
連線#2
update t1 set f2=5 where f1=1;
會進入等待 ... 因為被連線#1鎖定
連線#1
UPDATE t1 SET f2=0 WHERE f1=1;
此時連線#2就出現deadlock訊息
COMMIT;
如下圖
連線#1
Begin;
SELECT * FROM t1 WHERE f1=1 LOCK IN SHARE MODE;
UPDATE t1 SET f2=0 WHERE f1=1;
連線#2
update t1 set f2=5 where f1=1;
會進入等待 ... 如果等太久就超時，如果連線#1在時限內結束鎖定，連線#2的UPDATE就可以執行。
如下圖
連線#1
Begin;
SELECT * FROM t1 WHERE f1=1 LOCK IN SHARE MODE;
連線#2
Begin;
SELECT * FROM t2 WHERE f1=1 LOCK IN SHARE MODE;
連線#1
SELECT * FROM t2 WHERE f1=1 LOCK IN SHARE MODE;
連線#2
SELECT * FROM t1 WHERE f1=1 LOCK IN SHARE MODE;
連線#1
UPDATE t2 SET f2=0 WHERE f1=1;
進入等待
連線#2
UPDATE t1 SET f2=0 WHERE f1=1;
出現deadlock
如下圖
但是 SELECT ... FOR UPDATE 或是 SELECT ... LOCK IN SHARE MODE 就一定是row lock嗎?
以上這兩個方式，不一定是紀錄鎖定(row level lock)，要看是否針對主鍵。如果不針對主鍵，就不是紀錄鎖定，會是表單鎖定。
我們用 SELECT ... FOR UPDATE 來試試。
以上範例，不是針對主鍵，所以變成表單鎖定。
在不同的MySQL版本還有一點差異：
MySQL 5.5 以上版本使用 Select ... lock in share mode;
MySQL 5.5 以上版本使用 Select ... for update;
MySQL 8.0 還可以寫成 Select ... for share;
MySQL 8.0 還可以寫成 Select ... for update NOWAIT;
死鎖 (deadlock) 是什麼?
死鎖（Deadlock）是在多任務或多線程環境中常見的一種情況，指的是多個程序或線程因互相等待對方持有的資源而陷入無限等待的狀態，從而無法繼續執行。
若要解決死鎖問題，可以透過資源分配策略、死鎖預防機制和死鎖檢測與恢復策略來進行。
舉一個常見的死鎖（Deadlock）的例子來說明其運作方式：
假設有兩個程式，分別是程式A和程式B，它們都需要訪問兩個資源，資源1和資源2，以完成其任務。這兩個資源同一時間只能被一個程式所使用。
1. 程式A開始運行，佔有資源1。
2. 程式B開始運行，佔有資源2。
3. 程式A需要資源2才能繼續進行下去，因此它發出請求來獲得資源2。然而，資源2已被程式B佔有，所以程式A必須等待程式B釋放資源2。
4. 與此同時，程式B也想要繼續運行，它需要資源1。程式B對資源1發出請求，但是資源1已被程式A佔有，因此程式B也必須等待程式A釋放資源1。
在這個情境中，程式A和程式B都在等待對方釋放資源，但兩者都不知道該釋放自己已擁有的資源。這樣就形成了一個循環等待的狀態而導致死鎖。除非有外部介入（例如作業系統終止其中一個程式或手動釋放資源），否則兩個程式都無法繼續執行。
死鎖實際範例 :
連線#1
Begin;
select * from t2 where f1=1 for update;
select * from t3 where f1=1 for update;
連線#2
Begin;
select * from t3 where f1=1 for update;
select * from t2 where f1=1 for update;
以上兩個連線，執行順序如下 :
(1) 連線#1 執行到 select * from t2 ...
(2) 連線#2 執行到 select * from t3 ...
(3) 當連線#1 執行 select * from t3 ... 就會進入等待狀態，因為被連線#2 鎖定。
(4) 當連線#2 執行 select * from t2 ... 就會出現 deadlock，因為兩個連線在互相等待。
實際執行如下 :
再來看另外的例子 :
連線#3
Begin;
select * from t2 where f1=1 lock in for share mode;
select * from t3 where f1=1 lock in for share mode;
update t3 set f2=0 where f1=1;
連線#4
Begin;
select * from t3 where f1=1 lock in for share mode;
select * from t2 where f1=1 lock in for share mode;
update t2 set f2=0 where f1=1;
以上兩個連線，執行順序如下 :
(1) 連線#3 執行兩個select ... 都沒有問題
(2) 連線#4 執行兩個select ... 也都沒有問題
(3) 當連線#4 執行 update t2 ... 就會進入等待狀態，因為被連線#3 鎖定。
(4) 當連線#3 執行 update t3 ... 就會出現 deadlock，因為兩個連線在互相等待。
實際執行如下 :
以上兩個範例，不管是select ... for update還是select ... lock in share mode，只要互相等待對方鎖定的資源，就會產生deadlock，差別只在於發生deadlock的時間點。
(5) 交易的排他鎖
當你使用Begin或是START TRANSACTION時，就算你不使用任何lock的機制，也會自動施加排他鎖（Exclusive Locks）。
假設t1, t2都是InnoDB
連線#1
Begin;
update t1 set f2=0 where f1=1;
連線#2
update t1 set f2=10 where f1=1;
就會進入等待 ...
如下圖
連線#1
Begin;
update t1 set f2=0 where f2>0;
連線#2
update t1 set f2=10 where f1=1;
因為連線#1是鎖表單，這樣連線#2就會進入等待
如下圖
連線#1
Begin;
update t1 set f2=0 where f1=1;
連線#2
update t1 set f2=10 where f1=2;
因為連線#1是鎖f1=1的資料，這樣連線#2針對f1=2就不會被鎖定。
如下圖
0 留言