Mysql 支持3中鎖結(jié)構(gòu)

• 表級鎖,開銷小,加鎖快,不會出現(xiàn)死鎖,鎖定的粒度大,沖突概率高,并發(fā)度最低
• 行級鎖,開銷小,加鎖慢,會出現(xiàn)死鎖,鎖定粒度小,沖突概率最低,并發(fā)度最高
• 頁面鎖,開銷和加鎖處于表鎖和行鎖之間,會出現(xiàn)死鎖,鎖粒度基于表和行之間,并發(fā)一般
  

InnoDB鎖問題

InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）；二是采用了行級鎖。
 行級鎖和表級鎖本來就有許多不同之處，另外，事務(wù)的引入也帶來了一些新問題。

InnoDB的行鎖模式及加鎖方法

InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖。

• 共享鎖（s）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
• 排他鎖（Ｘ）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

• 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
• 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

InnoDB行鎖是通過索引上的索引項(xiàng)來實(shí)現(xiàn)的，這一點(diǎn)ＭySQL與Oracle不同，后者是通過在數(shù)據(jù)中對相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味者：只有通過索引條件檢索數(shù)據(jù)，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！

Next-Key鎖

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)的索引項(xiàng)加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機(jī)制不是所謂的間隙鎖（Next-Key鎖）。
舉例來說，假如emp表中只有101條記錄，其empid的值分別是1,2,...,100,101，下面的SQL：

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

是一個范圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。
InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關(guān)隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務(wù)插入了empid大于100的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句，就會發(fā)生幻讀；另一方面，是為了滿足其恢復(fù)和復(fù)制的需要。有關(guān)其恢復(fù)和復(fù)制對機(jī)制的影響，以及不同隔離級別下InnoDB使用間隙鎖的情況。
很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機(jī)制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會造成嚴(yán)重的鎖等待。因此，在實(shí)際開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。

什么時候使用表鎖?

對于InnoDB表，在絕大部分情況下都應(yīng)該使用行級鎖，因?yàn)槭聞?wù)和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務(wù)中，也可以考慮使用表級鎖。

第一種情況是：事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。

第二種情況是：事務(wù)涉及多個表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

當(dāng)然，應(yīng)用中這兩種事務(wù)不能太多，否則，就應(yīng)該考慮使用ＭyISAＭ表。
 在InnoDB下 ，使用表鎖要注意以下兩點(diǎn)。

（１）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層ＭySQL Server負(fù)責(zé)的，僅當(dāng)autocommit=0、innodb_table_lock=1（默認(rèn)設(shè)置）時，InnoDB層才能知道MySQL加的表鎖，ＭySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測并處理這種死鎖。

（２）在用LOCAK TABLES對InnoDB鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則ＭySQL不會給表加鎖；事務(wù)結(jié)束前，不要用UNLOCAK TABLES釋放表鎖，因?yàn)閁NLOCK TABLES會隱含地提交事務(wù)；COMMIT或ROLLBACK產(chǎn)不能釋放用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;

死鎖

在InnoDB中，除單個SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了InnoDB發(fā)生死鎖是可能的。
 發(fā)生死鎖后，InnoDB一般都能自動檢測到，并使一個事務(wù)釋放鎖并退回，另一個事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及鎖的情況下，InnoDB并不能完全自動檢測到死鎖，這需要通過設(shè)置鎖等待超時參數(shù)innodb_lock_wait_timeout來解決。需要說明的是，這個參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲取所需的鎖而掛起，會占用大量計算機(jī)資源，造成嚴(yán)重性能問題，甚至拖垮數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時閾值，可以避免這種情況發(fā)生。

通常來說，死鎖都是應(yīng)用設(shè)計的問題，通過調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫對象設(shè)計、事務(wù)大小、以及訪問數(shù)據(jù)庫的SQL語句，絕大部分都可以避免。下面就通過實(shí)例來介紹幾種死鎖的常用方法。

（１）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序?yàn)樵L問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會。如果兩個session訪問兩個表的順序不同，發(fā)生死鎖的機(jī)會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。

（２）在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。

（３）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)該先申請共享鎖，更新時再申請排他鎖，甚至死鎖。

（４）在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現(xiàn)死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。

（５）當(dāng)隔離級別為READ COMMITED時，如果兩個線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現(xiàn)鎖等待，當(dāng)?shù)冢眰€線程提交后，第２個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第３個線程又來申請排他鎖，也會出現(xiàn)死鎖。對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。

MyISAM 和 InnoDB 區(qū)別

對于ＭyISAM的表鎖，主要有以下幾點(diǎn)

（１）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串行的。
（２）在一定條件下，ＭyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以利用這一點(diǎn)來解決應(yīng)用中對同一表和插入的鎖爭用問題。
（３）ＭyISAM默認(rèn)的鎖調(diào)度機(jī)制是寫優(yōu)先，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置LOW_PRIPORITY_UPDATES參數(shù)，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項(xiàng)來調(diào)節(jié)讀寫鎖的爭用。
（４）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，ＭyISAM表可能會出現(xiàn)嚴(yán)重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。

對于InnoDB表，主要有以下幾點(diǎn)

（１）InnoDB的行銷是基于索引實(shí)現(xiàn)的，如果不通過索引訪問數(shù)據(jù)，InnoDB會使用表鎖。
（２）InnoDB間隙鎖機(jī)制，以及InnoDB使用間隙鎖的原因。
（３）在不同的隔離級別下，InnoDB的鎖機(jī)制和一致性讀策略不同。
（４）ＭySQL的恢復(fù)和復(fù)制對InnoDB鎖機(jī)制和一致性讀策略也有較大影響。
（５）鎖沖突甚至死鎖很難完全避免。

在了解InnoDB的鎖特性后，用戶可以通過設(shè)計和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：

• 盡量使用較低的隔離級別
  
• 精心設(shè)計索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機(jī)會。
• 選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小。
• 給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖。
• 不同的程序訪問一組表時，應(yīng)盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大減少死鎖的機(jī)會。
• 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響。
• 不要申請超過實(shí)際需要的鎖級別；除非必須，查詢時不要顯示加鎖。
• 對于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能

MySql樂觀鎖悲觀鎖

悲觀鎖

悲觀鎖的特點(diǎn)是先獲取鎖，再進(jìn)行業(yè)務(wù)操作，即“悲觀”的認(rèn)為獲取鎖是非常有可能失敗的，因此要先確保獲取鎖成功再進(jìn)行業(yè)務(wù)操作。通常所說的“一鎖二查三更新”即指的是使用悲觀鎖。通常來講在數(shù)據(jù)庫上的悲觀鎖需要數(shù)據(jù)庫本身提供支持，即通過常用的select … for update操作來實(shí)現(xiàn)悲觀鎖。當(dāng)數(shù)據(jù)庫執(zhí)行select for update時會獲取被select中的數(shù)據(jù)行的行鎖，因此其他并發(fā)執(zhí)行的select for update如果試圖選中同一行則會發(fā)生排斥（需要等待行鎖被釋放），因此達(dá)到鎖的效果。select for update獲取的行鎖會在當(dāng)前事務(wù)結(jié)束時自動釋放，因此必須在事務(wù)中使用。

這里需要注意的一點(diǎn)是不同的數(shù)據(jù)庫對select for update的實(shí)現(xiàn)和支持都是有所區(qū)別的，例如oracle支持select for update no wait，表示如果拿不到鎖立刻報錯，而不是等待，mysql就沒有no wait這個選項(xiàng)。另外mysql還有個問題是select for update語句執(zhí)行中所有掃描過的行都會被鎖上，這一點(diǎn)很容易造成問題。因此如果在mysql中用悲觀鎖務(wù)必要確定走了索引，而不是全表掃描。

樂觀鎖

樂觀鎖的特點(diǎn)先進(jìn)行業(yè)務(wù)操作，不到萬不得已不去拿鎖。即“樂觀”的認(rèn)為拿鎖多半是會成功的，因此在進(jìn)行完業(yè)務(wù)操作需要實(shí)際更新數(shù)據(jù)的最后一步再去拿一下鎖就好。

樂觀鎖在數(shù)據(jù)庫上的實(shí)現(xiàn)完全是邏輯的，不需要數(shù)據(jù)庫提供特殊的支持。一般的做法是在需要鎖的數(shù)據(jù)上增加一個版本號，或者時間戳，然后按照如下方式實(shí)現(xiàn)：

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根據(jù)獲取的數(shù)據(jù)進(jìn)行業(yè)務(wù)操作，得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
  // 樂觀鎖獲取成功，操作完成
} else {
  // 樂觀鎖獲取失敗，回滾并重試
}

在數(shù)據(jù)庫內(nèi)部update同一行的時候是不允許并發(fā)的，即數(shù)據(jù)庫每次執(zhí)行一條update語句時會獲取被update行的寫鎖，直到這一行被成功更新后才釋放。因此在業(yè)務(wù)操作進(jìn)行前獲取需要鎖的數(shù)據(jù)的當(dāng)前版本號，然后實(shí)際更新數(shù)據(jù)時再次對比版本號確認(rèn)與之前獲取的相同，并更新版本號，即可確認(rèn)這之間沒有發(fā)生并發(fā)的修改。如果更新失敗即可認(rèn)為老版本的數(shù)據(jù)已經(jīng)被并發(fā)修改掉而不存在了，此時認(rèn)為獲取鎖失敗，需要回滾整個業(yè)務(wù)操作并可根據(jù)需要重試整個過程。

樂觀鎖在不發(fā)生取鎖失敗的情況下開銷比悲觀鎖小，但是一旦發(fā)生失敗回滾開銷則比較大，因此適合用在取鎖失敗概率比較小的場景，可以提升系統(tǒng)并發(fā)性能
樂觀鎖還適用于一些比較特殊的場景，例如在業(yè)務(wù)操作過程中無法和數(shù)據(jù)庫保持連接等悲觀鎖無法適用的地方

以上就是詳解mysql 中的鎖結(jié)構(gòu)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 鎖結(jié)構(gòu)的資料請關(guān)注腳本之家其它相關(guān)文章！