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

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

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

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

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

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

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

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

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

• 0 不允許并發操作

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

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

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

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

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

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

5. 使用INSERT DELAYED語句

設計階段

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

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

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

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

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

2. 表索引設計不當，導致數據庫出現死鎖。

索引設計是數據庫設計非常重要的一環，不僅僅關系到后續業務的性能，如果設計不當還可導致業務上的死鎖。

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

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

• 將優化器的index merge優化關閉。

開發階段

事務處理時間過長，導致并發出現鎖等待。

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

問題排查

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

2. 通過innodb的information_Schema數據庫中的鎖等待以及事務試圖，查出相關的鎖信息：

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

維護階段

DDL操作被大查詢block。

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

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

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

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

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

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

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

總結

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

設計開發階段:

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

2. 為SQL創建合適的索引，避免多個單列索引執行出錯；

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

管理運維階段：

1. 在業務低峰期執行上述操作，比如創建刪除索引；

2. 在結構變更前，觀察數據庫中是否存在長時間運行的SQL，未提交的事務；

3. 結構變更期間，監控數據庫的線程狀態是否存在lock wait。

并發插入(Concurrent Inserts)

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

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

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

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

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

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

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

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語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復雜查詢不可避免，應盡量安排在數據庫空閑時段執行，比如一些定期統計可以安排在夜間執行。

mysql中update的low_priority解決并發問題

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

mysql中update用low_priority讓update不鎖定表

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

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

MySQL的默認的調度策略可用總結如下：

· 寫入操作優先于讀取操作。

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

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

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

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

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

MySQL提供了幾個語句調節符，允許你修改它的調度策略：

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

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

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

LOW_PRIORITY和HIGH_PRIORITY調節符影響那些使用數據表鎖的存儲引擎（例如MyISAM和MEMORY）。DELAYED調節符作用于MyISAM和MEMORY數據表。

改變語句調度的優先級

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

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

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

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

• MySQL
• MyISAM
• 鎖表