獲取鎖等待情況
可以通過(guò)檢查table_locks_waited和table_locks_immediate狀態(tài)變量來(lái)分析系統(tǒng)上的表鎖定爭(zhēng)奪：mysql> show status like 'Table%';+----------------------------+----------+| Variable_name | Value |+----------------------------+----------+| Table_locks_immediate | 105 || Table_locks_waited | 3 |+----------------------------+----------+2 rows in set (0.00 sec) 可以通過(guò)檢查Innodb_row_lock狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhē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) 另外芦瘾，針對(duì)Innodb類型的表碟渺，如果需要察看當(dāng)前的鎖等待情況斥季，可以設(shè)置InnoDB Monitors亥鸠，然后通過(guò)Show innodb status察看，設(shè)置的方式是： CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;監(jiān)視器可以通過(guò)發(fā)出下列語(yǔ)句來(lái)被停止： DROP TABLE innodb_monitor;設(shè)置監(jiān)視器后，在show innodb status的顯示內(nèi)容中，會(huì)有詳細(xì)的當(dāng)前鎖等待的信息，包括表名惯悠、鎖類型、鎖定記錄的情況等等竣况，便于進(jìn)行進(jìn)一步的分析和問(wèn)題的確定克婶。打開(kāi)監(jiān)視器以后，默認(rèn)情況下每15秒會(huì)向日志中記錄監(jiān)控的內(nèi)容丹泉，如果長(zhǎng)時(shí)間打開(kāi)會(huì)導(dǎo)致.err文件變得非常的巨大情萤，所以我們?cè)诖_認(rèn)問(wèn)題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器摹恨〗畹海或者通過(guò)使用--console選項(xiàng)來(lái)啟動(dòng)服務(wù)器以關(guān)閉寫(xiě)日志文件。什么情況下使用表鎖
表級(jí)鎖在下列幾種情況下比行級(jí)鎖更優(yōu)越：很多操作都是讀表晒哄。
在嚴(yán)格條件的索引上讀取和更新睁宰，當(dāng)更新或者刪除可以用單獨(dú)的索引來(lái)讀取得到時(shí)：
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
SELECT 和 INSERT 語(yǔ)句并發(fā)的執(zhí)行，但是只有很少的 UPDATE 和 DELETE 語(yǔ)句寝凌。
很多的掃描表和對(duì)全表的 GROUP BY 操作柒傻，但是沒(méi)有任何寫(xiě)表。

什么情況下使用行鎖
行級(jí)鎖定的優(yōu)點(diǎn)：當(dāng)在許多線程中訪問(wèn)不同的行時(shí)只存在少量鎖定沖突较木。
回滾時(shí)只有少量的更改诅愚。
可以長(zhǎng)時(shí)間鎖定單一的行。

行級(jí)鎖定的缺點(diǎn)：比頁(yè)級(jí)或表級(jí)鎖定占用更多的內(nèi)存。
當(dāng)在表的大部分中使用時(shí)违孝，比頁(yè)級(jí)或表級(jí)鎖定速度慢，因?yàn)槟惚仨毇@取更多的鎖泳赋。
如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進(jìn)行GROUP BY操作或者必須經(jīng)常掃描整個(gè)表雌桑，比其它鎖定明顯慢很多。
用高級(jí)別鎖定祖今，通過(guò)支持不同的類型鎖定校坑，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因?yàn)槠滏i成本小于行級(jí)鎖定千诬。

insert …select …帶來(lái)的問(wèn)題
當(dāng)使用insert...select...進(jìn)行記錄的插入時(shí)耍目，如果select的表是innodb類型的，不論insert的表是什么類型的表徐绑，都會(huì)對(duì)select的表的紀(jì)錄進(jìn)行鎖定邪驮。對(duì)于那些從oracle遷移過(guò)來(lái)的應(yīng)用，需要特別的注意傲茄，因?yàn)閛racle并不存在類似的問(wèn)題毅访，所以在oracle的應(yīng)用中insert...select...操作非常的常見(jiàn)。例如：有時(shí)候會(huì)對(duì)比較多的紀(jì)錄進(jìn)行統(tǒng)計(jì)分析盘榨，然后將統(tǒng)計(jì)的中間結(jié)果插入到另外一個(gè)表喻粹，這樣的操作因?yàn)檫M(jìn)行的非常少，所以可能并沒(méi)有設(shè)置相應(yīng)的索引草巡。如果遷移到mysql數(shù)據(jù)庫(kù)后不進(jìn)行相應(yīng)的調(diào)整守呜，那么在進(jìn)行這個(gè)操作期間，對(duì)需要select的表實(shí)際上是進(jìn)行的全表掃描導(dǎo)致的所有記錄的鎖定山憨，將會(huì)對(duì)應(yīng)用的其他操作造成非常嚴(yán)重的影響查乒。究其主要原因，是因?yàn)閙ysql在實(shí)現(xiàn)復(fù)制的機(jī)制時(shí)和oracle是不同的萍歉，如果不進(jìn)行select表的鎖定侣颂，則可能造成從數(shù)據(jù)庫(kù)在恢復(fù)期間插入結(jié)果集的不同，造成主從數(shù)據(jù)的不一致枪孩。如果不采用主從復(fù)制憔晒，關(guān)閉binlog并不能避免對(duì)select紀(jì)錄的鎖定，某些文檔中提到可以通過(guò)設(shè)置innodb_locks_unsafe_for_binlog來(lái)避免這個(gè)現(xiàn)象蔑舞，當(dāng)這個(gè)參數(shù)設(shè)置為true的時(shí)候拒担，將不會(huì)對(duì)select的結(jié)果集加鎖，但是這樣的設(shè)置將可能帶來(lái)非常嚴(yán)重的隱患攻询。如果使用這個(gè)binlog進(jìn)行從數(shù)據(jù)庫(kù)的恢復(fù)从撼，或者進(jìn)行主數(shù)據(jù)庫(kù)的災(zāi)難恢復(fù)，都將可能和主數(shù)據(jù)庫(kù)的執(zhí)行效果不同钧栖。因此低零，我們并不推薦通過(guò)設(shè)置這個(gè)參數(shù)來(lái)避免insert...select...導(dǎo)致的鎖婆翔，如果需要進(jìn)行可能會(huì)掃描大量數(shù)據(jù)的insert...select操作，我們推薦使用select...into outfile和load data infile的組合來(lái)實(shí)現(xiàn)掏婶，這樣是不會(huì)對(duì)紀(jì)錄進(jìn)行鎖定的啃奴。next-key鎖對(duì)并發(fā)插入的影響
在行級(jí)鎖定中，InnoDB 使用一個(gè)名為next-key locking的算法雄妥。InnoDB以這樣一種方式執(zhí)行行級(jí)鎖定：當(dāng)它搜索或掃描表的索引之時(shí)最蕾，它對(duì)遇到的索引記錄設(shè)置共享或獨(dú)占鎖定。因此老厌，行級(jí)鎖定事實(shí)上是索引記錄鎖定瘟则。InnoDB對(duì)索引記錄設(shè)置的鎖定也映像索引記錄之前的“間隙”。如果一個(gè)用戶對(duì)一個(gè)索引上的記錄R有共享或獨(dú)占的鎖定枝秤，另一個(gè)用戶 不能緊接在R之前以索引的順序插入一個(gè)新索引記錄醋拧。這個(gè)間隙的鎖定被執(zhí)行來(lái)防止所謂的“幽靈問(wèn)題”∷薨伲可以用next-key鎖定在你的應(yīng)用程序上實(shí)現(xiàn)一個(gè)唯一性檢查：如果你以共享模式讀數(shù)據(jù)趁仙，并且沒(méi)有看到你將要插入的行的重復(fù)，則你可以安全地插入你的行垦页，并且知道在讀過(guò)程中對(duì)你的行的繼承者設(shè)置的next-key鎖定與此同時(shí)阻止任何人對(duì)你的行插入一個(gè)重復(fù)雀费。因此，the next-key鎖定允許你鎖住在你的表中并不存在的一些東西痊焊。隔離級(jí)別對(duì)并發(fā)插入的影響
REPEATABLE READ是InnoDB的默認(rèn)隔離級(jí)別盏袄。帶唯一搜索條件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE語(yǔ)句只鎖定找到的索引記錄，而不鎖定記錄前的間隙薄啥。用其它搜索條件辕羽，這些操作采用next-key鎖定，用next-key鎖定或者間隙鎖定鎖住搜索的索引范圍垄惧，并且阻止其它用戶的新插入刁愿。在持續(xù)讀中，有一個(gè)與READ COMMITTED隔離級(jí)別重要的差別：在這個(gè)級(jí)別到逊，在同一事務(wù)內(nèi)所有持續(xù)讀讀取由第一次讀所確定的同一快照铣口。這個(gè)慣例意味著如果你在同一事務(wù)內(nèi)發(fā)出數(shù)個(gè)無(wú)格式SELECT語(yǔ)句，這些SELECT語(yǔ)句對(duì)相互之間也是持續(xù)的觉壶。READ COMMITTED隔離級(jí)別是一個(gè)有些象Oracle的隔離級(jí)別脑题。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD語(yǔ)句僅鎖定索引記錄，而不鎖定記錄前的間隙铜靶，因而允許隨意緊挨著已鎖定的記錄插入新記錄叔遂。UPDATE和DELETE語(yǔ)句使用一個(gè)帶唯一搜索條件的唯一的索引僅鎖定找到的索引記錄，而不包括記錄前的間隙。在范圍類型UPDATE和DELETE語(yǔ)句已艰，InnoDB必須對(duì)范圍覆蓋的間隙設(shè)置next-key鎖定或間隙鎖定以及其它用戶做的塊插入痊末。這是很必要的，因?yàn)橐孧ySQL復(fù)制和恢復(fù)起作用哩掺，“幽靈行”必須被阻止掉舌胶。如果應(yīng)用是從基于ORACLE的應(yīng)用遷移到MYSQL數(shù)據(jù)庫(kù)的，那么建議使用該隔離級(jí)別提供數(shù)據(jù)庫(kù)服務(wù)疮丛，因?yàn)樵摳綦x級(jí)別是最接近ORACLE的默認(rèn)隔離級(jí)別的，遷移可能遇到的鎖問(wèn)題最小辆它。如何減少鎖沖突
對(duì)Myisam類型的表：1) Myisam類型的表可以考慮通過(guò)改成Innodb類型的表來(lái)減少鎖沖突誊薄。2) 根據(jù)應(yīng)用的情況，嘗試橫向拆分成多個(gè)表或者改成Myisam分區(qū)對(duì)減少鎖沖突也會(huì)有一定的幫助锰茉。對(duì)Innodb類型的表：1) 首先要確認(rèn)呢蔫，在對(duì)表獲取行鎖的時(shí)候，要盡量的使用索引檢索紀(jì)錄飒筑，如果沒(méi)有使用索引訪問(wèn)片吊，那么即便你只是要更新其中的一行紀(jì)錄，也是全表鎖定的协屡。要確保sql是使用索引來(lái)訪問(wèn)紀(jì)錄的俏脊，必要的時(shí)候，請(qǐng)使用explain檢查sql的執(zhí)行計(jì)劃肤晓，判斷是否按照預(yù)期使用了索引爷贫。2) 由于mysql的行鎖是針對(duì)索引加的鎖，不是針對(duì)紀(jì)錄加的鎖补憾，所以雖然是訪問(wèn)不同行的紀(jì)錄漫萄，但是如果是相同的索引鍵，是會(huì)被加鎖的盈匾。應(yīng)用設(shè)計(jì)的時(shí)候也要注意腾务，這里和Oracle有比較大的不同。3) 當(dāng)表有多個(gè)索引的時(shí)候削饵，不同的事務(wù)可以使用不同的索引鎖定不同的行岩瘦，當(dāng)表有主鍵或者唯一索引的時(shí)候，不是必須使用主鍵或者唯一索引鎖定紀(jì)錄葵孤，其他普通索引同樣可以用來(lái)檢索紀(jì)錄担钮，并只鎖定符合條件的行。4) 用SHOW INNODB STATUS來(lái)確定最后一個(gè)死鎖的原因尤仍。查詢的結(jié)果中箫津，包括死鎖的事務(wù)的詳細(xì)信息，包括執(zhí)行的SQL語(yǔ)句的內(nèi)容，每個(gè)線程已經(jīng)獲得了什么鎖苏遥，在等待什么鎖饼拍，以及最后是哪個(gè)線程被回滾。詳細(xì)的分析死鎖產(chǎn)生的原因田炭，可以通過(guò)改進(jìn)程序有效的避免死鎖的產(chǎn)生师抄。5) 如果應(yīng)用并不介意死鎖的出現(xiàn)，那么可以在應(yīng)用中對(duì)發(fā)現(xiàn)的死鎖進(jìn)行處理教硫。6) 確定更合理的事務(wù)大小叨吮，小事務(wù)更少地傾向于沖突。7) 如果你正使用鎖定讀瞬矩，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE）茶鉴，試著用更低的隔離級(jí)別，比如READ COMMITTED景用。8) 以固定的順序訪問(wèn)你的表和行涵叮。則事務(wù)形成良好定義的查詢并且沒(méi)有死鎖。