本文目錄一覽：
關於MySQL中的表鎖和行鎖
mysql行鎖和表鎖
鎖是計算機協調多個進程或純線程並發訪問某一資源的機制。在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所在有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。
概述
相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。
MySQL大致可歸納為以下3種鎖：
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般
MySQL表級鎖的鎖模式（MyISAM)
MySQL表級鎖有兩種模式：表共享鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）。
對MyISAM的讀操作，不會阻塞其他用戶對同一表請求，但會阻塞對同一表的寫請求；
對MyISAM的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；
MyISAM表的讀操作和寫操作之間，以及寫操作之間是串列的。
當一個線程獲得對一個表的寫鎖後，只有持有鎖線程可以對錶進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
MySQL表級鎖的鎖模式
MySQL的表鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）。鎖模式的兼容如下表
MySQL中的表鎖兼容性
當前鎖模式/是否兼容/請求鎖模式
讀鎖 是 是 否
寫鎖 是 否 否
可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串列的！（當一線程獲得對一個表的寫鎖後，只有持有鎖的線程可以對錶進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。）
如何加表鎖
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，並非必須如此。
給MyISAM表顯示加鎖，一般是為了一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。
要特別說明以下兩點內容。
上面的例子在LOCK TABLES時加了『local』選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾插入記錄
在用LOCKTABLES給表顯式加表鎖是時，必須同時取得所有涉及表的鎖，並且MySQL支持鎖升級。也就是說，在執行LOCK TABLES後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本如此，MySQL問題一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因
一個session使用LOCK TABLE 命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖等待。
當使用LOCK TABLE時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖多少次，否則也會出錯！
並發鎖
在一定條件下，MyISAM也支持查詢和操作的並發進行。
MyISAM存儲引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
當concurrent_insert設置為0時，不允許並發插入。
當concurrent_insert設置為1時，如果MyISAM允許在一個讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾插入記錄，都允許在表尾並發插入記錄。
可以利用MyISAM存儲引擎的並發插入特性，來解決應用中對同一表查詢和插入鎖爭用。例如，將concurrent_insert系統變數為2，總是允許並發插入；同時，通過定期在系統空閑時段執行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產生的中間空洞。
MyISAM的鎖調度
前面講過，MyISAM存儲引擎的讀和寫鎖是互斥，讀操作是串列的。那麼，一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀進程先請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀請求之前！這是因為MySQL認為寫請求一般比讀請求重要。這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調節MyISAM的調度行為。
通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先順序降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
雖然上面3種方法都是要麼更新優先，要麼查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如用戶登錄系統）中，讀鎖等待嚴重的問題。
另外，MySQL也提供了一種折中的辦法來調節讀寫衝突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL變暫時將寫請求的優先順序降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制和解決辦法。這裡還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程「餓死」！因此，應用中應盡量避免出現長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題。因為這種看似巧妙的SQL語句，往往比較複雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的「分解」，使每一步查詢都能在較短時間完成，從而減少鎖衝突。如果複雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行。
InnoDB鎖問題
InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）；二是採用了行級鎖。
行級鎖和表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。
1.事務（Transaction）及其ACID屬性
事務是由一組SQL語句組成的邏輯處理單元，事務具有4屬性，通常稱為事務的ACID屬性。
原性性（Actomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以操持完整性；事務結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
持久性（Durable）：事務完成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。
2.並發事務帶來的問題
相對於串列處理來說，並發事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支持可以支持更多的用戶。但並發事務處理也會帶來一些問題，主要包括以下幾種情況。
更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題——最後的更新覆蓋了其他事務所做的更新。例如，兩個編輯人員製作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文檔。最後保存其更改保存其更改副本的編輯人員覆蓋另一個編輯人員所做的修改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題
臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務並提交前，這條記錄的數據就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」的數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現象被形象地叫做「臟讀」。
不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些數據已經發生了改變、或某些記錄已經被刪除了！這種現象叫做「不可重複讀」。
幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」。
3.事務隔離級別
在並發事務處理帶來的問題中，「更新丟失」通常應該是完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
「臟讀」、「不可重複讀」和「幻讀」，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本可以分為以下兩種。
一種是在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
另一種是不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot），並用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度，好像是資料庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。
資料庫的事務隔離級別越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串列化」進行，這顯然與「並發」是矛盾的，同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對「不可重複讀」和「幻讀」並不敏感，可能更關心數據並發訪問的能力。
為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己業務邏輯要求，通過選擇不同的隔離級別來平衡＂隔離＂與＂並發＂的矛盾
事務4種隔離級別比較
隔離級別/讀數據一致性及允許的並發副作用 讀數據一致性 臟讀 不可重複讀 幻讀
未提交讀（Read uncommitted）
最低級別，只能保證不讀取物理上損壞的數據 是 是 是
已提交度（Read committed） 語句級 否 是 是
可重複讀（Repeatable read） 事務級 否 否 是
可序列化（Serializable） 最高級別，事務級 否 否 否
最後要說明的是：各具體資料庫並不一定完全實現了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標準級別，另外還自己定義的Read only隔離級別：SQL Server除支持上述ISO/ANSI SQL92定義的4個級別外，還支持一個叫做＂快照＂的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。MySQL支持全部4個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級下是採用MVCC一致性讀，但某些情況又不是。
獲取InonoD行鎖爭用情況
可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
如果發現爭用比較嚴重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設置InnoDB Monitors來進一步觀察發生鎖衝突的表、數據行等，並分析鎖爭用的原因。
InnoDB的行鎖模式及加鎖方法
InnoDB實現了以下兩種類型的行鎖。
共享鎖（s）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
排他鎖（X）：允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的數據集共享讀鎖和排他寫鎖。
另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務打算給數據行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給數據行加排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
InnoDB行鎖模式兼容性列表
如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB就請求的鎖授予該事務；反之，如果兩者兩者不兼容，該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的，不需用戶干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排鎖。
共享鎖（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE … FOR UPDATE
用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數據依存關係時確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT … FOR UPDATE方式獲取排他鎖。
InnoDB行鎖實現方式
InnoDB行鎖是通過索引上的索引項來實現的，這一點MySQL與Oracle不同，後者是通過在數據中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味者：只有通過索引條件檢索數據，InnoDB才會使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發性能。
什麼時候使用表鎖
對於InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務中，也可以考慮使用表級鎖。
第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖衝突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。
第二種情況是：事務涉及多個表，比較複雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少資料庫因事務回滾帶來的開銷。
當然，應用中這兩種事務不能太多，否則，就應該考慮使用MyISAM表。
在InnoDB下 ，使用表鎖要注意以下兩點。
（1）使用LOCK TALBES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層MySQL Server負責的，僅當autocommit=0、innodb_table_lock=1（默認設置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測並處理這種死鎖。
（2）在用LOCAK TABLES對InnoDB鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK產不能釋放用LOCAK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句。
關於死鎖
MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。但是在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了InnoDB發生死鎖是可能的。
發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並退回，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在並發訪問比較高的情況下，如果大量事務因無法立即獲取所需的鎖而掛起，會佔用大量計算機資源，造成嚴重性能問題，甚至拖垮資料庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。
通常來說，死鎖都是應用設計的問題，通過調整業務流程、資料庫對象設計、事務大小、以及訪問資料庫的SQL語句，絕大部分都可以避免。下面就通過實例來介紹幾種死鎖的常用方法。
（1）在應用中，如果不同的程序會並發存取多個表，應盡量約定以相同的順序為訪問表，這樣可以大大降低產生死鎖的機會。如果兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可能避免。
（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。
（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應該先申請共享鎖，更新時再申請排他鎖，甚至死鎖。
（4）在REPEATEABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT…ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這麼做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可以避免問題。
（5）當隔離級別為READ COMMITED時，如果兩個線程都先執行SELECT…FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第1個線程提交後，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。
儘管通過上面的設計和優化等措施，可以大減少死鎖，但死鎖很難完全避免。因此，在程序設計中總是捕獲並處理死鎖異常是一個很好的編程習慣。
如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因和改進措施。
總結
對於MyISAM的表鎖，主要有以下幾點
（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串列的。
（2）在一定條件下，MyISAM允許查詢和插入並發執行，我們可以利用這一點來解決應用中對同一表和插入的鎖爭用問題。
（3）MyISAM默認的鎖調度機制是寫優先，這並不一定適合所有應用，用戶可以通過設置LOW_PRIPORITY_UPDATES參數，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調節讀寫鎖的爭用。
（4）由於表鎖的鎖定粒度大，讀寫之間又是串列的，因此，如果更新操作較多，MyISAM表可能會出現嚴重的鎖等待，可以考慮採用InnoDB表來減少鎖衝突。
對於InnoDB表，主要有以下幾點
（1）InnoDB的行銷是基於索引實現的，如果不通過索引訪問數據，InnoDB會使用表鎖。
（2）InnoDB間隙鎖機制，以及InnoDB使用間隙鎖的原因。
（3）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。
（4）MySQL的恢復和複製對InnoDB鎖機制和一致性讀策略也有較大影響。
（5）鎖衝突甚至死鎖很難完全避免。
在了解InnoDB的鎖特性後，用戶可以通過設計和SQL調整等措施減少鎖衝突和死鎖，包括：
盡量使用較低的隔離級別
精心設計索引，並盡量使用索引訪問數據，使加鎖更精確，從而減少鎖衝突的機會。
選擇合理的事務大小，小事務發生鎖衝突的幾率也更小。
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖。
不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大減少死鎖的機會。
盡量用相等條件訪問數據，這樣可以避免間隙鎖對並發插入的影響。
不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖。
對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能
MySQL資料庫表被鎖、解鎖，刪除事務
在程序員的職業生涯中，總會遇到資料庫表被鎖的情況，前些天就又撞見一次。由於業務突發需求，各個部門都在批量操作、導出數據，而資料庫又未做讀寫分離，結果就是：資料庫的某張表被鎖了！
用戶反饋系統部分功能無法使用，緊急排查，定位是資料庫表被鎖，然後進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。
用戶反饋某功能頁面報502錯誤，於是第一時間看服務是否正常，資料庫是否正常。在控制台看到資料庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是資料庫層出現問題了。
查看阻塞事務列表，發現其中有鎖表現象，本想利用控制台直接結束掉阻塞的事務，但控制台賬號許可權有限，於是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。
下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應？
想像一個場景，當然也是軟體工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然資料庫的表被鎖了，業務無法正常運轉，那麼我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物？
首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——「重啟」。至於後果如何，你能不能跑了，要你自己三思而後行了！
重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。
下面來看看不用跑路的解決方案：
遇到資料庫阻塞問題，首先要查詢一下表是否在使用。
如果查詢結果為空，那麼說明表沒在使用，說明不是鎖表的問題。
如果查詢結果不為空，比如出現如下結果：
則說明表（test）正在被使用，此時需要進一步排查。
查看資料庫當前的進程，看看是否有慢SQL或被阻塞的線程。
執行命令：
該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。
在上述實踐中，阿里雲控制台之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的資料庫賬號，無法操作另外一個用戶的線程。
如果情況緊急，此步驟可以跳過，主要用來查看核對：
如果情況緊急，此步驟可以跳過，主要用來查看核對：
看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。
搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。
執行kill命令：
對應的線程都執行完kill命令之後，後續事務便可正常處理。
針對緊急情況，通常也會直接操作第一、第二、第六步。
這裡再補充一些MySQL鎖相關的知識點：資料庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問的時候，資料庫需要合理地控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。
根據加鎖的範圍，MySQL裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。
表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對於InnoDB來說，一般會採用行級鎖。畢竟鎖住整張表影響範圍太大了。
另外一個表級鎖是MDL（metadata lock），用於並發情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。
常見的一種鎖表場景就是有事務操作處於：Waiting for table metadata lock狀態。
MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待場景。
一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態，後續對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的後果。
通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨佔鎖，會進行等待。
通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨佔鎖。
處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然後kill掉，讓其回滾。
通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中，對錶進行了一個失敗的操作（比如查詢了一個不存在的欄位），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。
處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。
總之，alter table的語句是很危險的（核心是未提交事務或者長事務導致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。
如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。
關於MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。
MySQL從入門到精通(九) MySQL鎖，各種鎖
鎖是計算機協調多個進程或線程並發訪問某一資源的機制，在資料庫中，除傳統的計算資源（CPU、RAM、I/O）爭用外，數據也是一種供許多用戶共享的資源，如何保證數據並發訪問的一致性，有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素，從這個角度來說，鎖對資料庫而言是尤其重要，也更加複雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定資料庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應的行數據。
全局鎖就是對整個資料庫實例加鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。但是對資料庫加全局鎖是有弊端的，如在主庫上備份，那麼在備份期間都不能執行更新，業務會受影響，第二如果是在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進位日誌，會導致主從延遲。
解決辦法是在innodb引擎中，備份時加上–single-transaction參數來完成不加鎖的一致性數據備份。
添加全局鎖: flush tables with read lock; 解鎖 unlock tables。
表級鎖,每次操作會鎖住整張表.鎖定粒度大,發送鎖衝突的概率最高,並發讀最低,應用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數據鎖(meta data lock, MDL)和意向鎖。
表鎖又分為: 表共享讀鎖 read lock、表獨佔寫鎖write lock
語法: 1、加鎖 lock tables 表名 … read/write
2、釋放鎖 unlock tables 或者關閉客戶端連接
注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。
元數據鎖,在加鎖過程中是系統自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作。為了避免DML和DDL衝突，保證讀寫的正確性。
在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當對錶結構進行變更操作時，加MDL寫鎖(排他).
查看元數據鎖:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向鎖，為了避免DML在執行時，加的行鎖與表鎖的衝突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖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;
行級鎖，每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖衝突的概率最高，並發讀最高，應用在innodb存儲引擎中。
innodb的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖，對於行級鎖，主要分為以下三類:
1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。
2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產生幻讀，在RR隔離級別下都支持。
3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙Gap,在RR隔離級別下支持。
innodb實現了以下兩種類型的行鎖
1、共享鎖 S: 允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
2、排他鎖 X: 允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。
insert 語句 排他鎖 自動添加的
update語句 排他鎖 自動添加
delete 語句 排他鎖 自動添加
select 正常查詢語句 不加鎖 。。。
select 。。。lock in share mode 共享鎖 需要手動在select 之後加lock in share mode
select 。。。for update 排他鎖 需要手動在select之後添加for update
默認情況下，innodb在repeatable read事務隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。
間隙鎖唯一目的是防止其它事務插入間隙，間隙鎖可以共存，一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用的間隙鎖。
一文詳解-MySQL 事務和鎖
當多個用戶訪問同一份數據時，一個用戶在更改數據的過程中，可能有其他用戶同時發起更改請求，為保證資料庫記錄的更新從一個一致性狀態變為另外一個一致性狀態，使用事務處理是非常必要的，事務具有以下四個特性：
MySQL 提供了多種事務型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務。為了支持事務，InnoDB 存儲引擎引入了與事務處理相關的 REDO 日誌和 UNDO 日誌，同時事務依賴於 MySQL 提供的鎖機制
事務執行時需要將執行的事務日誌寫入日誌文件，對應的文件為 REDO 日誌。當每條 SQL 進行數據更新操作時，首先將 REDO 日誌寫進日誌緩衝區。當客戶端執行 COMMIT 命令提交時，日誌緩衝區的內容將被刷新到磁碟，日誌緩衝區的刷新方式或者時間間隔可以通過參數 innodb_flush_log_at_trx_commit 控制
REDO 日誌對應磁碟上的 ib_logifleN 文件，該文件默認為 5MB，建議設置為 512MB，以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日誌的記錄，恢復最新數據，保證已提交事務的持久性
與 REDO 日誌相反，UNDO 日誌主要用於事務異常時的數據回滾，具體內容就是記錄數據被修改前的信息到 UNDO 緩衝區，然後在合適的時間將內容刷新到磁碟
假如由於系統錯誤或者 rollback 操作而導致事務回滾，可以根據 undo 日誌回滾到沒修改前的狀態，保證未提交事務的原子性
與 REDO 日誌不同的是，磁碟上不存在單獨的 UNDO 日誌文件，所有的 UNDO 日誌均存在表空間對應的 .ibd 數據文件中，即使 MySQL 服務啟動了獨立表空間
在 MySQL 中，可以使用 BEGIN 開始事務，使用 COMMIT 結束事務，中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務
MySQL 定義了四種隔離級別，指定事務中哪些數據改變其他事務可見、哪些數據該表其他事務不可見。低級別的隔離級別可以支持更高的並發處理，同時佔用的系統資源更少
InnoDB 系統級事務隔離級別可以使用以下語句設置：
查看系統級事務隔離級別：
InnoDB 會話級事務隔離級別可以使用以下語句設置：
查看會話級事務隔離級別：
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。讀取未提交的數據稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務，在 B 事務更新但未提交之前，A 事務讀取到了更新後的數據，但由於 B 事務回滾，導致 A 事務出現了臟讀現象
所有事務只能看見已經提交事務所做的改變，此級別可以解決臟讀，但也會導致不可重複讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務，A事務讀取了 B 事務的數據，在 B 事務更新並提交後，A 事務又讀取到了更新後的數據，此時就出現了同一 A 事務中的查詢出現了不同的查詢結果
MySQL 默認的事務隔離級別，能確保同一事務的多個實例在並發讀取數據時看到同樣的數據行，理論上會導致一個問題，幻讀（Phontom Read）。例如，第一個事務對一個表中的數據做了修改，這種修改會涉及表中的全部數據行，同時第二個事務也修改這個表中的數據，這次的修改是向表中插入一行新數據，此時就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行
InnoDB 通過多版本並發控制機制（MVCC）解決了該問題：InnoDB 通過為每個數據行增加兩個隱含值的方式來實現，這兩個隱含值記錄了行的創建時間、過期時間以及每一行存儲時間發生時的系統版本號，每個查詢根據事務的版本號來查詢結果
通過強制事務排序，使其不可能相互衝突，從而解決幻讀問題。簡而言之，就是在每個讀的數據行上加上共享鎖實現，這個級別會導致大量的超時現象和鎖競爭，一般不推薦使用
為了解決資料庫並發控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對並發操作進行控制，因此產生了鎖
共享鎖的粒度是行或者元組（多個行），一個事務獲取了共享鎖以後，可以對鎖定範圍內的數據執行讀操作
排他鎖的粒度與共享鎖相同，一個事務獲取排他鎖以後，可以對鎖定範圍內的數據執行寫操作
有兩個事務 A 和 B，如果事務 A 獲取了一個元組的共享鎖，事務 B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務 A 釋放共享鎖之後。如果事務 A 獲取了一個元組的排他鎖，事務 B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之後
意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務有意對數據上共享鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行
鎖的粒度主要分為表鎖和行鎖
表鎖的開銷最小，同時允許的並發量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數據時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執行時使用的也是表鎖
行鎖可以支持最大的並發，InnoDB 存儲引擎使用該鎖機制。如果要支持並發讀/寫，建議採用 InnoDB 存儲引擎
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/279699.html
微信掃一掃 
支付寶掃一掃 