在資料函式庫系統中，有效管理平行存取和確保資料一致性至關重要。MySQL 提供了多種機制來實作這些目標，例如讀寫鎖、交易和隔離層級。讀寫鎖允許多個客戶端同時讀取資料，但只允許一個客戶端寫入，確保資料完整性。交易則將一組操作視為單一工作單元，保證操作的原子性和一致性。MySQL 支援四種隔離層級，從 READ UNCOMMITTED 到 SERIALIZABLE，提供不同程度的隔離性和平行性。此外，InnoDB 儲存引擎的多版本平行控制（MVCC）機制能有效提升讀取效能，減少鎖定衝突。MySQL 的複製技術則允許將資料從主節點複製到副本文點，提高資料可用性和容錯能力。
平行控制：讀寫鎖的原理與應用
在處理電子試算表或資料函式庫時，資料的平行存取是一個常見的問題。當多個客戶端同時讀取或寫入資料時，如何確保資料的一致性和完整性就變得非常重要。本篇文章將探討平行控制的基本概念，特別是在MySQL中的實作。
讀寫鎖的基本原理
在多個客戶端同時存取資料的情況下，為了避免資料損壞或不一致，需要實施鎖定機制。鎖定機制通常包含兩種型別：分享鎖（Shared Locks）和排他鎖（Exclusive Locks），也稱為讀鎖（Read Locks）和寫鎖（Write Locks）。
- 讀鎖（Read Locks）： 多個客戶端可以同時對同一資源加讀鎖，不會互相干擾。這意味著多個客戶端可以同時讀取同一資源。
- 寫鎖（Write Locks）： 當一個客戶端對資源加寫鎖時，其他客戶端無法對該資源加讀鎖或寫鎖，直到寫鎖被釋放。這確保了在寫入操作進行時，不會有其他客戶端幹擾。
鎖粒度的重要性
為了提高平行性，鎖定的粒度非常重要。鎖定粒度越細，允許的平行操作就越多。例如，行級鎖定（Row-Level Locking）允許不同的客戶端同時修改同一表格中的不同行，從而提高平行性。
然而，鎖定操作本身是有開銷的。過多的鎖定操作會導致效能下降。因此，資料函式倉管理系統需要在鎖定開銷和資料安全性之間取得平衡。
MySQL中的鎖定策略
MySQL提供了多種儲存引擎，每種引擎都可以實作自己的鎖定策略和粒度。主要包括以下兩種：
表級鎖定（Table Locks）
表級鎖定是最基本的鎖定策略，它鎖定整個表格。當客戶端需要寫入表格時，會取得寫鎖，阻止其他客戶端進行讀寫操作。表級鎖定的開銷較低，但平行性較差。
行級鎖定（Row Locks）
行級鎖定提供了更高的平行性，但開銷較大。它允許不同的客戶端同時修改同一表格中的不同行。行級鎖定需要更複雜的管理機制來追蹤鎖定的狀態。
此圖示展示了讀寫鎖的基本原理
內容解密：
此圖示展示了讀寫鎖的基本原理。當資源被加上讀鎖時，多個客戶端可以同時讀取該資源。然而，當某個客戶端對資源加上寫鎖時，其他客戶端的讀寫操作都會被阻塞，直到寫鎖被釋放。這種機制確保了資料的一致性和完整性。
MySQL 交易處理與隔離層級
在資料函式庫系統中，交易（Transaction）扮演著至關重要的角色，尤其是在需要確保資料一致性和完整性的應用場景中。MySQL 作為一個廣泛使用的資料函式庫系統，提供了對交易的支援，並且透過不同的隔離層級（Isolation Levels）來控制交易之間的幹擾。
交易的概念
交易是一組被視為單一、不可分割的工作單元的 SQL 陳述式。如果資料函式庫引擎能夠將整個交易應用到資料函式庫，那麼它就會這樣做；但如果由於某種原因（例如系統當機）導致交易中的任何一個陳述式無法執行，那麼整個交易都不會被應用。這就是所謂的「全有或全無」（all or nothing）。
以銀行應用程式為例，當需要將客戶的支票帳戶中的資金轉移到儲蓄帳戶時，至少需要執行三個步驟：檢查支票帳戶餘額是否足夠、扣除支票帳戶中的資金、將資金新增到儲蓄帳戶中。整個操作應該被包裹在一個交易中，以確保如果任何一步驟失敗，已經完成的步驟可以被回復。
ACID 特性
單純的交易還不足以確保資料的安全性。系統必須透過 ACID 測試，這代表了原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability）。
- 原子性：交易必須作為單一、不可分割的工作單元運作，要麼整個交易被應用，要麼整個交易不被提交。
- 一致性：資料函式庫應該始終從一個一致的狀態轉移到下一個一致的狀態。
- 隔離性：交易的結果通常對其他交易是不可見的，直到該交易完成。
- 永續性：一旦交易被提交，其變更是永久性的。
隔離層級
ANSI SQL 標準定義了四種隔離層級，分別是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。每種隔離層級都對交易的隔離性提供了不同程度的保證。
-- 示範不同的隔離層級下，交易的行為
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
-- 在此隔離層級下，其他交易的變更在提交後對本交易可見
READ UNCOMMITTED
在 READ UNCOMMITTED 層級下，交易可以讀取未提交的交易結果。這種層級可能會導致許多問題，例如「髒讀」（dirty read）。
READ COMMITTED
READ COMMITTED 是大多數資料函式庫系統的預設隔離層級。它保證交易只能看到在其開始後提交的交易所做的變更。然而，它仍然允許「不可重複讀」（nonrepeatable read）的情況發生。
REPEATABLE READ
REPEATABLE READ 解決了 READ UNCOMMITTED 層級下的一些問題，保證了在同一交易中，多次讀取相同的資料時，結果保持一致。但是，它理論上仍然允許「幻讀」（phantom read）的發生。MySQL 的 InnoDB 儲存引擎透過 Next-Key Locking 機制，有效地避免了幻讀。
-- 使用 REPEATABLE READ 隔離層級
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- 即使其他交易插入了新的符合條件的記錄，本次交易中再次執行相同的查詢，結果仍保持一致
MySQL 交易隔離級別與死鎖處理
MySQL 的交易（Transaction）機制是確保資料函式庫操作一致性與完整性的重要功能。在多使用者環境下，交易隔離級別（Isolation Level）與死鎖（Deadlock）處理是影響資料函式庫效能與資料正確性的關鍵因素。
交易隔離級別
MySQL 支援四種交易隔離級別，分別為 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。這些級別決定了交易在執行過程中如何處理平行存取的資料。
1. READ UNCOMMITTED
此級別允許交易讀取其他交易尚未提交的變更，可能導致 髒讀（Dirty Read）、不可重複讀（Nonrepeatable Read） 和 幻讀（Phantom Read） 的問題。
2. READ COMMITTED
此級別確保交易只能讀取已提交的資料，避免了髒讀，但仍可能出現不可重複讀和幻讀的問題。
3. REPEATABLE READ
MySQL 的預設隔離級別。此級別確保同一交易中的多次讀取結果一致，避免了髒讀和不可重複讀，但仍可能出現幻讀。InnoDB 透過多版本並發控制（MVCC）解決了幻讀問題。
4. SERIALIZABLE
最高隔離級別，透過強制交易依序執行，避免所有並發問題。但此級別會導致大量的鎖定與逾時問題，嚴重影響並發效能。
| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 | 鎖定讀取 | |
|
|
-|
|
| | READ UNCOMMITTED | 是 | 是 | 是 | 否 | | READ COMMITTED | 否 | 是 | 是 | 否 | | REPEATABLE READ | 否 | 否 | 是 | 否 | | SERIALIZABLE | 否 | 否 | 否 | 是 |
死鎖問題與處理
當兩個或多個交易互相持有並請求相同資源的鎖定時，便會發生死鎖。例如：
交易 1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 AND date = '2020-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 AND date = '2020-05-02';
COMMIT;
交易 2
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 AND date = '2020-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 AND date = '2020-05-01';
COMMIT;
此例中，交易 1 和交易 2 將陷入死鎖狀態。InnoDB 能夠偵測死鎖並立即回復其中一個交易，以解除死鎖。
死鎖處理機制
- InnoDB 偵測到死鎖後，會回復持有最少獨佔鎖的交易。
- 部分儲存引擎可能因特定操作順序而發生死鎖。
- 應用程式應設計重試機制，以處理死鎖導致的交易失敗。
交易日誌與效能最佳化
MySQL 的交易日誌（Transaction Log）機制提升了交易的處理效率。儲存引擎先將變更寫入記憶體，並將操作記錄到交易日誌中，再非同步更新磁碟資料。這種 預寫日誌（Write-Ahead Logging, WAL） 機制減少了隨機 I/O，提升效能。
若系統當機，儲存引擎可透過交易日誌還原未完成的變更，確保資料的一致性。
MySQL 中的交易控制
AUTOCOMMIT 模式
MySQL 預設啟用 AUTOCOMMIT，每個單獨的 SQL 操作視為一個交易。若關閉 AUTOCOMMIT，則需手動執行 COMMIT 或 ROLLBACK 提交或回復交易。
設定隔離級別
可透過 SET TRANSACTION ISOLATION LEVEL 命令設定當前交易的隔離級別，例如：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
建議在伺服器層級設定常用的隔離級別，並在特定情況下調整。
MySQL 交易管理與鎖定機制
MySQL 的交易管理與鎖定機制是確保資料函式庫一致性與平行存取的重要功能。本章將探討 MySQL 的鎖定機制、多版本平行控制（MVCC）及其對交易管理的影響。
鎖定提示與交易隔離級別
MySQL 提供了多種鎖定提示（locking hints）來控制交易的平行行為，但這些提示經常被濫用，通常建議避免使用。MySQL 8.0 引入了 SELECT...FOR SHARE 取代了舊版的 SELECT...LOCK IN SHARE MODE。
混合儲存引擎的交易風險
MySQL 不在伺服器層級管理交易，而是由底層的儲存引擎自行實作交易。這意味著在單一交易中混合使用不同的儲存引擎可能會導致不可預期的結果。當混合使用交易型和非交易型表格（如 InnoDB 和 MyISAM）時，如果發生回復，非交易型表格的變更無法被復原，可能導致資料函式庫處於不一致的狀態。
風險示範
- 在交易中混合使用 InnoDB 和 MyISAM 表格。
- 發生回復時，InnoDB 的變更被復原，但 MyISAM 的變更仍然存在。
隱式與顯式鎖定
InnoDB 使用兩階段鎖定協定，可以在交易的任何階段取得鎖，但在 COMMIT 或 ROLLBACK 時才釋放鎖。InnoDB 支援顯式鎖定，如 SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE，但通常建議使用隱式鎖定。
顯式鎖定的使用場景
- 需要在交易中對特定資料列進行獨佔存取。
- 需要確保資料的一致性與完整性。
多版本平行控制（MVCC）
MySQL 的交易型儲存引擎（如 InnoDB）使用多版本平行控制（MVCC）來提高平行性。MVCC 透過維護資料的多個版本，允許非鎖定讀取和寫入操作同時進行，從而提高系統的吞吐量。
MVCC 的工作原理
- 為每個交易分配一個交易 ID。
- 當交易修改資料時，將變更記錄到 undo log 中。
- 當其他交易讀取資料時，根據其可見性規則選擇合適的資料版本。
InnoDB 的 MVCC 實作
InnoDB 透過為每個交易分配一個唯一的 ID，並使用 undo log 來記錄資料的變更。當其他交易讀取資料時，InnoDB 會根據其可見性規則選擇合適的資料版本。
MVCC 的優缺點
- 優點：提高平行性，減少鎖定爭用。
- 缺點：增加儲存引擎的複雜度，可能導致額外的效能開銷。
MySQL 多版本平行控制（MVCC）與架構解析
MySQL 的多版本平行控制（MVCC）是一種用於提升資料函式庫平行存取效率的技術，但其實作並無統一的正式標準，不同的資料函式庫引擎和系統有各自的實作方式。MVCC 僅支援 REPEATABLE READ 和 READ COMMITTED 隔離級別。
MVCC 的限制
READ UNCOMMITTED不支援 MVCC，因為查詢不會讀取適合其交易版本的資料列版本，而是讀取最新的版本。SERIALIZABLE也不支援 MVCC，因為讀取會鎖定所有傳回的資料列。
MySQL 複製技術
MySQL 設計為在任意時間點只允許一個節點接受寫入操作，這雖然有利於管理一致性，但在需要將資料寫入多個伺服器或多個位置時會面臨取捨。MySQL 提供了一種原生方式來將一個節點上的寫入操作分發到其他節點，稱為複製（Replication）。在 MySQL 中，源節點為每個副本文點建立一個執行緒，該執行緒以複製使用者的身份登入，並在寫入操作發生時喚醒，將新資料傳送給副本。
複製拓撲結構
下圖展示了一個簡單的 MySQL 伺服器複製拓撲結構，通常稱為源-副本設定的拓撲樹。
此圖示
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title MySQL 平行控制與交易管理機制解析
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml
對於任何生產環境中的資料，都應該使用複製技術，並至少設定三個以上的副本，理想情況下將它們分散在不同的地理位置（在雲託管環境中稱為區域），以實作災難還原規劃。
資料檔案結構
在 MySQL 8.0 版本中，表格元資料被重新設計為資料字典，並與表格的 .ibd 檔案一起儲存。這使得表格結構資訊支援事務處理和原子性資料定義變更。除了依靠 information_schema 檢索表格定義和元資料外，還引入了字典物件快取，這是一種根據 LRU（最近最少使用）的記憶體快取，用於儲存分割區定義、表格定義、儲存程式定義、字元集和校對規則資訊。
資料檔案變更
.ibd和.frm檔案被序列化的字典資訊（.sdi）所取代，每個表格一個檔案。
InnoDB 儲存引擎
InnoDB 是 MySQL 的預設事務處理儲存引擎，也是最重要和最廣泛使用的引擎。它被設計用於處理許多短暫的事務，這些事務通常會完成而不是被回復。其效能和自動當機還原功能使其也適用於非事務性儲存需求。
InnoDB 的特點
- 使用 MVCC 實作高平行性。
- 實作了所有四種 SQL 標準隔離級別，預設為
REPEATABLE READ。 - 使用聚集索引構建表格，提供快速的主鍵查詢。
- 包含多種內部最佳化，如預讀、適應性雜湊索引和插入緩衝區。
程式碼範例：建立 InnoDB 表格
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
) ENGINE=InnoDB;
內容解密：
CREATE TABLE users：建立一個名為users的新表格。id INT AUTO_INCREMENT PRIMARY KEY：定義一個自動遞增的主鍵id。name VARCHAR(255) NOT NULL：定義一個不允許為空的名字欄位。email VARCHAR(255) UNIQUE NOT NULL：定義一個不允許為空且必須唯一的電子郵件欄位。ENGINE=InnoDB：指定使用 InnoDB 儲存引擎。
JSON 檔案支援
MySQL 5.7 版本開始，InnoDB 支援 JSON 資料型別，提供自動驗證 JSON 檔案以及最佳化的儲存，以實作快速讀取存取。MySQL 8.0.7 版本進一步新增了在 JSON 陣列上定義多值索引的功能，這可以顯著提高對 JSON 型別的查詢效能。
JSON 資料處理範例
CREATE TABLE documents (
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON
) ENGINE=InnoDB;
INSERT INTO documents (data) VALUES ('{"name": "John", "age": 30}');
SELECT * FROM documents WHERE JSON_EXTRACT(data, '$.name') = 'John';
內容解密：
CREATE TABLE documents：建立一個名為documents的表格用於儲存 JSON 資料。data JSON：定義一個 JSON 型別的欄位data。INSERT INTO documents (data) VALUES ('{"name": "John", "age": 30}')：插入一條包含 JSON 資料的記錄。SELECT * FROM documents WHERE JSON_EXTRACT(data, '$.name') = 'John'：查詢data中name為 ‘John’ 的記錄。