鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。鎖保證資料併發訪問的一致性、有效性；鎖衝突也是影響資料庫併發訪問效能的一個重要因素。鎖是Mysql在伺服器層和儲存引擎層的的併發控制。
加鎖是消耗資源的，鎖的各種操作，包括獲得鎖、檢測鎖是否是否已解除、釋放鎖等。
共享鎖與排他鎖
- 共享鎖（讀鎖）：其他事務可以讀，但不能寫。
- 排他鎖（寫鎖） ：其他事務不能讀取，也不能寫。
MySQL 不同的儲存引擎支援不同的鎖機制，所有的儲存引擎都以自己的方式顯現了鎖機制，伺服器層完全不瞭解儲存引擎中的鎖實現：
1.MyISAM 儲存引擎採用的是表級鎖（table-level locking）
2.InnoDB 儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖
3.預設情況下，表鎖和行鎖都是自動獲得的， 不需要額外的命令
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，併發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高
MyISAM表級鎖模式：
表共享讀鎖 （Table Read Lock）：不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；
表獨佔寫鎖 （Table Write Lock）：會阻塞其他使用者對同一表的讀和寫操作；
MyISAM 表的讀操作與寫操作之間，以及寫操作之間是序列的。當一個執行緒獲得對一個表的寫鎖後， 只有持有鎖的執行緒可以對錶進行更新操作。 其他執行緒的讀、 寫操作都會等待，直到鎖被釋放為止。
預設情況下，寫鎖比讀鎖具有更高的優先順序：當一個鎖釋放時，這個鎖會優先給寫鎖佇列中等候的獲取鎖請求，然後再給讀鎖佇列中等候的獲取鎖請求。
這也正是 MyISAM 表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。同時，一些需要長時間執行的查詢操作，也會使寫執行緒“餓死” ，應用中應儘量避免出現長時間執行的查詢操作（在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解” ，使每一步查詢都能在較短時間完成，從而減少鎖衝突。如果複雜查詢不可避免，應儘量安排在資料庫空閒時段執行，比如一些定期統計可以安排在夜間執行）
可以設定改變讀鎖和寫鎖的優先順序：
通過指定啟動引數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連線發出的更新請求優先順序降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
給系統引數max_write_lock_count設定一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先順序降低，給讀程式一定獲得鎖的機會。
MyISAM加鎖方法：
MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的表加讀鎖，在執行更新操作
（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預，因此，使用者一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。
在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現死鎖（Deadlock Free）的原因。
檢視錶級鎖爭用情況：
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變數來分析系統上的表鎖的爭奪，如果 Table_locks_waited 的值比較高，則說明存在著較嚴重的表級鎖爭用情況：
Table_locks_immediate：能夠立即獲得表級鎖的鎖請求次數
Table_locks_waited：不能立即獲取表級鎖而需要等待的鎖請求次數
如果Table_locks_immediate / Table_locks_waited > 5000，最好採用InnoDB引擎，
因為InnoDB是行鎖而MyISAM是表鎖，對於高併發寫入的應用InnoDB效果會好些！
MySQL的nnoDB鎖機制
InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，innodb正常的select ID from table where id=1；不會上任何鎖，接下來詳細討論InnoDB的鎖問題;
一：InnoDB行鎖的介紹
共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖,也就是我讀取的行，你不能修改，但是大家都可以讀這一行，其他人也可以讀表裡面的其他資料或者操作其他的行資料；
排他鎖（X)：允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖。也就是我更新的行，不允許其他的事務讀取和更新相同的行；
另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務打算給資料行加行共享鎖，事務在給一個資料行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給資料行加行排他鎖，事務在給一個資料行加排他鎖前必須先取得該表的IX鎖。
意向鎖是InnoDB自動加的，不需使用者干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖（X)；對於普通SELECT語句，InnoDB不會加任何鎖；
關於innodb鎖機制，實現原理：
InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在資料塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！ 索引分為主鍵索引和二級索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了二級索引，MySQL會先鎖定該二級索引，再鎖定相關的主鍵索引InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在資料塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！ 索引分為主鍵索引和二級索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了二級索引，MySQL會先鎖定該二級索引，再鎖定相關的主鍵索引
關於innodb鎖機制需要注意的是：
1）InnoDB行鎖是通過給索引項加鎖實現的，如果沒有索引，InnoDB會通過隱藏的聚簇索引來對記錄加鎖。也就是說：如果不通過索引條件檢索資料，那麼InnoDB將對錶中所有資料加鎖，實際效果跟表鎖一樣。
2）由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。說白了就是，where id=1 for update 會鎖定所有id=1的資料行，如果是where id=1 and name=’liuwenhe’ for update,這樣會把所有 id=1以及所有name=’liuwenhe’的行都上排它鎖；
3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖。
4）即便在條件中使用了索引欄位，但是否使用索引來檢索資料是由MySQL優化器通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，或者飲食轉換，或者like百分號在前等等，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。4）即便在條件中使用了索引欄位，但是否使用索引來檢索資料是由MySQL優化器通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，或者飲食轉換，或者like百分號在前等等，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。
可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits：當前鎖等待的數量
Innodb_row_lock_time：自系統啟動到現在，鎖定的總時間，單位：毫秒 ms。
Innodb_row_lock_time_avg：平均鎖定的時間，單位：毫秒 ms。
Innodb_row_lock_time_max：最大鎖定時間，單位：毫秒 ms。
Innodb_row_lock_waits：自系統啟動到現在，鎖等待次數，即鎖定的總次數。
如果發現鎖爭用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高
本作品採用《CC 協議》，轉載必須註明作者和本文連結