MySQL 鎖機制常用知識點有哪些？

本文和大家分享的主要是MySQL 鎖機制相關(guān)內(nèi)容，一起來看看吧，希望對大家學習MySQL有所幫助。

　　行鎖、表鎖對比

開銷、加鎖速度、死鎖、粒度、并發(fā)性能

　?。?span>表鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低

　?。?span>行鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高

　　MySQL表級鎖的鎖模式

　?。甅ySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。

　?。?span>對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！

　　MyISAM的并發(fā)插入（Concurrent Inserts）

　　MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進行。

　　MyISAM存儲引擎有一個系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。

　　． 當concurrent_insert設(shè)置為0時，不允許并發(fā)插入。

　?。?nbsp;當concurrent_insert設(shè)置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設(shè)置。

　　． 當concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。

　　可以利用MyISAM存儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同一表查詢和插入的鎖爭用。例如，將concurrent_insert系統(tǒng)變量設(shè)為2，總是允許并發(fā)插入；同時，通過定期在系統(tǒng)空閑時段執(zhí)行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞。

　　MyISAM的鎖調(diào)度

　　MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。一個進程請求某個 MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，寫進程先獲得鎖。即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！

　　我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM 的調(diào)度行為。

　　． 通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認給予讀請求以優(yōu)先的權(quán)利。

　?。?nbsp;通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級降低。

　?。?nbsp;通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級。

　　另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進程一定獲得鎖的機會。

　　InnoDB的行鎖模式

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

　?。?nbsp;共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

　?。?nbsp;排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

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

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

　　InnoDB的行鎖加鎖方法

　　意向鎖是InnoDB自動加的，不需用戶干預(yù)。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

　?。?nbsp;共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

　?。?nbsp;排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

　　InnoDB鎖的互斥與兼容關(guān)系

　　鎖和鎖之間的關(guān)系，要么是相容的，要么是互斥的。

　?。?nbsp;鎖a和鎖b相容是指：操作同樣一組數(shù)據(jù)時，如果事務(wù)t1獲取了鎖a,另一個事務(wù)t2還可以獲取鎖b；

　　． 鎖a和鎖b互斥是指：操作同樣一組數(shù)據(jù)時，如果事務(wù)t1獲取了鎖a，另一個事務(wù)t2在t1釋放鎖a之前無法獲取鎖b。

　　(y表示兼容，n表示不兼容)

InnoDB行鎖實現(xiàn)方式

　　InnoDB行鎖是通過 給索引上的索引項加鎖 來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。

　　InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

　　． 在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。

　?。?nbsp;由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。

　　． 當表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖。

　?。?nbsp;即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。

　　InnoDB間隙鎖（Next-Key鎖）

　　當我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

　　InnoDB使用間隙鎖的目的

　?。?nbsp;一方面是為了防止幻讀，以滿足相關(guān)隔離級別的要求.

　?。?nbsp;一方面是滿足其恢復(fù)和復(fù)制的需要.

　　恢復(fù)和復(fù)制的需要，對InnoDB鎖機制的影響

　　MySQL通過BINLOG錄執(zhí)行成功的INSERT、UPDATE、DELETE等更新數(shù)據(jù)的SQL語句，并由此實現(xiàn)MySQL數(shù)據(jù)庫的恢復(fù)和主從復(fù)制（可以參見本書“管理篇”的介紹）。MySQL的恢復(fù)機制（復(fù)制其實就是在Slave Mysql不斷做基于BINLOG的恢復(fù)）有以下特點。

　?。?nbsp;一是MySQL的恢復(fù)是SQL語句級的，也就是重新執(zhí)行BINLOG中的SQL語句。這與Oracle數(shù)據(jù)庫不同，Oracle是基于數(shù)據(jù)庫文件塊的。

　?。?nbsp;二是MySQL的Binlog是按照事務(wù)提交的先后順序記錄的，恢復(fù)也是按這個順序進行的。

　　從上面兩點可知，MySQL的恢復(fù)機制要求：在一個事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀，這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級別的要求，實際上是要求事務(wù)要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用范圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖，但這并不是隔離級別要求的.

　　InnoDB什么時候使用表鎖

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

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

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

　　InnoDB使用表鎖注意事項

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

　?。?nbsp;（2）在用 LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則MySQL不會給表加鎖；事務(wù)結(jié)束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務(wù)；COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。

　　死鎖

　　MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現(xiàn)死鎖。但在InnoDB中，除單個SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了在InnoDB中發(fā)生死鎖是可能的。

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

　　避免死鎖的常用方法

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

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

　?。?nbsp;（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

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

　?。?nbsp;（5）當隔離級別為READ COMMITTED時，如果兩個線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現(xiàn)鎖等待，當?shù)?/span>1個線程提交后，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現(xiàn)死鎖。

　　如果出現(xiàn)死鎖，可以用SHOW INNODB STATUS命令來確定最后一個死鎖產(chǎn)生的原因。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細信息，如引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進措施。

 

 

來源：簡書