高性能的MySQL（1）鎖和MVCC

一、MySQL邏輯架構

用戶端

連結/線程處理

查詢緩存

解析器

優化器

存儲引擎

二、鎖

1、讀寫鎖

讀鎖是共享的，是互相不阻塞的，多個客戶在同一時間讀取同一資源，互補幹擾。寫鎖是排他的，會阻塞其他的寫鎖和讀鎖，寫鎖有更高的優先級~

一種提高共享資源并發性的方式就是讓鎖定對象更有選擇性。盡量隻鎖定需要修改的部分資料，而不是所有的資源，鎖定的資源越少，系統的并發性更好。

表鎖（table lock）

表鎖是鎖開銷最小的政策，會鎖定整張表，一個使用者對表進行寫操作前，需要先獲得寫鎖，這會阻塞其他使用者對表的所有讀寫操作。舉例：有如下一張表

1

2

3

4

5

6

7

8

9

10

11

12

<code>CREATE</code> <code>TABLE</code> <code>`emp2` (</code>

<code>  </code><code>`id` </code><code>int</code><code>(11) </code><code>NOT</code> <code>NULL</code> <code>DEFAULT</code> <code>'0'</code><code>,</code>

<code>  </code><code>`</code><code>name</code><code>` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num1` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num2` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`num3` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`job_num` </code><code>int</code><code>(10) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`d` </code><code>date</code> <code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>PRIMARY</code> <code>KEY</code> <code>(`id`),</code>

<code>  </code><code>KEY</code> <code>`job_num` (`job_num`)</code>

<code>) ENGINE=MyISAM </code><code>DEFAULT</code> <code>CHARSET=utf8 ;</code>

我們在一個終端中加上讀表鎖

<a href="http://blog.51cto.com/attachment/201310/105606537.png" target="_blank"></a>

然後打開另一個終端，可以讀但不可以寫

<a href="http://blog.51cto.com/attachment/201310/105742614.png" target="_blank"></a>

直到第一個連結釋放鎖之後，才能修改成功。

<a href="http://blog.51cto.com/attachment/201310/110043513.png" target="_blank"></a>

<a href="http://blog.51cto.com/attachment/201310/110119735.png" target="_blank"></a>

行級鎖：

行鎖可以最大程度的支援并發，但是鎖的開銷也最大，InnoDB支援行級鎖，在存儲引擎層實作。

死鎖：

兩個或者多個事務在同一資源上互相占用，并請求鎖定對方占用的資源時，導緻産生死鎖，比如有如下2個事務

<code>#事務1</code>

<code>start </code><code>transaction</code><code>;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=45 </code><code>where</code> <code>id = 4;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=54 </code><code>where</code> <code>id = 3;</code>

<code>commit</code><code>;</code>

<code>#事務2</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=12 </code><code>where</code> <code>id = 3;</code>

<code>update</code> <code>tab </code><code>set</code> <code>col=18 </code><code>where</code> <code>id = 4;</code>

如果巧合，兩個事務都執行了第一條，更新了一行，同時鎖定了該行資料，接着都嘗試去執行第二條，卻發現被對方鎖定，然後2個事務都等待對方釋放鎖，則陷入死循環。為了解決這問題，資料庫系統實作了各種死鎖檢測和死鎖逾時機制。

2、隐式和顯式鎖定

隐式：InnoDB，在執行事務過程中會自動加鎖，除非執行COMMIT或ROLLBACK時鎖會自動釋放。

顯式：可以使用SELECT...LOCK IN SHARE MODE || SELECT...FOR UPDATE生成鎖

三、InnoDB的MVCC（多版本并發控制）

通過在每行記錄的最後儲存2個隐藏的列來實作。一個儲存行的建立時間，一個儲存行的過期時間，其實存的系統版本号。每開始一個新的事務，系統版本号都會遞增。事務開始時刻的系統版本号作為事務的版本号，用來和查詢到的每行記錄的版本号來比較。

SELECT：

InnoDB會根據以下2個條件檢查每行記錄：

a、隻查找版本早于目前事務版本的資料行，也就是行的系統版本号小于或等于事務的系統版本号，這樣可以確定事務讀取的行，要麼是在事務開始前已經存在的，要麼是事務自身插入或修改的。

b、行的删除版本要麼未定義，要麼大于目前事務版本号。這樣可以確定事務讀取到的行在事務開始之前未被删除。

INSERT：

InnoDB為新插入的每一行儲存目前系統版本号為行版本号

DELETE：

InnoDB為删除的每一行儲存目前系統版本号作為行的删除标示。

UPDATE：

InnoDB為插入一行新紀錄，儲存目前系統版本号作為行版本号，同時儲存目前系統版本号到原來的行作為行删除标示。

儲存這2個額外的系統版本号，使大多數讀操作都可以不用枷鎖。這樣資料庫操作簡單，性能也會很好，并且保證隻會讀到标準的行。但是需要額外的空間和更多的檢查工作。

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1306411，如需轉載請自行聯系原作者