mysql行鎖和表鎖
在永平等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都做網(wǎng)站、網(wǎng)站設(shè)計 網(wǎng)站設(shè)計制作按需網(wǎng)站開發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站建設(shè),成都全網(wǎng)營銷,外貿(mào)網(wǎng)站制作,永平網(wǎng)站建設(shè)費用合理。
鎖是計算機協(xié)調(diào)多個進(jìn)程或純線程并發(fā)訪問某一資源的機制。在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所在有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。
概述
相對其他數(shù)據(jù)庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。
MySQL大致可歸納為以下3種鎖：
表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般
MySQL表級鎖的鎖模式（MyISAM)
MySQL表級鎖有兩種模式：表共享鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。
對MyISAM的讀操作，不會阻塞其他用戶對同一表請求，但會阻塞對同一表的寫請求；
對MyISAM的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
MyISAM表的讀操作和寫操作之間，以及寫操作之間是串行的。
當(dāng)一個線程獲得對一個表的寫鎖后，只有持有鎖線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
MySQL表級鎖的鎖模式
MySQL的表鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的兼容如下表
MySQL中的表鎖兼容性
當(dāng)前鎖模式/是否兼容/請求鎖模式
讀鎖 ? ?是 ? ?是 ? ?否 ?
寫鎖 ? ?是 ? ?否 ? ?否 ?
可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串行的?。ó?dāng)一線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。）
如何加表鎖
MyISAM在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預(yù)，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，并非必須如此。
給MyISAM表顯示加鎖，一般是為了一定程度模擬事務(wù)操作，實現(xiàn)對某一時間點多個表的一致性讀取。
要特別說明以下兩點內(nèi)容。
上面的例子在LOCK TABLES時加了‘local’選項，其作用就是在滿足MyISAM表并發(fā)插入條件的情況下，允許其他用戶在表尾插入記錄
在用LOCKTABLES給表顯式加表鎖是時，必須同時取得所有涉及表的鎖，并且MySQL支持鎖升級。也就是說，在執(zhí)行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執(zhí)行查詢操作，而不能執(zhí)行更新操作。其實，在自動加鎖的情況下也基本如此，MySQL問題一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現(xiàn)死鎖（Deadlock Free）的原因
一個session使用LOCK TABLE 命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現(xiàn)鎖等待。
當(dāng)使用LOCK TABLE時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現(xiàn)多少次，就要通過與SQL語句中相同的別名鎖多少次，否則也會出錯！
并發(fā)鎖
在一定條件下，MyISAM也支持查詢和操作的并發(fā)進(jìn)行。
MyISAM存儲引擎有一個系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。
當(dāng)concurrent_insert設(shè)置為0時，不允許并發(fā)插入。
當(dāng)concurrent_insert設(shè)置為1時，如果MyISAM允許在一個讀表的同時，另一個進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。
當(dāng)concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞，都允許在表尾插入記錄，都允許在表尾并發(fā)插入記錄。
可以利用MyISAM存儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同一表查詢和插入鎖爭用。例如，將concurrent_insert系統(tǒng)變量為2，總是允許并發(fā)插入；同時，通過定期在系統(tǒng)空閑時段執(zhí)行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產(chǎn)生的中間空洞。
MyISAM的鎖調(diào)度
前面講過，MyISAM存儲引擎的讀和寫鎖是互斥，讀操作是串行的。那么，一個進(jìn)程請求某個MyISAM表的讀鎖，同時另一個進(jìn)程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進(jìn)程先獲得鎖。不僅如此，即使讀進(jìn)程先請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀請求之前！這是因為MySQL認(rèn)為寫請求一般比讀請求重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應(yīng)用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM的調(diào)度行為。
通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請求以優(yōu)先的權(quán)利。
通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級。
雖然上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法，但還是可以用其來解決查詢相對重要的應(yīng)用（如用戶登錄系統(tǒng)）中，讀鎖等待嚴(yán)重的問題。
另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達(dá)到這個值后，MySQL變暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機會。
上面已經(jīng)討論了寫優(yōu)先調(diào)度機制和解決辦法。這里還要強調(diào)一點：一些需要長時間運行的查詢操作，也會使寫進(jìn)程“餓死”！因此，應(yīng)用中應(yīng)盡量避免出現(xiàn)長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題。因為這種看似巧妙的SQL語句，往往比較復(fù)雜，執(zhí)行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時段執(zhí)行，比如一些定期統(tǒng)計可以安排在夜間執(zhí)行。
InnoDB鎖問題
InnoDB與MyISAM的最大不同有兩點：一是支持事務(wù)（TRANSACTION）；二是采用了行級鎖。
行級鎖和表級鎖本來就有許多不同之處，另外，事務(wù)的引入也帶來了一些新問題。
1.事務(wù)（Transaction）及其ACID屬性
事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有4屬性，通常稱為事務(wù)的ACID屬性。
原性性（Actomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。
一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以操持完整性；事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。這意味著事務(wù)處理過程中的中間狀態(tài)對外部是不可見的，反之亦然。
持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。
2.并發(fā)事務(wù)帶來的問題
相對于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持可以支持更多的用戶。但并發(fā)事務(wù)處理也會帶來一些問題，主要包括以下幾種情況。
更新丟失（Lost Update）：當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題——最后的更新覆蓋了其他事務(wù)所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改。如果在一個編輯人員完成并提交事務(wù)之前，另一個編輯人員不能訪問同一文件，則可避免此問題
臟讀（Dirty Reads）：一個事務(wù)正在對一條記錄做修改，在這個事務(wù)并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”的數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做“臟讀”。
不可重復(fù)讀（Non-Repeatable Reads）：一個事務(wù)在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象叫做“不可重復(fù)讀”。
幻讀（Phantom Reads）：一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。
3.事務(wù)隔離級別
在并發(fā)事務(wù)處理帶來的問題中，“更新丟失”通常應(yīng)該是完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。
“臟讀”、“不可重復(fù)讀”和“幻讀”，其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式，基本可以分為以下兩種。
一種是在讀取數(shù)據(jù)前，對其加鎖，阻止其他事務(wù)對數(shù)據(jù)進(jìn)行修改。
另一種是不用加任何鎖，通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot），并用這個快照來提供一定級別（語句級或事務(wù)級）的一致性讀取。從用戶的角度，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本，因此，這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經(jīng)常稱為多版本數(shù)據(jù)庫。
數(shù)據(jù)庫的事務(wù)隔離級別越嚴(yán)格，并發(fā)副作用越小，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的，同時，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。
為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSI SQL92定義了4個事務(wù)隔離級別，每個級別的隔離程度不同，允許出現(xiàn)的副作用也不同，應(yīng)用可以根據(jù)自己業(yè)務(wù)邏輯要求，通過選擇不同的隔離級別來平衡＂隔離＂與＂并發(fā)＂的矛盾
事務(wù)4種隔離級別比較
隔離級別/讀數(shù)據(jù)一致性及允許的并發(fā)副作用 ? ?讀數(shù)據(jù)一致性 ? ?臟讀 ? ?不可重復(fù)讀 ? ?幻讀 ?
未提交讀（Read uncommitted）
最低級別，只能保證不讀取物理上損壞的數(shù)據(jù) ? ?是 ? ?是 ? ?是 ?
已提交度（Read committed） ? ?語句級 ? ?否 ? ?是 ? ?是 ?
可重復(fù)讀（Repeatable read） ? ?事務(wù)級 ? ?否 ? ?否 ? ?是 ?
可序列化（Serializable） ? ?最高級別，事務(wù)級 ? ?否 ? ?否 ? ?否 ?
最后要說明的是：各具體數(shù)據(jù)庫并不一定完全實現(xiàn)了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標(biāo)準(zhǔn)級別，另外還自己定義的Read only隔離級別：SQL Server除支持上述ISO/ANSI SQL92定義的4個級別外，還支持一個叫做＂快照＂的隔離級別，但嚴(yán)格來說它是一個用MVCC實現(xiàn)的Serializable隔離級別。MySQL支持全部4個隔離級別，但在具體實現(xiàn)時，有一些特點，比如在一些隔離級下是采用MVCC一致性讀，但某些情況又不是。
獲取InonoD行鎖爭用情況
可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：
如果發(fā)現(xiàn)爭用比較嚴(yán)重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設(shè)置InnoDB Monitors來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭用的原因。?
InnoDB的行鎖模式及加鎖方法
InnoDB實現(xiàn)了以下兩種類型的行鎖。
共享鎖（s）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。
另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。
InnoDB行鎖模式兼容性列表
如果一個事務(wù)請求的鎖模式與當(dāng)前的鎖兼容，InnoDB就請求的鎖授予該事務(wù)；反之，如果兩者兩者不兼容，該事務(wù)就要等待鎖釋放。
意向鎖是InnoDB自動加的，不需用戶干預(yù)。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及及數(shù)據(jù)集加排他鎖（X）；對于普通SELECT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X）；對于普通SELECT語句，InnoDB不會任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排鎖。
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE ...?FOR UPDATE
用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時確認(rèn)某行記錄是否存在，并確保沒有人對這個記錄進(jìn)行UPDATE或者DELETE操作。但是如果當(dāng)前事務(wù)也需要對該記錄進(jìn)行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用SELECT ... FOR UPDATE方式獲取排他鎖。
InnoDB行鎖實現(xiàn)方式
InnoDB行鎖是通過索引上的索引項來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味者：只有通過索引條件檢索數(shù)據(jù)，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！
在實際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。
什么時候使用表鎖
對于InnoDB表，在絕大部分情況下都應(yīng)該使用行級鎖，因為事務(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)該考慮使用MyISAM表。
在InnoDB下 ，使用表鎖要注意以下兩點。
（1）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層MySQL Server負(fù)責(zé)的，僅當(dāng)autocommit=0、innodb_table_lock=1（默認(rèn)設(shè)置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測并處理這種死鎖。
（2）在用LOCAK TABLES對InnoDB鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則MySQL不會給表加鎖；事務(wù)結(jié)束前，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務(wù)；COMMIT或ROLLBACK產(chǎn)不能釋放用LOCAK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句。
關(guān)于死鎖
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ù)因無法立即獲取所需的鎖而掛起，會占用大量計算機資源，造成嚴(yán)重性能問題，甚至拖垮數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時閾值，可以避免這種情況發(fā)生。
通常來說，死鎖都是應(yīng)用設(shè)計的問題，通過調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫對象設(shè)計、事務(wù)大小、以及訪問數(shù)據(jù)庫的SQL語句，絕大部分都可以避免。下面就通過實例來介紹幾種死鎖的常用方法。
（1）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序為訪問表，這樣可以大大降低產(chǎn)生死鎖的機會。如果兩個session訪問兩個表的順序不同，發(fā)生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。
（2）在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。
（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)該先申請共享鎖，更新時再申請排他鎖，甚至死鎖。
（4）在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現(xiàn)死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。
（5）當(dāng)隔離級別為READ COMMITED時，如果兩個線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現(xiàn)鎖等待，當(dāng)?shù)?個線程提交后，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現(xiàn)死鎖。對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。
盡管通過上面的設(shè)計和優(yōu)化等措施，可以大減少死鎖，但死鎖很難完全避免。因此，在程序設(shè)計中總是捕獲并處理死鎖異常是一個很好的編程習(xí)慣。
如果出現(xiàn)死鎖，可以用SHOW INNODB STATUS命令來確定最后一個死鎖產(chǎn)生的原因和改進(jìn)措施。
總結(jié)
對于MyISAM的表鎖，主要有以下幾點
（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串行的。
（2）在一定條件下，MyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以利用這一點來解決應(yīng)用中對同一表和插入的鎖爭用問題。
（3）MyISAM默認(rèn)的鎖調(diào)度機制是寫優(yōu)先，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置LOW_PRIPORITY_UPDATES參數(shù)，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調(diào)節(jié)讀寫鎖的爭用。
（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，MyISAM表可能會出現(xiàn)嚴(yán)重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。
對于InnoDB表，主要有以下幾點
（1）InnoDB的行銷是基于索引實現(xiàn)的，如果不通過索引訪問數(shù)據(jù)，InnoDB會使用表鎖。
（2）InnoDB間隙鎖機制，以及InnoDB使用間隙鎖的原因。
（3）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。
（4）MySQL的恢復(fù)和復(fù)制對InnoDB鎖機制和一致性讀策略也有較大影響。
（5）鎖沖突甚至死鎖很難完全避免。
在了解InnoDB的鎖特性后，用戶可以通過設(shè)計和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：
盡量使用較低的隔離級別
精心設(shè)計索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機會。
選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小。
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖。
不同的程序訪問一組表時，應(yīng)盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大減少死鎖的機會。
盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響。
不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖。
對于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能
1、在mysql數(shù)據(jù)庫中如何鎖定一行數(shù)據(jù)，保證不被其他的操作影響。
2、從對數(shù)據(jù)的操作類型分為讀鎖和寫鎖。從對數(shù)據(jù)操作的粒度來分：表鎖和行鎖。
3、現(xiàn)在我們建立一個表來演示數(shù)據(jù)庫的行鎖講解。
4、行鎖基本演示如下圖所示。
5、如果兩個會話操作的是不同的行，就不會互相阻塞了。
鎖是計算機協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機制，在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）爭用外，數(shù)據(jù)也是一種供許多用戶共享的資源，如何保證數(shù)據(jù)并發(fā)訪問的一致性，有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素，從這個角度來說，鎖對數(shù)據(jù)庫而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。
全局鎖就是對整個數(shù)據(jù)庫實例加鎖，加鎖后整個實例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對數(shù)據(jù)庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進(jìn)制日志，會導(dǎo)致主從延遲。
解決辦法是在innodb引擎中，備份時加上--single-transaction參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級鎖,每次操作會鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock
語法: 1、加鎖 lock tables 表名 ... read/write
2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接
注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。
元數(shù)據(jù)鎖,在加鎖過程中是系統(tǒng)自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務(wù)的時候，不可以對元數(shù)據(jù)進(jìn)行寫入操作。為了避免DML和DDL沖突，保證讀寫的正確性。
在MySQL5.5中引入了MDL，當(dāng)對一張表進(jìn)行增刪改查的時候，加MDL讀鎖(共享)；當(dāng)對表結(jié)構(gòu)進(jìn)行變更操作時，加MDL寫鎖(排他).
查看元數(shù)據(jù)鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖，為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲引擎中。
innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現(xiàn)的，而不是對記錄加的鎖，對于行級鎖，主要分為以下三類:
1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進(jìn)行update和delete操作，在RC,RR隔離級別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進(jìn)行insert操作，產(chǎn)生幻讀，在RR隔離級別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級別下支持。
innodb實現(xiàn)了以下兩種類型的行鎖
1、共享鎖 S: 允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。
insert 語句 排他鎖 自動添加的
update語句 排他鎖 自動添加
delete 語句 排他鎖 自動添加
select 正常查詢語句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode
select 。。。for update 排他鎖 需要手動在select之后添加for update
默認(rèn)情況下，innodb在repeatable read事務(wù)隔離級別運行，innodb使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。
間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個事務(wù)采用的間隙鎖不會阻止另一個事務(wù)在同一間隙上采用的間隙鎖。
加鎖情況與死鎖原因分析
為方便大家復(fù)現(xiàn)，完整表結(jié)構(gòu)和數(shù)據(jù)如下：
CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB
insert into t3 values(1,1),(15,15),(20,20);
在 session1 執(zhí)行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產(chǎn)生的：
1.?session1 執(zhí)行 delete ?會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內(nèi)部觀測到的：X Lock but not gap）；
2.?session2 和 session3 在執(zhí)行 insert 的時候，由于唯一約束檢測發(fā)生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區(qū)間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進(jìn)入等待；
3.?session1 在執(zhí)行 commit 后，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；
4.?session2 和 session3 繼續(xù)執(zhí)行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現(xiàn)了，并且由于插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。
死鎖日志如下：
請點擊輸入圖片描述
INSERT INTENTION LOCK
在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。
但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設(shè)存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務(wù)在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務(wù)不會互相沖突等待。
當(dāng)插入一條記錄時，會去檢查當(dāng)前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進(jìn)入等待狀態(tài)（插入意向鎖之間并不互斥）?？偨Y(jié)一下這把鎖的屬性：
1. 它不會阻塞其他任何鎖；
2. 它本身僅會被 gap lock 阻塞。
在學(xué)習(xí) MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...
GAP LOCK
在此例中，另外一個重要的點就是 gap lock，通常情況下我們說到 gap lock 都只會聯(lián)想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發(fā)生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。
通過下面這個例子就能驗證：
請點擊輸入圖片描述
這里 session1 插入數(shù)據(jù)遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 并不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當(dāng) session2 插入遇到唯一沖突但是因為被 X Lock 阻塞，并不會立刻報錯 “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。
有個困惑很久的疑問：出現(xiàn)唯一沖突需要加 S Next-Key Lock 是事實，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實現(xiàn)的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點希望能與大家一起討論得到好的解釋。
如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：
普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到后1條記錄的左閉右開區(qū)間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區(qū)間。
對于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：
1. 在絕大部分的業(yè)務(wù)場景下，都可以把 MySQL 的隔離界別設(shè)置為 READ-COMMITTED；
2. 在業(yè)務(wù)方便控制字段值唯一的情況下，盡量減少表中唯一索引的數(shù)量。
鎖沖突矩陣
前面我們說的 GAP LOCK 其實是鎖的屬性，另外我們知道 InnoDB 常規(guī)鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對我們分析死鎖很有幫助：
請點擊輸入圖片描述
重慶分公司
重慶分公司
