mysql的儲存引擎與鎖

標籤：

一、背景知識

1、鎖是電腦協調多個進程或線程並發訪問某一資源的機制。

A、鎖分類。

| 共用鎖定（讀鎖）：在鎖定期間，多個使用者可以讀取同一個資源，讀取過程中資料不會發生變化。

| 獨佔鎖定（寫鎖）：在鎖定期間，只允許一個使用者寫入資料，其它使用者的讀取，寫入等操作都會被拒絕。

B、鎖顆粒

| 表鎖：開銷小，加鎖快；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高,並發度最低。

| 行鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。

| 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死結；鎖定粒度界於表鎖和行鎖之間，並發度一般。

2、事務是由一組SQL語句組成的邏輯處理單元。

A、事務（Transaction）及其ACID屬性。

| 原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。

|   一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；事務結束時，所有的內部資料結構（如B樹索引或雙向鏈表）也都必須是正確的。

| 隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並行作業影響的“獨立”環境執行。這意味著交易處理過程中的中間狀態對外部是不可見的，反之亦然。

| 持久性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

銀行轉帳就是事務的一個典型例子。

B、事務並發問題

| 更新丟失（Lost Update）：兩個或多個事務同時更新一個資源，前面的事務的操作結果會被最後面的事務的操作結果覆蓋。

| 髒讀（Dirty Reads）：事務A正在更新記錄X，事務B讀取了記錄X，並藉此做進一步處理，事務A更新完畢記錄X，此時資料庫中的記錄X與事務B讀到的記錄X並不是一致的。那麼事務B讀取到的就是“髒資料”，此類現象稱之為“髒讀”。

| 不可重複讀取（Non-Repeatable Reads）：事務A讀取了記錄X，然後事務B更新了記錄X，然後事務A再次讀取記錄X。此類情況下，事務A讀取的記錄X並不一定是相同的。

| 幻讀（Phantom Reads）：事務Ａ按相同的查詢條件讀取已經讀取過的記錄Ｘ時，發現，事務Ｂ更新的記錄Ｙ也符合事務Ａ的查詢條件。那麼，事務A將會讀取到記錄Ｙ，而不是記錄Ｘ。此類現象稱之為“幻讀”。

Ｃ、事務隔離解決事務並發問題

隔離等級	說明	髒讀	不可重複讀取	幻讀
Read Uncommitted（讀取未提交內容）	所有事務都可以看到其他未提交事務的執行結果。	是	是	是
Read Committed（讀取提交內容）	一個事務只能看見已經提交事務所做的改變。	否	是	是
Repeatable Read（可重讀）	這是MySQL的預設交易隔離等級，它確保同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行。	否	否	是
Serializable（可序列化）	它是在每個讀的資料行上加上共用鎖定。在這個層級，可能導致大量的逾時現象和鎖競爭。	否	否	否

二、各個儲存引擎的特點

特點	MyISAM	InnoDB	Memory	Archive
儲存限制	256TB	64TB	有	無
事務安全	-	支援	-	-
支援索引	支援	支援	支援
鎖顆粒	表鎖	行鎖	表鎖	行鎖
資料壓縮	支援	-	-	支援
支援外鍵	-	支援	-	-

三、MyISAM的鎖詳解

在我的test資料庫中，有兩張MyISAM儲存引擎的表分別是User與Log。

下面將示範兩個線程（A、B）同時操作一張表的情況，開兩個ｃｍｄ視窗一個代表Ａ一個代表Ｂ按順序執行下面的代碼。

操作	說明	線程
mysql> show tables; +----------------+ | Tables_in_test   | +----------------+ | log                   | | user                 | +----------------+ 2 rows in set	顯示所有的表	A
mysql> show status like ‘table%‘; +-----------------------+-------+ | Variable_name            | Value   | +-----------------------+-------+ | Table_locks_immediate | 50      | | Table_locks_waited      | 0        | +-----------------------+-------+ 2 rows in set (0.00 sec)	顯示表級鎖爭用情況	A
mysql> lock table log write local; Query OK, 0 rows affected	給表log顯式加上寫鎖，“local”選項的作用是在滿足MyISAM表並發插入條件的情況下，允許其它線程在表尾並發插入記錄。	A
mysql> select * from log;	線程Ｂ讀取已經被線程Ａ上了讀鎖的表ｌｏｇ，可以看到線程Ｂ正在等待。	Ｂ
mysql> select * from user; 1100 - Table ‘user‘ was not locked with LOCK TABLES	線程Ａ讀取了沒有被加鎖的表Ｕｓｅｒ，可以看到，ｍｙｓｑｌ不讓讀取。	Ａ
mysql> unlock tables; Query OK, 0 rows affected	線程A釋放了對log表的鎖。	A
mysql> show status like ‘table%‘; +-----------------------+-------+ | Variable_name            | Value   | +-----------------------+-------+ | Table_locks_immediate | 51      | | Table_locks_waited      | 0        | +-----------------------+-------+ 2 rows in set (0.00 sec)	顯示表級鎖爭用情況，可以看到Table_locks_immediate的值加1了。	A
+--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   2       | 呵呵       |          10      | |      2 |   2       | 哈哈       |          20      | +--------+-----+---------+-------------+ 2 rows in set (5 min 47.34 sec)	線程B讀取到了log的資料。	B

1、總結

| MyISAM，在預設情況下會自動加鎖，並不需要顯式加鎖。
| MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死結（Deadlock Free）的原因。其它未加鎖的表，並不允許操作。
| MyISAM加的是表級鎖。
| 當線程Ａ與線程Ｂ同時要給某個表上讀鎖與寫鎖的時候，MyISAM預設讓線程Ｂ先上寫鎖。大量的讀與寫操作並存的時候，寫操作可能會一直得到寫鎖，導致讀操作處於阻塞狀態。

2、並發插入調度

MyISAM儲存引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
| 當concurrent_insert設定為0時，不允許並發插入。
| 當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的預設設定。
| 當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。

3、MyISAM的鎖調度

| 通過指定啟動參數 low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
| 通過執行命令SET LOW_PRIORITY_UPDATES=1，使該串連發出的更新要求優先順序降低。
| 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
| 給系統變數max_write_lock_count設定一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先順序降低，給讀操作一定獲得鎖的機會。

 四、InnoDB的鎖詳解

在我的test資料庫中，有兩張InnoDB儲存引擎的表分別是User與Log。

下面將示範兩個線程（A、B）同時操作一張表的情況，開兩個ｃｍｄ視窗一個代表Ａ一個代表Ｂ按順序執行下面的代碼。

第一個表格是，獨佔鎖定的執行個體。

操作	說明	線程
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | log                 | | user               | +----------------+ 2 rows in set	顯示所有的表	A
mysql> select * from log; +--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   1 | 呵呵         |           0     | |      2 |   2 | 哈哈         |           0     | +--------+-----+---------+-------------+ 2 rows in set	顯示log表的資訊	A
mysql> show status like ‘innodb_row_lock%‘; +-------------------------------+----------+ | Variable_name                       | Value       | +-------------------------------+----------+ | Innodb_row_lock_current_waits | 0          | | Innodb_row_lock_time              | 185770 | | Innodb_row_lock_time_avg       | 26538   | | Innodb_row_lock_time_max      | 51620   | | Innodb_row_lock_waits             | 7          | +-------------------------------+----------+ 5 rows in set (0.00 sec)	查看InnoDB的鎖爭用情況	B
mysql> start transaction; Query OK, 0 rows affected	開啟事務	A
mysql> update log set create_time=1 where uid=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0	更新uid=1的資料 Select…For Update 語句可以在讀取資料的時候就鎖定它。	A
mysql> update log set create_time=2 where uid=2;	更新uid=2的資料，可以看線程B在等待，因為此時InnoDB加的是表級獨佔鎖定。	B
mysql> commit; Query OK, 0 rows affected	提交事務	A
mysql> update log set create_time=2 where uid=2; Query OK, 1 row affected (9.69 sec) Rows matched: 1  Changed: 1  Warnings: 0	線程B執行update成功	B
mysql> show status like ‘innodb_row_lock%‘; +--------------------------------+---------+ | Variable_name                         | Value     | +--------------------------------+---------+ | Innodb_row_lock_current_waits | 0          | | Innodb_row_lock_time              | 196980 | | Innodb_row_lock_time_avg       | 24622   | | Innodb_row_lock_time_max      | 51620   | | Innodb_row_lock_waits             | 8          | +--------------------------------+---------+ 5 rows in set (0.00 sec)	再次查看InnoDB的鎖爭用情況 。 如　InnoDB_row_lock_waits　和InnoDB_row_lock_time_avg　的值比較高，則鎖的爭用情況嚴重。	B
mysql> select * from log; +--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   1 | 呵呵          |           1    | |      2 |   2 | 哈哈          |           2    | +--------+-----+---------+-------------+ 2 rows in set (0.00 sec)	顯示log表的資訊	B

 第二個表格是，共用鎖定的執行個體。

操作	說明	線程
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)	開啟事務	A
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)	開啟事務	B
mysql> select * from log where uid=1 lock in share mode; +--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   1 | 呵呵    |           1 | +--------+-----+---------+-------------+ 1 row in set (0.00 sec)	線程Ａ取得共用鎖定	Ａ
mysql> select * from log where uid=1 lock in share mode; +--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   1 | 呵呵    |           1 | +--------+-----+---------+-------------+ 1 row in set (0.00 sec)	線程Ｂ取得共用鎖定	Ｂ
mysql> update log set create_time=1 where uid=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	線程Ａ更新一條記錄，暫時不要去操作下面的步驟，直到出現報錯。 由於線程Ｂ的共用鎖定，導致了線程A無法更新資料。	A
mysql> update log set create_time=1 where uid=1; Query OK, 0 rows affected (7.82 sec) Rows matched: 1  Changed: 0  Warnings: 0	線程Ａ再次更新記錄，同時線上程A還在等待的時候，線程B也執行了更新記錄。	A
mysql> update log set create_time=1 where uid=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction	線程B執行更新記錄。注意，當線程B執行更新語句的時候，線程B失去了共用鎖定，線程A獲得了獨佔鎖定，導致線程B的語句立刻出現了報錯。	B
mysql> select * from log where uid=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	線程Ｂ再次執行擷取共用鎖定的語句，可以看到，由於線程Ａ已經取得了獨佔鎖定，導致線程Ｂ爭鎖失敗。 如果線程Ａ執行ｃｏｍｍｉｔ語句釋放獨佔鎖定後，線程Ｂ則可以立馬獲得共用鎖定。	B

總結：

InnoDB行鎖是通過給索引上的索引項目加鎖來實現的，無索引的情況下，InnoDB將使用表鎖！實際應用中，要特別注意此特性。避免大量鎖衝突影響並發效能。

mysql的儲存引擎與鎖