mysql中innoDB鎖定的介紹-mysql教程-PHP中文網

在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調整等措施減少鎖定衝突和死鎖，包括：