最近一段時間處理了較多鎖的問題，包括鎖等待導致業(yè)務連接堆積或超時，死鎖導致業(yè)務失敗等，這類問題對業(yè)務可能會造成嚴重的影響，沒有處理經(jīng)驗的用戶往往無從下手。下面將從整個數(shù)據(jù)庫設計，開發(fā)，運維階段介紹如何避免鎖問題的發(fā)生，提供一些最佳實踐供讀者參考。

最近服務器上經(jīng)常出現(xiàn)mysql進程占CPU100%的情況，使用show processlist命令后，看到出現(xiàn)了很多狀態(tài)為LOCKED的sql。使用show status like 'table%'檢查Table_locks_immediate和Table_locks_waited，發(fā)現(xiàn)Table_locks_waited偏 大。出問題的表是MyISAM，分析大概是MyISAM的鎖表導致。

MyISAM適合于讀頻率遠大于寫頻率這一情況。而我目前的應用可能會出現(xiàn)在某一時段讀寫頻率相當。大致如下：

• 一個客戶端發(fā)出需要長時間運行的SELECT

• 其他客戶端在同一個表上發(fā)出INSERT或者UPDATE，這個客戶將等待SELECT完成

• 另一個客戶在同一個表上發(fā)出另一個SELECT；因UPDATE或INSERT比SELECT有更高有優(yōu)先級，該SELECT將等待UPDATE或INSERT完成，也將等待第一個SELECT完成

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

解決方案大概有如下幾種：

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

• 0 不允許并發(fā)操作

• 1 如果MyISAM表中沒有空洞（即表的中間沒有被***的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。

• 2 無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄

1. 使用--low-priority-updates啟用mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優(yōu)先級。在這種情況下，在先前情形的最后的SELECT語句將在INSERT語句前執(zhí)行。

2. 為max_write_lock_count設置一個低值，使得在一定數(shù)量的WRITE鎖定后，給出READ鎖定

3. 使用LOW_PRIORITY屬性給于一個特定的INSERT，UPDATE或DELETE較低的優(yōu)先級

4. 使用HIGH_PRIORITY屬性給于一個特定的SELECT

5. 使用INSERT DELAYED語句

設計階段

在數(shù)據(jù)庫設計階段，引擎選擇和索引設計不當可能導致后期業(yè)務上線后出現(xiàn)較為嚴重的鎖或者死鎖問題。

1. 表引擎選擇使用myisam，引發(fā)table level lock wait。

從5.5版本開始，MySQL官方就把默認引擎由myisam轉(zhuǎn)為innodb，這兩種引擎的主要區(qū)別：

由于myisam引擎只支持table lock，在使用myisam引擎表過程中，當數(shù)據(jù)庫中出現(xiàn)執(zhí)行時間較長的查詢后就會堵塞該表上的更新動作，所以經(jīng)常會碰到線程會話處于表級鎖等待（Waiting for table level lock）的情況，嚴重的情況下會出現(xiàn)由于實例連接數(shù)被占滿而應用無法正常連接的情況

從上述的案例中可以看到，t_myisam表為myisam存儲引擎，當該表上有執(zhí)行時間較長的查詢語句在執(zhí)行的時候，該表上其他的更新全被堵塞住了，這個時候應用或者數(shù)據(jù)庫的連接很快耗完，導致應用請求失敗。這個時候快速的恢復方法為將線程id：111 kill掉即可（可以執(zhí)行show processlist查看到當前數(shù)據(jù)庫所有連接狀態(tài)）。另外myisam存儲引擎的表索引在實例異常關閉的情況下會導致索引損壞，這個時候必須要對表進行repair操作，該操作同樣會阻塞該表上的所有請求。

2. 表索引設計不當，導致數(shù)據(jù)庫出現(xiàn)死鎖。

索引設計是數(shù)據(jù)庫設計非常重要的一環(huán)，不僅僅關系到后續(xù)業(yè)務的性能，如果設計不當還可導致業(yè)務上的死鎖。

MySQL官方也已經(jīng)確認了此bug：77209，解決方法有兩種：

• 加test_id + group_id的組合索引，這樣就可以避免掉index merge；

• 將優(yōu)化器的index merge優(yōu)化關閉。

開發(fā)階段

事務處理時間過長，導致并發(fā)出現(xiàn)鎖等待。

并發(fā)事務處理在數(shù)據(jù)庫中經(jīng)?？吹降膽脠鼍?，在這種場景下，需要避免大事務，長事務，復雜事務導致事務在數(shù)據(jù)庫中的運行時間加長，事務時間變長則導致事務中鎖的持有時間變長，影響整體的數(shù)據(jù)庫吞吐量。

問題排查

1. 通過show processlist確定出現(xiàn)鎖等待的線程以及SQL信息:

2. 通過innodb的information_schema數(shù)據(jù)庫中的鎖等待以及事務試圖，查出相關的鎖信息：

3. 從提前打開數(shù)據(jù)庫的general log中查找到上述被blcok 的update語句在日志中的位置，發(fā)現(xiàn)了update被blcok的根本原因： 日志中有兩條不同的SQL同時并發(fā)更新同一條記錄，所以后更新的SQL會等待前更新的SQL，如果SQL1所在的事務1直沒有提交，那么事務2將會一直等待，這樣就出現(xiàn)上述updating的狀態(tài)

維護階段

DDL操作被大查詢block。

當應用上線進入維護階段，則開始會有較多的數(shù)據(jù)庫變更操作，比如：添加字段，添加索引等操作，這一類操作導致的鎖故障也是非常頻繁的，下面將會介紹一則案例，一個DDL操作被查詢block，導致數(shù)據(jù)庫連接堆積：

Query |6 | User sleep                       | select id ,sleep(50) from t
Query |4 | Waiting for table metadata lock  | alter table t add column gmt_create datetime
Query |2 | Waiting for table metadata lock  | select * from t where id=1
Query |1 | Waiting for table metadata lock  | select * from t where id=2
Query |1 | Waiting for table metadata lock  | update t set id =2 where id=1

Metadata lock wait 的含義：為了在并發(fā)環(huán)境下維護表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務（顯式或隱式）的時候，不可以對元數(shù)據(jù)進行寫入操作。因此 MySQL 引入了 metadata lock ，來保護表的元數(shù)據(jù)信息。因此在對表進行上述操作時，如果表上有活動事務（未提交或回滾），請求寫入的會話會等待在 Metadata lock wait。

導致 Metadata lock wait 等待的常見因素包括：活動事務，當前有對表的長時間查詢，顯示或者隱式開啟事務后未提交或回滾，比如查詢完成后未提交或者回滾，表上有失敗的查詢事務等。

上述案例中，查詢，更新和DDL操作的線程狀態(tài)都為Waiting for table metadata lock，對表t的操作全部被阻塞，前端源源不斷的請求到達數(shù)據(jù)庫，這個時候數(shù)據(jù)庫的連接很容易被打滿，那我們來分析一下為什么有這這些鎖等待：

• alter 操作的鎖等待：由于在表t上做了一個添加字段的操作，該操作會在結(jié)束前對表獲取一個metadata lock，但是該表上已經(jīng)有一個查詢一直未結(jié)束，導致metadata lock一直獲取不到，所以添加字段操作只能等待查詢結(jié)束，這就解釋了alter操作為什么狀態(tài)為Waiting for table metadata lock。

• 查詢和更新的鎖等待：由于前面進行的alter操作已經(jīng)在T表上試圖獲取metadata lock，所以后續(xù)對表T的的查詢和更新操作在獲取metadata lock的時候會被alter操作所阻塞，進而導致這些線程狀態(tài)為Waiting for table metadata lock。

解決辦法則是將線程6 kill 掉即可，更加友好的方式為：控制session會話等待meta data lock的超時時間，執(zhí)行DDL操作前，set session lock_wait_timeout = 10 //可根據(jù)需要設置，即使改session獲取不到meta data lock 鎖，最多也就阻塞數(shù)據(jù)庫10秒鐘,10秒鐘之后，會話將自動超時退出，而后面的DML將能夠繼續(xù)得到執(zhí)行，從而有效的降低了因為meta data lock而導致的數(shù)據(jù)庫表的死鎖的風險。

總結(jié)

鎖問題是非常常見的問題，需要我們在數(shù)據(jù)庫開發(fā)、設計、管理的各個階段都需要注意，防范未然，做到心中有數(shù)。

設計開發(fā)階段:

1. 表設計要避免使用myisam存儲引擎，改用innodb引擎；

2. 為SQL創(chuàng)建合適的索引，避免多個單列索引執(zhí)行出錯；

3. 避免大事務，長事務，復雜事務導致事務在數(shù)據(jù)庫中的運行時間加長。

管理運維階段：

1. 在業(yè)務低峰期執(zhí)行上述操作，比如創(chuàng)建刪除索引；

2. 在結(jié)構(gòu)變更前，觀察數(shù)據(jù)庫中是否存在長時間運行的SQL，未提交的事務；

3. 結(jié)構(gòu)變更期間，監(jiān)控數(shù)據(jù)庫的線程狀態(tài)是否存在lock wait。

并發(fā)插入(Concurrent Inserts)

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

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

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

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

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

在如表20-4所示的例子中，session_1獲得了一個表的READ LOCAL鎖，該線程可以對表進行查詢操作，但不能對表進行更新操作；其他的線程(session_2)，雖然不能對表進行刪除和更新操作，但卻可以對該表進行并發(fā)插入操作，這里假設該表中間不存在空洞。

可以利用MyISAM存儲引擎的并發(fā)插入特性，來解決應用中對同一表查詢和插入的鎖爭用。例如，將concurrent_insert系統(tǒng)變量設為2，總是允許并發(fā)插入；同時，通過定期在系統(tǒng)空閑時段執(zhí)行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞。有關OPTIMIZE TABLE語句的詳細介紹，可以參見第18章中“兩個簡單實用的優(yōu)化方法”一節(jié)的內(nèi)容。

MyISAM的鎖調(diào)度

前面講過，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。那么，一個進程請求某個 MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般比讀請求要重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調(diào)節(jié)MyISAM 的調(diào)度行為。

通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認給予讀請求以優(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)先的方法，但還是可以用其來解決查詢相對重要的應用(如用戶登錄系統(tǒng))中，讀鎖等待嚴重的問題。

另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進程一定獲得鎖的機會。上面已經(jīng)討論了寫優(yōu)先調(diào)度機制帶來的問題和解決辦法。這里還要強調(diào)一點：一些需要長時間運行的查詢操作，也會使寫進程“餓死”！因此，應用中應盡量避免出現(xiàn)長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題，因為這種看似巧妙的SQL語句，往往比較復雜，執(zhí)行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復雜查詢不可避免，應盡量安排在數(shù)據(jù)庫空閑時段執(zhí)行，比如一些定期統(tǒng)計可以安排在夜間執(zhí)行。

mysql中update的low_priority解決并發(fā)問題

在處理訪客信息更新是遇到了大并發(fā)的問題，low_priority，低優(yōu)先級，可以讓并發(fā)沒那么占CPU，對于低配VPS來說，作用還是很大的。
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...

mysql中update用low_priority讓update不鎖定表

MySQL允許你改變語句調(diào)度的優(yōu)先級，它可以使來自多個客戶端的查詢更好地協(xié)作，這樣單個客戶端就不會由于鎖定而等待很長時間。改變優(yōu)先級還可以確保特定類型的查詢被處理得更快。這一部分講解MySQL的默認的調(diào)度策略和可以用來影響這些策略的選項。它還談到了并發(fā)性插入操作的使用和存儲引擎鎖定層次對客戶端的并發(fā)性的影響。為了討論的方便，我們把執(zhí)行檢索（SELECT）的客戶端稱為"讀取者"，把執(zhí)行修改操作（DELETE、INSERT、REPLACE或UPDATE）的客戶端稱為"寫入者"。

(在不要求實時的情況下，寫入可以降低跟新插入操作的優(yōu)先級，確保讀的速度。當然，也有更好的方法來完成這個目的，主從，中間緩存，都可以。)

MySQL的默認的調(diào)度策略可用總結(jié)如下：

· 寫入操作優(yōu)先于讀取操作。

· 對某張數(shù)據(jù)表的寫入操作某一時刻只能發(fā)生一次，寫入請求按照它們到達的次序來處理。

· 對某張數(shù)據(jù)表的多個讀取操作可以同時地進行。

MyISAM和MEMORY存儲引擎借助于數(shù)據(jù)表鎖來實現(xiàn)這樣的調(diào)度策略。當客戶端訪問某張表的時候，首先必須獲取它的鎖。當客戶端完成對表的操作的時候，鎖就會被解除。通過LOCK TABLES和UNLOCK TABLES語句來顯式地獲取或釋放鎖是可行的，但是在通常情況下，服務器的鎖管理器會自動地在需要的時候獲取鎖，在不再需要的時候釋放鎖。獲取的鎖的類型依賴于客戶端是寫入還是讀取操作。

對某張表進行寫入操作的客戶端必須擁有獨占的（排他的）訪問權(quán)的鎖。操作在進行的過程中，該數(shù)據(jù)表處于不一致的（inconsistent）狀態(tài)，因為數(shù)據(jù)記錄在刪除、添加或修改的時候，數(shù)據(jù)表上的索引也可能需要更新以相互匹配。這個數(shù)據(jù)表在變化的過程中，如果允許其它的客戶端訪問，會出現(xiàn)問題。非常明顯，允許兩個客戶端同時寫入一張數(shù)據(jù)表是不利的，因為這樣的操作會很快使數(shù)據(jù)表中的信息成為一堆無用的垃圾。但是允許客戶端讀取變化之中的數(shù)據(jù)表也不好，因為正在讀取的位置中的數(shù)據(jù)可能正在變化（修改），讀取的結(jié)果可能不是真實的。

對某張表執(zhí)行讀取操作的客戶端必須獲取一個鎖，防止在讀取的過程中，其它的客戶端寫入或改變表。但是這個鎖不需要獨占的訪問權(quán)。讀取操作不會改變數(shù)據(jù)，因此沒有理由讓某個讀取者阻止其它的讀取者訪問這張表。因此讀取鎖允許其它的客戶端在同一時刻讀取這張表。

MySQL提供了幾個語句調(diào)節(jié)符，允許你修改它的調(diào)度策略：

· LOW_PRIORITY關鍵字應用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

· HIGH_PRIORITY關鍵字應用于SELECT和INSERT語句。

· DELAYED關鍵字應用于INSERT和REPLACE語句。

LOW_PRIORITY和HIGH_PRIORITY調(diào)節(jié)符影響那些使用數(shù)據(jù)表鎖的存儲引擎（例如MyISAM和MEMORY）。DELAYED調(diào)節(jié)符作用于MyISAM和MEMORY數(shù)據(jù)表。

改變語句調(diào)度的優(yōu)先級

LOW_PRIORITY關鍵字影響DELETE、INSERT、LOAD DATA、REPLACE和UPDATE語句的執(zhí)行調(diào)度。通常情況下，某張數(shù)據(jù)表正在被讀取的時候，如果有寫入操作到達，那么寫入者一直等待讀取者完成操作（查詢開始之后就不能中斷，因此允許讀取者完成操作）。如果寫入者正在等待的時候，另一個讀取操作到達了，該讀取操作也會被阻塞（block），因為默認的調(diào)度策略是寫入者優(yōu)先于讀取者。當?shù)谝粋€讀取者完成操作的時候，寫入者開始操作，并且直到該寫入者完成操作，第二個讀取者才開始操作。

如果寫入操作是一個LOW_PRIORITY（低優(yōu)先級）請求，那么系統(tǒng)就不會認為它的優(yōu)先級高于讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那么就允許第二個讀取者插到寫入者之前。只有在沒有其它的讀取者的時候，才允許寫入者開始操作。理論上，這種調(diào)度修改暗示著，可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。如果前面的讀取操作在進行的過程中一直有其它的讀取操作到達，那么新的請求都會插入到LOW_PRIORITY寫入操作之前。

SELECT查詢的HIGH_PRIORITY（高優(yōu)先級）關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優(yōu)先級更高。另外一種影響是，高優(yōu)先級的SELECT在正常的SELECT語句之前執(zhí)行，因為這些語句會被寫入操作阻塞。

如果你希望所有支持LOW_PRIORITY選項的語句都默認地按照低優(yōu)先級來處理，那么請使用--low-priority-updates選項來啟動服務器。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優(yōu)先級，可以消除該選項對單個INSERT語句的影響。

• MySQL
• MyISAM
• 鎖表