mysql-innoDB-鎖_ZenDei技術網路在線

在InnoDB加鎖前，為什麼要先start transaction innodb下鎖的釋放在事務提交/回滾之後，事務一旦提交/回滾之後，就會自動釋放事務中的鎖，innodb預設情況下autocommit=1即開啟自動提交檢索條件使用索引和不使用索引的鎖區別：檢索條件有索引的情況下會鎖定特定的一些 ...

在InnoDB加鎖前，為什麼要先start transaction

　　innodb下鎖的釋放在事務提交/回滾之後，事務一旦提交/回滾之後，就會自動釋放事務中的鎖，innodb預設情況下autocommit=1即開啟自動提交

檢索條件使用索引和不使用索引的鎖區別：

　　檢索條件有索引的情況下會鎖定特定的一些行。

檢索條件沒有使用使用的情況下會進行全表掃描，從而鎖定全部的行（包括不存在的記錄）

讀鎖:

　　讀鎖是共用的，或者說是相互不阻塞的。多個用戶在同一時刻可以同時讀取同一個資源，而互不幹擾。

寫鎖：

　　寫鎖是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖。另外寫鎖比讀鎖有更高的優先順序，因此一個寫鎖請求可能會被插入到讀鎖隊列的前面，但是讀鎖則不肯能插入到寫鎖的前面

表鎖：

　　InnoDB還有兩個表鎖：意向共用鎖（IS），意向排它鎖（IX）

行鎖：

　　InnoDB實現了兩種類型額行級鎖，共用鎖和排它鎖

樂觀鎖：

　　樂觀鎖，也叫樂觀併發控制，它假設多用戶併發的事務在處理時不會彼此互相影響，各事務能夠在不產生鎖的情況下處理各自影響的那部分數據。在提交數據更新之前，每個事務會先檢查在該事務讀取數據後，有沒有其他事務又修改了該數據。如果其他事務有更新的話，那麼當前正在提交的事務會進行回滾。

悲觀鎖:

　　悲觀鎖，也叫悲觀併發控制，當事務A對某行數據應用了鎖，並且當這個事務把鎖釋放後，其他事務才能夠執行與該鎖衝突的操作，這裡事務A所施加的鎖就叫悲觀鎖。享鎖和排他鎖（行鎖，間隙鎖，next-key lock）都屬於悲觀鎖

悲觀鎖與樂觀鎖的實現方式：

　　悲觀鎖的實現依靠的是資料庫提供的鎖機制來實現，例如select * from news where id=12 for update,而樂觀鎖依靠的是記錄數據版本來實現，即通過在表中添加版本號欄位來作為是否可以成功提交的關鍵因素。

共用鎖（S）：

　　共用鎖也叫讀鎖，一個事務獲取了一個數據行的共用鎖，其他事務能獲得該行對應的共用鎖，但不能獲得排他鎖，即一個事務在讀取一個數據行的時候，其他事務也可以讀，但不能對該數據行進行增刪改

　　設置共用鎖： SELECT .... LOCK IN SHARE MODE;

排它鎖（X）：

　　排它鎖也叫寫鎖，一個事務獲取了一個數據行的排他鎖，其他事務就不能再獲取該行的其他鎖（排他鎖或者共用鎖），即一個事務在讀取一個數據行的時候，其他事務不能對該數據行進行增刪改查

　　設置排它鎖：SELECT .... FOR UPDATE

　　註意點：

對於select 語句，innodb不會加任何鎖，也就是可以多個併發去進行select的操作，不會有任何的鎖衝突，因為根本沒有鎖。
對於insert，update，delete操作，innodb會自動給涉及到的數據加排他鎖，只有查詢select需要我們手動設置排他鎖。

意向共用鎖（IS）：

　　通知資料庫接下來需要施加什麼鎖並對錶加鎖。如果需要對記錄A加共用鎖，那麼此時innodb會先找到這張表，對該表加意向共用鎖之後，再對記錄A添加共用鎖。也就是說一個數據行加共用鎖前必須先取得該表的IS鎖

意向排它鎖（IX）：

　　通知資料庫接下來需要施加什麼鎖並對錶加鎖。如果需要對記錄A加排他鎖，那麼此時innodb會先找到這張表，對該表加意向排他鎖之後，再對記錄A添加共用鎖。也就是說一個數據行加排它鎖前必須先取得該表的IX鎖

　　共用鎖和意向共用鎖，排他鎖與意向排他鎖的區別：

共用鎖和排他鎖，系統在特定的條件下會自動添加共用鎖或者排他鎖，也可以手動添加共用鎖或者排他鎖。
意向共用鎖和意向排他鎖都是系統自動添加和自動釋放的，整個過程無需人工干預。
共用鎖和排他鎖都是鎖的行記錄，意向共用鎖和意向排他鎖鎖定的是表。

鎖的實現方式：

　　在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。

　　InnoDB行鎖是通過給索引項加鎖實現的，如果沒有索引，InnoDB會通過隱藏的聚簇索引來對記錄加鎖。也就是說：如果不通過索引條件檢索數據，那麼InnoDB將對錶中所有數據加鎖，實際效果跟表鎖一樣

行鎖分為三種情況：

　　Record Lock:對索引項加鎖，即鎖定一條記錄。

　　Gap Lock:對索引項之間的 ‘間隙’ 、對第一條記錄前的間隙或最後一條記錄後的間隙加鎖，即鎖定一個範圍的記錄，不包含記錄本身

　　Next-key Lock:鎖定一個範圍的記錄並包含記錄本身(上面兩者的結合)

　　註意：InnoDB預設級別是repeatable-read（重覆讀）級別。ANSI/IOS SQL標准定義了4種事務隔離級別：未提交讀(read uncommitted)，提交讀(read committed)，重覆讀(repeatable read)，串列讀(serializable)

Gap Lock和Next-key Lock的區別：

　　Next-Key Lock是行鎖與間隙鎖的組合，這樣，當InnoDB掃描索引記錄的時候，會首先對選中的索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）。如果一個間隙被事務T1加了鎖，其它事務是不能在這個間隙插入記錄的。

　　行鎖防止別的事務修改或刪除，Gap鎖防止別的事務新增，行鎖和GAP鎖結合形成的Next-Key鎖共同解決了RR界別在寫數據時的幻讀問題。

何時在InnoDB中使用表鎖：

　　InnoDB在絕大部分情況會使用行級鎖，因為事務和行鎖往往是我們選擇InnoDB的原因，但是有些情況下我們也考慮使用表級鎖

當事務需要更新大部分數據時，表又比較大，如果使用預設的行鎖，不僅效率低，而且還容易造成其他事務長時間等待和鎖衝突。
事務比較複雜，很可能引起死鎖導致回滾。

在InnoDB下，使用表鎖要註意以下兩點。

（１）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層ＭySQL Server負責的，僅當autocommit=0、innodb_table_lock=1（預設設置）時，InnoDB層才能知道MySQL加的表鎖，ＭySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測並處理這種死鎖。（２）在用LOCAK TABLES對InnoDB鎖時要註意，要將AUTOCOMMIT設為0，否則ＭySQL不會給表加鎖；事務結束前，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK不能釋放用LOCAK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下：　　例如：如果需要寫表t1並從表t讀　　

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

死鎖：

　　我們說過MyISAM中是不會產生死鎖的，因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼全部等待。而在InnoDB中，鎖是逐步獲得的，就造成了死鎖的可能。

　發生死鎖後，InnoDB一般都可以檢測到，並使一個事務釋放鎖回退，另一個獲取鎖完成事務。但在涉及外部鎖，或涉及鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在併發訪問比較高的情況下，如果大量事務因無法立即獲取所需的鎖而掛起，會占用大量電腦資源，造成嚴重性能問題，甚至拖垮資料庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。

　　有多種方法可以避免死鎖，這裡介紹常見的三種：

如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。如果兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。
在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率。
對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概。
在程式以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。
在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程式發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這麼做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。
當隔離級別為READ COMMITED時，如果兩個線程都先執行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第１個線程提交後，第２個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第３個線程又來申請排他鎖，也會出現死鎖。對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖

　　　ps:如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因和改進措施。

總結：

　　對於InnoDB表，主要有以下幾點（１）InnoDB的行銷是基於索引實現的，如果不通過索引訪問數據，InnoDB會使用表鎖。（２）InnoDB間隙鎖機制，以及InnoDB使用間隙鎖的原因。（３）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。（４）ＭySQL的恢復和複製對InnoDB鎖機制和一致性讀策略也有較大影響。（５）鎖衝突甚至死鎖很難完全避免。　　在瞭解InnoDB的鎖特性後，用戶可以通過設計和SQL調整等措施減少鎖衝突和死鎖，包括：