高效能MySql學習筆記——鎖、事務、隔離等級

文章目錄

• 從對資料操作的類型（讀\寫）分
• 從鎖定的資料範圍分

為什麼需要鎖？

因為資料庫要解決並發控制問題。在同一時刻，可能會有多個用戶端對Table1.rown進行操作，比如有的在讀取該行資料，其他的嘗試去刪除它。為了保證資料的一致性，資料庫就要對這種並行作業進行控制，因此就有了鎖的概念。

鎖的分類從對資料操作的類型（讀\寫）分

讀鎖（共用鎖定）：針對同一塊資料，多個讀操作可以同時進行而不會互相影響。

寫鎖（獨佔鎖定）：噹噹前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

從鎖定的資料範圍分

表鎖

行鎖

為了儘可能提高資料庫的並發度，每次鎖定的資料範圍越小越好，理論上每次只鎖定當前操作的資料的方案會得到最大的並發度，但是管理鎖是很耗資源的事情（涉及擷取，檢查，釋放鎖等動作），因此資料庫系統需要在高並發響應和系統效能兩方面進行平衡，這樣就產生了“鎖粒度（Lock granularity）”的概念

鎖粒度（Lock granularity）

表鎖：管理鎖的開銷最小，同時允許的並發量也最小的鎖機制。MyIsam儲存引擎使用的鎖機制。當要寫入資料時，把整個表都鎖上，此時其他讀、寫動作一律等待。在MySql中，除了MyIsam儲存引擎使用這種鎖策略外，MySql本身也使用表鎖來執行某些特定動作，比如alter table.

行鎖：可以支援最大並發的鎖策略。InnoDB和Falcon兩張儲存引擎都採用這種策略。

MySql是一種開放的架構，你可以實現自己的儲存引擎，並實現自己的鎖粒度策略，不像Oracle，你沒有機會改變鎖策略，Oracle採用的是行鎖。

事務（Transaction）

從業務角度出發，對資料庫的一組操作要求保持4個特徵：

Atomicity：原子性

Consistency：一致性，

Isolation：隔離性

Durability：持久性

為了更好地理解ACID，以銀行賬戶轉賬為例：

1 START TRANSACTION;

2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;

原子性：要麼完全提交（10233276的checking餘額減少200，savings 的餘額增加200），要麼完全復原（兩個表的餘額都不發生變化）

一致性：這個例子的一致性體現在 200元不會因為資料庫系統運行到第3行之後，第4行之前時崩潰而不翼而飛，因為事物還沒有提交。

隔離性：允許在一個事務中的動作陳述式會與其他事務的語句隔離開，比如事務A運行到第3行之後，第4行之前，此時事務B去查詢checking餘額時，它仍然能夠看到在事務A中被減去的200元，因為事務A和B是彼此隔離的。在事務A提交之前，事務B觀察不到資料的改變。

持久性：這個很好理解。

事務跟鎖一樣都會需要大量工作，因此你可以根據你自己的需要來決定是否需要事務支援，從而選擇不同的儲存引擎。

隔離等級（Isolation Level）

 SQL標準定義了4類隔離等級，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低層級的隔離級一般支援更高的並發處理，並擁有更低的系統開銷。
Read Uncommitted（讀取未提交內容）

       在該隔離等級，所有事務都可以看到其他未提交事務的執行結果。本隔離等級很少用於實際應用，因為它的效能也不比其他層級好多少。讀取未提交的資料，也被稱之為髒讀（Dirty Read）。
Read Committed（讀取提交內容）

       這是大多數資料庫系統的預設隔離等級（但不是MySQL預設的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離等級 也支援所謂的不可重複讀取（Nonrepeatable Read），因為同一事務的其他執行個體在該執行個體處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）

       這是MySQL的預設交易隔離等級，它確保同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。簡單的說，幻讀指當使用者讀取某一範圍的資料行時，另一個事務又在該範圍插入入了新行，當使用者再讀取該範圍的資料行時，會發現有新的“幻影” 行。InnoDB和Falcon儲存引擎通過多版本並發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。

Serializable（可序列化） 
這是最高的隔離等級，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，它是在每個讀的資料行上加上共用鎖定。在這個層級，可能導致大量的逾時現象和鎖競爭。

         這四種隔離等級採取不同的鎖類型來實現，若讀取的是同一個資料的話，就容易發生問題。例如：

         髒讀(Drity Read)：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正確的。

         不可重複讀取(Non-repeatable read):在一個事務的兩次查詢之中資料不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。

         幻讀(Phantom Read):在一個事務的兩次查詢中資料筆數不一致，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的。

         在MySQL中，實現了這四種隔離等級，分別有可能產生問題如下所示：