Mysql 支持3中鎖結構

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

InnoDB鎖問題

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

InnoDB的行鎖模式及加鎖方法

InnoDB實現了以下兩種類型的行鎖。

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

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務打算給數據行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。 意向排他鎖（IX）：事務打算給數據行加排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。 當前鎖模式和請求鎖模式 X IX S IS X 沖突 沖突 沖突 沖突 IX 沖突 兼容 沖突 兼容 S 沖突 沖突 兼容 兼容 IS 沖突 兼容 兼容 兼容

InnoDB行鎖是通過索引上的索引項來實現的，這一點ＭySQL與Oracle不同，后者是通過在數據中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味者：只有通過索引條件檢索數據，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！

Next-Key鎖

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

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

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

什么時候使用表鎖?

對于InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務中，也可以考慮使用表級鎖。

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

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

當然，應用中這兩種事務不能太多，否則，就應該考慮使用ＭyISAＭ表。 在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;

死鎖

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

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

（１）在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序為訪問表，這樣可以大大降低產生死鎖的機會。如果兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。

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

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

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

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

MyISAM 和 InnoDB 區別

對于ＭyISAM的表鎖，主要有以下幾點

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

對于InnoDB表，主要有以下幾點

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

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

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

MySql樂觀鎖悲觀鎖

悲觀鎖

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

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

樂觀鎖

樂觀鎖的特點先進行業務操作，不到萬不得已不去拿鎖。即“樂觀”的認為拿鎖多半是會成功的，因此在進行完業務操作需要實際更新數據的最后一步再去拿一下鎖就好。

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

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

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

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

以上就是詳解mysql 中的鎖結構的詳細內容，更多關于MySQL 鎖結構的資料請關注好吧啦網其它相關文章！