獲取鎖等待情況
可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪：mysql> show status like 'Table%';+----------------------------+----------+| Variable_name | Value |+----------------------------+----------+| Table_locks_immediate | 105 || Table_locks_waited | 3 |+----------------------------+----------+2 rows in set (0.00 sec) 可以通過檢查Innodb_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：mysql> show status like 'innodb_row_lock%';+----------------------------------------+----------+| Variable_name | Value |+----------------------------------------+----------+| Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 2001 || Innodb_row_lock_time_avg | 667 || Innodb_row_lock_time_max | 845 || Innodb_row_lock_waits | 3 |+----------------------------------------+----------+5 rows in set (0.00 sec) 另外，針對Innodb類型的表，如果需要察看當前的鎖等待情況，可以設置InnoDB Monitors，然后通過Show innodb status察看，設置的方式是： CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;監(jiān)視器可以通過發(fā)出下列語句來被停止： DROP TABLE innodb_monitor;設置監(jiān)視器后，在show innodb status的顯示內(nèi)容中，會有詳細的當前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等等，便于進行進一步的分析和問題的確定。打開監(jiān)視器以后，默認情況下每15秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導致.err文件變得非常的巨大，所以我們在確認問題原因之后，要記得刪除監(jiān)控表以關閉監(jiān)視器?；蛘咄ㄟ^使用--console選項來啟動服務器以關閉寫日志文件。什么情況下使用表鎖
表級鎖在下列幾種情況下比行級鎖更優(yōu)越：很多操作都是讀表。
在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來讀取得到時：
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
SELECT 和 INSERT 語句并發(fā)的執(zhí)行，但是只有很少的 UPDATE 和 DELETE 語句。
很多的掃描表和對全表的 GROUP BY 操作，但是沒有任何寫表。

什么情況下使用行鎖
行級鎖定的優(yōu)點：當在許多線程中訪問不同的行時只存在少量鎖定沖突。
回滾時只有少量的更改。
可以長時間鎖定單一的行。

行級鎖定的缺點：比頁級或表級鎖定占用更多的內(nèi)存。
當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。
如果你在大部分數(shù)據(jù)上經(jīng)常進行GROUP BY操作或者必須經(jīng)常掃描整個表，比其它鎖定明顯慢很多。
用高級別鎖定，通過支持不同的類型鎖定，你也可以很容易地調(diào)節(jié)應用程序，因為其鎖成本小于行級鎖定。

insert …select …帶來的問題
當使用insert...select...進行記錄的插入時，如果select的表是innodb類型的，不論insert的表是什么類型的表，都會對select的表的紀錄進行鎖定。對于那些從oracle遷移過來的應用，需要特別的注意，因為oracle并不存在類似的問題，所以在oracle的應用中insert...select...操作非常的常見。例如：有時候會對比較多的紀錄進行統(tǒng)計分析，然后將統(tǒng)計的中間結果插入到另外一個表，這樣的操作因為進行的非常少，所以可能并沒有設置相應的索引。如果遷移到mysql數(shù)據(jù)庫后不進行相應的調(diào)整，那么在進行這個操作期間，對需要select的表實際上是進行的全表掃描導致的所有記錄的鎖定，將會對應用的其他操作造成非常嚴重的影響。究其主要原因，是因為mysql在實現(xiàn)復制的機制時和oracle是不同的，如果不進行select表的鎖定，則可能造成從數(shù)據(jù)庫在恢復期間插入結果集的不同，造成主從數(shù)據(jù)的不一致。如果不采用主從復制，關閉binlog并不能避免對select紀錄的鎖定，某些文檔中提到可以通過設置innodb_locks_unsafe_for_binlog來避免這個現(xiàn)象，當這個參數(shù)設置為true的時候，將不會對select的結果集加鎖，但是這樣的設置將可能帶來非常嚴重的隱患。如果使用這個binlog進行從數(shù)據(jù)庫的恢復，或者進行主數(shù)據(jù)庫的災難恢復，都將可能和主數(shù)據(jù)庫的執(zhí)行效果不同。因此，我們并不推薦通過設置這個參數(shù)來避免insert...select...導致的鎖，如果需要進行可能會掃描大量數(shù)據(jù)的insert...select操作，我們推薦使用select...into outfile和load data infile的組合來實現(xiàn)，這樣是不會對紀錄進行鎖定的。next-key鎖對并發(fā)插入的影響
在行級鎖定中，InnoDB 使用一個名為next-key locking的算法。InnoDB以這樣一種方式執(zhí)行行級鎖定：當它搜索或掃描表的索引之時，它對遇到的索引記錄設置共享或獨占鎖定。因此，行級鎖定事實上是索引記錄鎖定。InnoDB對索引記錄設置的鎖定也映像索引記錄之前的“間隙”。如果一個用戶對一個索引上的記錄R有共享或獨占的鎖定，另一個用戶 不能緊接在R之前以索引的順序插入一個新索引記錄。這個間隙的鎖定被執(zhí)行來防止所謂的“幽靈問題”?？梢杂胣ext-key鎖定在你的應用程序上實現(xiàn)一個唯一性檢查：如果你以共享模式讀數(shù)據(jù)，并且沒有看到你將要插入的行的重復，則你可以安全地插入你的行，并且知道在讀過程中對你的行的繼承者設置的next-key鎖定與此同時阻止任何人對你的行插入一個重復。因此，the next-key鎖定允許你鎖住在你的表中并不存在的一些東西。隔離級別對并發(fā)插入的影響
REPEATABLE READ是InnoDB的默認隔離級別。帶唯一搜索條件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE語句只鎖定找到的索引記錄，而不鎖定記錄前的間隙。用其它搜索條件，這些操作采用next-key鎖定，用next-key鎖定或者間隙鎖定鎖住搜索的索引范圍，并且阻止其它用戶的新插入。在持續(xù)讀中，有一個與READ COMMITTED隔離級別重要的差別：在這個級別，在同一事務內(nèi)所有持續(xù)讀讀取由第一次讀所確定的同一快照。這個慣例意味著如果你在同一事務內(nèi)發(fā)出數(shù)個無格式SELECT語句，這些SELECT語句對相互之間也是持續(xù)的。READ COMMITTED隔離級別是一個有些象Oracle的隔離級別。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD語句僅鎖定索引記錄，而不鎖定記錄前的間隙，因而允許隨意緊挨著已鎖定的記錄插入新記錄。UPDATE和DELETE語句使用一個帶唯一搜索條件的唯一的索引僅鎖定找到的索引記錄，而不包括記錄前的間隙。在范圍類型UPDATE和DELETE語句，InnoDB必須對范圍覆蓋的間隙設置next-key鎖定或間隙鎖定以及其它用戶做的塊插入。這是很必要的，因為要讓MySQL復制和恢復起作用，“幽靈行”必須被阻止掉。如果應用是從基于ORACLE的應用遷移到MYSQL數(shù)據(jù)庫的，那么建議使用該隔離級別提供數(shù)據(jù)庫服務，因為該隔離級別是最接近ORACLE的默認隔離級別的，遷移可能遇到的鎖問題最小。如何減少鎖沖突
對Myisam類型的表：1) Myisam類型的表可以考慮通過改成Innodb類型的表來減少鎖沖突。2) 根據(jù)應用的情況，嘗試橫向拆分成多個表或者改成Myisam分區(qū)對減少鎖沖突也會有一定的幫助。對Innodb類型的表：1) 首先要確認，在對表獲取行鎖的時候，要盡量的使用索引檢索紀錄，如果沒有使用索引訪問，那么即便你只是要更新其中的一行紀錄，也是全表鎖定的。要確保sql是使用索引來訪問紀錄的，必要的時候，請使用explain檢查sql的執(zhí)行計劃，判斷是否按照預期使用了索引。2) 由于mysql的行鎖是針對索引加的鎖，不是針對紀錄加的鎖，所以雖然是訪問不同行的紀錄，但是如果是相同的索引鍵，是會被加鎖的。應用設計的時候也要注意，這里和Oracle有比較大的不同。3) 當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，當表有主鍵或者唯一索引的時候，不是必須使用主鍵或者唯一索引鎖定紀錄，其他普通索引同樣可以用來檢索紀錄，并只鎖定符合條件的行。4) 用SHOW INNODB STATUS來確定最后一個死鎖的原因。查詢的結果中，包括死鎖的事務的詳細信息，包括執(zhí)行的SQL語句的內(nèi)容，每個線程已經(jīng)獲得了什么鎖，在等待什么鎖，以及最后是哪個線程被回滾。詳細的分析死鎖產(chǎn)生的原因，可以通過改進程序有效的避免死鎖的產(chǎn)生。5) 如果應用并不介意死鎖的出現(xiàn)，那么可以在應用中對發(fā)現(xiàn)的死鎖進行處理。6) 確定更合理的事務大小，小事務更少地傾向于沖突。7) 如果你正使用鎖定讀，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），試著用更低的隔離級別，比如READ COMMITTED。8) 以固定的順序訪問你的表和行。則事務形成良好定義的查詢并且沒有死鎖。