MySQL鎖等待與死鎖問題分析
：
在 MySQL 運(yùn)維過程中，鎖等待和死鎖問題是令各位 DBA 及開發(fā)同學(xué)非常頭痛的事。出現(xiàn)此類問題會(huì)造成業(yè)務(wù)回滾、卡頓等故障，特別是業(yè)務(wù)繁忙的系統(tǒng)，出現(xiàn)死鎖問題后影響會(huì)更嚴(yán)重。本篇文章我們一起來學(xué)習(xí)下什么是鎖等待及死鎖，出現(xiàn)此類問題又應(yīng)該如何分析處理呢？
1.了解鎖等待與死鎖
出現(xiàn)鎖等待或死鎖的原因是訪問數(shù)據(jù)庫需要加鎖，那你可能要問了，為啥要加鎖呢？原因是為了確保并發(fā)更新場(chǎng)景下的數(shù)據(jù)正確性，保證數(shù)據(jù)庫事務(wù)的隔離性。
試想一個(gè)場(chǎng)景，如果你要去圖書館借一本《高性能MySQL》，為了防止有人提前把這本書借走，你可以提前進(jìn)行預(yù)約（加鎖），這把鎖可以怎么加？
- 封鎖圖書館（數(shù)據(jù)庫級(jí)別的鎖）
- 把數(shù)據(jù)庫相關(guān)的書都鎖?。ū砑?jí)別的鎖）
- 只鎖 MySQL 相關(guān)的書（頁級(jí)別的鎖）
- 只鎖《高性能MySQL》這本書（行級(jí)別的鎖）
鎖的粒度越細(xì)，并發(fā)級(jí)別越高，實(shí)現(xiàn)也更復(fù)雜。
鎖等待也可稱為事務(wù)等待，后執(zhí)行的事務(wù)等待前面處理的事務(wù)釋放鎖，但是等待時(shí)間超過了 MySQL 的鎖等待時(shí)間，就會(huì)引發(fā)這個(gè)異常。等待超時(shí)后的報(bào)錯(cuò)為“Lock wait timeout exceeded...”。
死鎖發(fā)生的原因是兩個(gè)事務(wù)互相等待對(duì)方釋放相同資源的鎖，從而造成的死循環(huán)。產(chǎn)生死鎖后會(huì)立即報(bào)錯(cuò)“Deadlock found when trying to get lock...”。
2.現(xiàn)象復(fù)現(xiàn)及處理
下面我們以 MySQL 5.7.23 版本為例（隔離級(jí)別是 RR ），來復(fù)現(xiàn)下上述兩種異常現(xiàn)象。
mysql>showcreatetabletest_tb\G ***************************1.row*************************** Table:test_tb CreateTable:CREATETABLE`test_tb`( `id`int(11)NOTNULLAUTO_INCREMENT, `col1`varchar(50)NOTNULLDEFAULT'', `col2`int(11)NOTNULLDEFAULT'1', `col3`varchar(20)NOTNULLDEFAULT'', PRIMARYKEY(`id`), KEY`idx_col1`(`col1`) )ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8 1rowinset(0.00sec) mysql>select*fromtest_tb; +----+------+------+------+ |id|col1|col2|col3| +----+------+------+------+ |1|fdg|1|abc| |2|a|2|fg| |3|ghrv|2|rhdv| +----+------+------+------+ 3rowsinset(0.00sec) #事務(wù)一首先執(zhí)行 mysql>begin; QueryOK,0rowsaffected(0.00sec) mysql>select*fromtest_tbwherecol1='a'forupdate; +----+------+------+------+ |id|col1|col2|col3| +----+------+------+------+ |2|a|2|fg| +----+------+------+------+ 1rowinset(0.00sec) #事務(wù)二然后執(zhí)行 mysql>begin; QueryOK,0rowsaffected(0.01sec) mysql>updatetest_tbsetcol2=1wherecol1='a'; ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
出現(xiàn)上種異常的原因是事務(wù)二在等待事務(wù)一的行鎖，但事務(wù)一一直沒提交，等待超時(shí)而報(bào)錯(cuò)。InnoDB 行鎖等待超時(shí)時(shí)間由 innodb_lock_wait_timeout 參數(shù)控制，此參數(shù)默認(rèn)值為 50 ，單位為秒，即默認(rèn)情況下，事務(wù)二會(huì)等待 50s ，若仍拿不到行鎖則會(huì)報(bào)等待超時(shí)異常并回滾此條語句。
對(duì)于 5.7 版本，出現(xiàn)鎖等待時(shí)，我們可以查看 information_schema 中的幾張系統(tǒng)表來查詢事務(wù)狀態(tài)。
- innodb_trx 當(dāng)前運(yùn)行的所有事務(wù)。
- innodb_locks 當(dāng)前出現(xiàn)的鎖。
- innodb_lock_waits 鎖等待的對(duì)應(yīng)關(guān)系
#鎖等待發(fā)生時(shí)查看innodb_trx表可以看到所有事務(wù) #trx_state值為L(zhǎng)OCKWAIT則代表該事務(wù)處于等待狀態(tài) mysql>select*frominformation_schema.innodb_trx\G ***************************1.row*************************** trx_id:38511 trx_state:LOCKWAIT trx_started:2021-03-2417:20:43 trx_requested_lock_id:38511:156:4:2 trx_wait_started:2021-03-2417:20:43 trx_weight:2 trx_mysql_thread_id:1668447 trx_query:updatetest_tbsetcol2=1wherecol1='a' trx_operation_state:startingindexread trx_tables_in_use:1 trx_tables_locked:1 trx_lock_structs:2 trx_lock_memory_bytes:1136 trx_rows_locked:1 trx_rows_modified:0 trx_concurrency_tickets:0 trx_isolation_level:REPEATABLEREAD trx_unique_checks:1 trx_foreign_key_checks:1 trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:0 trx_is_read_only:0 trx_autocommit_non_locking:0 ***************************2.row*************************** trx_id:38510 trx_state:RUNNING trx_started:2021-03-2417:18:54 trx_requested_lock_id:NULL trx_wait_started:NULL trx_weight:4 trx_mysql_thread_id:1667530 trx_query:NULL trx_operation_state:NULL trx_tables_in_use:0 trx_tables_locked:1 trx_lock_structs:4 trx_lock_memory_bytes:1136 trx_rows_locked:3 trx_rows_modified:0 trx_concurrency_tickets:0 trx_isolation_level:REPEATABLEREAD trx_unique_checks:1 trx_foreign_key_checks:1 trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:0 trx_is_read_only:0 trx_autocommit_non_locking:0 2rowsinset(0.00sec) #innodb_trx字段值含義 trx_id：事務(wù)ID。 trx_state：事務(wù)狀態(tài)，有以下幾種狀態(tài)：RUNNING、LOCKWAIT、ROLLINGBACK 和 COMMITTING。 trx_started：事務(wù)開始時(shí)間。 trx_requested_lock_id：事務(wù)當(dāng)前正在等待鎖的標(biāo)識(shí)，可以和 INNODB_LOCKS 表JOIN以得到更多詳細(xì)信息。 trx_wait_started：事務(wù)開始等待的時(shí)間。 trx_weight：事務(wù)的權(quán)重。 trx_mysql_thread_id：事務(wù)線程ID，可以和PROCESSLIST表JOIN。 trx_query：事務(wù)正在執(zhí)行的SQL語句。 trx_operation_state：事務(wù)當(dāng)前操作狀態(tài)。 trx_tables_in_use：當(dāng)前事務(wù)執(zhí)行的SQL中使用的表的個(gè)數(shù)。 trx_tables_locked：當(dāng)前執(zhí)行SQL的行鎖數(shù)量。 trx_lock_structs：事務(wù)保留的鎖數(shù)量。 trx_isolation_level：當(dāng)前事務(wù)的隔離級(jí)別。 # sys.innodb_lock_waits 視圖也可看到事務(wù)等待狀況，且給出了殺鏈接的SQL mysql>select*fromsys.innodb_lock_waits\G ***************************1.row*************************** wait_started:2021-03-2417:20:43 wait_age:00:00:22 wait_age_secs:22 locked_table:`testdb`.`test_tb` locked_index:idx_col1 locked_type:RECORD waiting_trx_id:38511 waiting_trx_started:2021-03-2417:20:43 waiting_trx_age:00:00:22 waiting_trx_rows_locked:1 waiting_trx_rows_modified:0 waiting_pid:1668447 waiting_query:updatetest_tbsetcol2=1wherecol1='a' waiting_lock_id:38511:156:4:2 waiting_lock_mode:X blocking_trx_id:38510 blocking_pid:1667530 blocking_query:NULL blocking_lock_id:38510:156:4:2 blocking_lock_mode:X blocking_trx_started:2021-03-2417:18:54 blocking_trx_age:00:02:11 blocking_trx_rows_locked:3 blocking_trx_rows_modified:0 sql_kill_blocking_query:KILLQUERY1667530 sql_kill_blocking_connection:KILL1667530
sys.innodb_lock_waits 視圖整合了事務(wù)等待狀況，同時(shí)給出殺掉堵塞源端的 kill 語句。不過是否要?dú)⒌翩溄舆€是需要綜合考慮的。
死鎖與鎖等待稍有不同，我們同樣也來簡(jiǎn)單復(fù)現(xiàn)下死鎖現(xiàn)象。
#開啟兩個(gè)事務(wù) #事務(wù)一執(zhí)行 mysql>updatetest_tbsetcol2=1wherecol1='a'; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 #事務(wù)二執(zhí)行 mysql>updatetest_tbsetcol2=1whereid=3; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 #回到事務(wù)一執(zhí)行回車后此條語句處于鎖等待狀態(tài) mysql>updatetest_tbsetcol1='abcd'whereid=3; QueryOK,1rowaffected(5.71sec) Rowsmatched:1Changed:1Warnings:0 #回到事務(wù)二再執(zhí)行此時(shí)二者相互等待發(fā)生死鎖 mysql>updatetest_tbsetcol3='gddx'wherecol1='a'; ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
發(fā)生死鎖后會(huì)選擇一個(gè)事務(wù)進(jìn)行回滾，想查明死鎖原因，可以執(zhí)行 show engine innodb status 來查看死鎖日志，根據(jù)死鎖日志，結(jié)合業(yè)務(wù)邏輯來進(jìn)一步定位死鎖原因。
在實(shí)際應(yīng)用中，我們要盡量避免死鎖現(xiàn)象的發(fā)生，可以從以下幾個(gè)方面入手：
- 事務(wù)盡可能小，不要將復(fù)雜邏輯放進(jìn)一個(gè)事務(wù)里。
- 涉及多行記錄時(shí)，約定不同事務(wù)以相同順序訪問。
- 業(yè)務(wù)中要及時(shí)提交或者回滾事務(wù)，可減少死鎖產(chǎn)生的概率。
- 表要有合適的索引。
- 可嘗試將隔離級(jí)別改為 RC 。
總結(jié)：
本篇文章簡(jiǎn)單介紹了鎖等待及死鎖發(fā)生的原因，其實(shí)真實(shí)業(yè)務(wù)中發(fā)生死鎖還是很難分析的，需要一定的經(jīng)驗(yàn)積累。本篇文章只是面向初學(xué)者，希望各位對(duì)死鎖能夠有個(gè)初印象。
以上就是MySQL鎖等待與死鎖問題分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL鎖等待與死鎖的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信