細聊MySQL的Innodb儲存引擎（二）

標籤：mysql   innodb   事務   acid   交易隔離等級   

細聊MySQL的Innodb儲存引擎（一）

        上一篇主要和大家探討了下Innodb的鎖機制與隔離機制。本篇來和大家一起研究下在使用Innodb是會出現的問題以及如何解決它們。

Innodb是如何解決幻讀問題的

什麼是幻讀？聽起來似乎很高端，但實際上它只是反映了事務中的一種資料不一致的情況。下面看我來描述這樣一個情境，通過這個情境，大家就能很清楚的知道幻讀到底是什麼意思。

開啟兩個用戶端，設為A和B

A用戶端

mysql> start transaction; （步驟一）

Query OK, 0 rows affected (0.00 sec)

mysql> select * from b; （步驟二）

+----+------+

| id | name |

+----+------+

| 10 | abd  |

| 99 | NULL |

|  1 | wang |

|  7 | eeee |

|  2 | wei  |

|  3 | ak47 |

|  9 | ffff |

+----+------+

7 rows in set (0.00 sec)

mysql> mysql> select * from b;（步驟六）

+----+------+

| id | name |

+----+------+

| 10 | abd  |

| 99 | NULL |

|  1 | wang |

|  7 | eeee |

|  2 | wei  |

|  3 | ak47 |

|  9 | ffff |

+----+------+

7 rows in set (0.00 sec)

mysql> insert into b (id,name) values (100,‘abc’); （步驟七）

ERROR 1062 (23000): Duplicate entry ‘100‘ for key ‘PRIMARY‘

B用戶端

mysql> start transaction; （步驟三）

Query OK, 0 rows affected (0.00 sec)

mysql> insert into b (id,name) values (100,‘abc’); （步驟四）

Query OK, 1 row affected (0.00 sec)

mysql> commit; （步驟五）

Query OK, 0 rows affected (0.01 sec)

＝。＝ what? 明明步驟六查詢結果沒有值為100的id，為啥插入時提示重複key呢？這個值為100的id在事務A中“憑空”的產生了，這種現象就稱之為幻讀。

由於REPEATABLE-READ隔離等級是參照第一次查詢的時間點快照來保持一致性讀的，所以當事務B提交插入資料後，事務A仍然顯示舊版本的結果集來保持資料的一致性。而這樣恰好就成為了引起幻讀的原因。

那麼，如何解決幻讀問題呢？要解決幻讀問題，需要在查詢事務中顯示添加鎖，如使用select * from b for update聲明來擷取最新的資料。但這樣就與一致性讀的性質相矛盾了。所以具體問題使用什麼辦法解決要具體分析。

死結的偵測與復原

Innodb能自動偵測事務死結，當偵測到死結時，Innodb能自動復原小事務。事務的大小由插入、更新或刪除的行數決定。

如果沒有設定Innodb_table_locks參數或者將事務設定成自動，那麼Innodb將不能偵測死結。如果MySQL使用LOCK TABLES聲明或使用其它的儲存引擎加鎖，MySQL也不能偵測死結。

當Innodb執行一個交易回復後，所有被該事務設定的鎖都將被釋放。如果一個SQL聲明執行後返回錯誤。那麼被該聲明設定的鎖將不被釋放。

如何避免死結

1、如果頻繁的出現死結警告，可以開啟innodb_print_all_deadlocks選項，查看在error log裡的關於死結的詳細資料。

2、盡量使用小事務，這樣可以減少衝突的機率。

3、如果你使用SELECT...FOR UPDATE類似聲明，盡量使用更低的隔離等級，比如READ-COMMITTED。

4、掌握好事務內的操作順序，這樣可以有效防止死結。

5、最佳化索引，這樣在查詢時能掃描更少的索引記錄，對更少的索引加鎖。

本文出自 “架構師之路” 部落格，請務必保留此出處http://wangweiak47.blog.51cto.com/2337362/1591765

細聊MySQL的Innodb儲存引擎（二）