建站
咨詢
售后
建站咨詢
新聞中心
今天碰到一次因死鎖導(dǎo)致更新操作的sql事務(wù)執(zhí)行時(shí)間過(guò)長(zhǎng)，特將排查過(guò)程記錄如下：
成都創(chuàng)新互聯(lián)公司2013年成立，先為龍陵等服務(wù)建站，龍陵等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為龍陵企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
首先該sql事務(wù)的where條件已經(jīng)命中了主鍵索引，而且表也不大，故可以排除掃表過(guò)慢原因。通過(guò) show processlist;發(fā)現(xiàn)也只有該sql事務(wù)在操作這個(gè)表，初看起來(lái)似乎也不像是死鎖的原因：
但通過(guò)咨詢yellbehuang后發(fā)現(xiàn)，判斷sql事務(wù)是否死鎖不能簡(jiǎn)單通過(guò)show processlist來(lái)判斷，而是要通過(guò)查詢innodb鎖的相關(guān)表來(lái)確定，和innodb鎖有關(guān)的主要有三個(gè)表，
- innodb_trx ## 當(dāng)前運(yùn)行的所有事務(wù)
- innodb_locks ## 當(dāng)前出現(xiàn)的鎖
- innodb_lock_waits ## 鎖等待的對(duì)應(yīng)關(guān)系
上面表的各個(gè)字段的含義如下：
- innodb_locks:
- +————-+———————+——+—–+———+——-+
- | Field | Type | Null | Key | Default | Extra |
- +————-+———————+——+—–+———+——-+
- | lock_id | varchar(81) | NO | | | |#鎖ID
- | lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務(wù)ID
- | lock_mode | varchar(32) | NO | | | |#鎖模式
- | lock_type | varchar(32) | NO | | | |#鎖類型
- | lock_table | varchar(1024) | NO | | | |#被鎖的表
- | lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引
- | lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號(hào)
- | lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁(yè)號(hào)
- | lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號(hào)
- | lock_data | varchar(8192) | YES | | NULL | |#被鎖的數(shù)據(jù)
- innodb_lock_waits:
- +-------------------+-------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------------------+-------------+------+-----+---------+-------+
- | requesting_trx_id | varchar(18) | NO | | | |#請(qǐng)求鎖的事務(wù)ID
- | requested_lock_id | varchar(81) | NO | | | |#請(qǐng)求鎖的鎖ID
- | blocking_trx_id | varchar(18) | NO | | | |#當(dāng)前擁有鎖的事務(wù)ID
- | blocking_lock_id | varchar(81) | NO | | | |#當(dāng)前擁有鎖的鎖ID
- +-------------------+-------------+------+-----+---------+-------+
- innodb_trx :
- +—————————-+———————+——+—–+———————+——-+
- | Field | Type | Null | Key | Extra |
- +—————————-+———————+——+—–+———————+——-+
- | trx_id | varchar(18) | NO | | |#事務(wù)ID
- | trx_state | varchar(13) | NO | | |#事務(wù)狀態(tài)：
- | trx_started | datetime | NO | | |#事務(wù)開(kāi)始時(shí)間；
- | trx_requested_lock_id | varchar(81) | YES | | |#innodb_locks.lock_id
- | trx_wait_started | datetime | YES | | |#事務(wù)開(kāi)始等待的時(shí)間
- | trx_weight | bigint(21) unsigned | NO | | |#
- | trx_mysql_thread_id | bigint(21) unsigned | NO | | |#事務(wù)線程ID
- | trx_query | varchar(1024) | YES | | |#具體SQL語(yǔ)句
- | trx_operation_state | varchar(64) | YES | | |#事務(wù)當(dāng)前操作狀態(tài)
- | trx_tables_in_use | bigint(21) unsigned | NO | | |#事務(wù)中有多少個(gè)表被使用
- | trx_tables_locked | bigint(21) unsigned | NO | | |#事務(wù)擁有多少個(gè)鎖
- | trx_lock_structs | bigint(21) unsigned | NO | | |#
- | trx_lock_memory_bytes | bigint(21) unsigned | NO | | |#事務(wù)鎖住的內(nèi)存大?。˙）
- | trx_rows_locked | bigint(21) unsigned | NO | | |#事務(wù)鎖住的行數(shù)
- | trx_rows_modified | bigint(21) unsigned | NO | | |#事務(wù)更改的行數(shù)
- | trx_concurrency_tickets | bigint(21) unsigned | NO | | |#事務(wù)并發(fā)票數(shù)
- | trx_isolation_level | varchar(16) | NO | | |#事務(wù)隔離級(jí)別
- | trx_unique_checks | int(1) | NO | | |#是否唯一性檢查
- | trx_foreign_key_checks | int(1) | NO | | |#是否外鍵檢查
- | trx_last_foreign_key_error | varchar(256) | YES | | |#最后的外鍵錯(cuò)誤
- | trx_adaptive_hash_latched | int(1) | NO | | |#
- | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | |#
可以通過(guò)select * from INNODB_LOCKS a inner join INNODB_TRX b on a.lock_trx_id=b.trx_id and trx_mysql_thread_id=線程id 來(lái)獲取該sql的鎖狀態(tài)，線程id可以通過(guò)上面的show processlist來(lái)獲得，執(zhí)行結(jié)果如下：
此時(shí)發(fā)現(xiàn)，該sql連接確實(shí)處于LOCK WAIT鎖等待狀態(tài)
通過(guò)select * from innodb_lock_waits where requesting_trx_id=75CB26E5(即上面查詢得到的lock_trx_id)可以得到當(dāng)前擁有鎖的事務(wù)ID 75CB26AE。
再通過(guò)select * from innodb_trx where lock_trx_id=75CB26AE獲取sql語(yǔ)句與線程id
從上面的結(jié)果中看出，該事務(wù)處于running狀態(tài)，但sql卻為null，該線程id即對(duì)于上面show processlist的206機(jī)器的30764端口的連接，該連接處于sleep狀態(tài)。為什么sql為null卻依然占有鎖?在查詢相關(guān)資料和咨詢jameszhou后，知道了這個(gè)實(shí)際和innodb 引擎的寫(xiě)機(jī)制有關(guān)，innodb執(zhí)行寫(xiě)事務(wù)操作時(shí)，實(shí)際是先取得索引中該行的行鎖(即使該表上沒(méi)有任何索引，那么innodb會(huì)在后臺(tái)創(chuàng)建一個(gè)隱藏的聚集主鍵索引)，再在緩存里寫(xiě)入，最后事務(wù)commit后正式寫(xiě)入DB中并釋放鎖。之所以sql為null，是因?yàn)樵撨B接已經(jīng)把sql update操作執(zhí)行寫(xiě)入緩存中了，但是由于代碼bug沒(méi)有最后commit，導(dǎo)致一直占用著行鎖，后續(xù)新的連接想寫(xiě)這一行數(shù)據(jù)卻因?yàn)橐恢比〔坏叫墟i而處于長(zhǎng)時(shí)間的等待狀態(tài)。
那為什么innodb需要兩次寫(xiě)?下面是我查詢相關(guān)資料得出來(lái)的結(jié)論:
因?yàn)閕nnodb中的日志是邏輯的，所謂邏輯就是比如當(dāng)插入一條記錄時(shí)，它可能會(huì)導(dǎo)致在某一個(gè)頁(yè)面(這條記錄最終被插入的位置)的多個(gè)偏移位置寫(xiě)入某個(gè)長(zhǎng)度的值，比如頁(yè)頭的記錄數(shù)，槽數(shù)，頁(yè)尾槽數(shù)據(jù)，頁(yè)中的記錄值等等，這些本是一些物理操作，而innodb為了節(jié)約日志量及其它一些原因，設(shè)計(jì)為邏輯處理的方式，那就是它會(huì)在一個(gè)頁(yè)面的基礎(chǔ)上，把一條記錄插入，那么在日志記錄中記錄的內(nèi)容為表空間號(hào)、頁(yè)面號(hào)、記錄的各個(gè)列的值等等，在內(nèi)部轉(zhuǎn)換為上面的物理操作。
但這里的一個(gè)問(wèn)題是，如果那個(gè)頁(yè)面本身是錯(cuò)誤的，這種錯(cuò)誤有可能是因?yàn)閷?xiě)斷裂(1個(gè)頁(yè)面為16K，分多次寫(xiě)入，后面的有可能沒(méi)有寫(xiě)成功，導(dǎo)致這個(gè)頁(yè)面不完整)引起的，那么這個(gè)邏輯操作就沒(méi)辦法完成了，因?yàn)樗那疤崾沁@個(gè)頁(yè)面還是正確的，完整的，因?yàn)槿绻@個(gè)頁(yè)面不正確的話，這個(gè)頁(yè)面里的數(shù)據(jù)是無(wú)效的，有可能產(chǎn)生各種不可預(yù)料的問(wèn)題。
那么正是因?yàn)檫@個(gè)問(wèn)題，所以必須要首先保證這個(gè)頁(yè)面是正確的，方法就是兩次寫(xiě)，它的思想最終是一種備份思想，也就是一種鏡像。
innodb兩次寫(xiě)的過(guò)程：
可以將兩次寫(xiě)看作是在Innodb表空間內(nèi)部分配的一個(gè)短期的日志文件，這一日志文件包含100個(gè)數(shù)據(jù)頁(yè)。Innodb在寫(xiě)出緩沖區(qū)中的數(shù)據(jù)頁(yè)時(shí)采用的是一次寫(xiě)多個(gè)頁(yè)的方式，這樣多個(gè)頁(yè)就可以先順序?qū)懭氲絻纱螌?xiě)緩沖區(qū)并調(diào)用fsync()保證這些數(shù)據(jù)被寫(xiě)出到磁盤，然后數(shù)據(jù)頁(yè)才被定出到它們實(shí)際的存儲(chǔ)位置并再次調(diào)用fsync()。故障恢復(fù)時(shí)Innodb檢查doublewrite緩沖區(qū)與數(shù)據(jù)頁(yè)原存儲(chǔ)位置的內(nèi)容，若數(shù)據(jù)頁(yè)在兩次寫(xiě)緩沖區(qū)中處于不一致?tīng)顟B(tài)將被簡(jiǎn)單的丟棄，若在原存儲(chǔ)位置中不一致則從兩次寫(xiě)緩沖區(qū)中還原。
原文鏈接：https://www.qcloud.com/community/article/886137
作者：陳文嘯
【本文是專欄作者“騰訊云技術(shù)社區(qū)”的原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)通過(guò)聯(lián)系原作者獲取授權(quán)】
分享標(biāo)題：如何排查MySQL執(zhí)行死鎖原因
標(biāo)題URL：http://www.5511xx.com/article/dhgsihi.html
