《Mysql入門(mén)細(xì)說(shuō)MySQL死鎖與日志二三事》要點(diǎn)：
本文介紹了Mysql入門(mén)細(xì)說(shuō)MySQL死鎖與日志二三事，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

最近線上 MySQL 接連發(fā)生了幾起數(shù)據(jù)異常,都是在凌晨爆發(fā),由于業(yè)務(wù)場(chǎng)景屬于典型的數(shù)據(jù)倉(cāng)庫(kù)型應(yīng)用,日間壓力較小無(wú)法復(fù)現(xiàn).甚至有些異常還比較詭異,最后 root cause 分析頗費(fèi)周折.那實(shí)際業(yè)務(wù)當(dāng)中咱們?nèi)绾文芸焖俚亩ㄎ痪€上 MySQL 問(wèn)題,修復(fù)異常呢?下文我會(huì)根據(jù)兩個(gè)實(shí)際 case,分享下相關(guān)的經(jīng)驗(yàn)與方法.MYSQL學(xué)習(xí)

Case1：部分?jǐn)?shù)據(jù)更新失敗MYSQL學(xué)習(xí)

某天渠道同學(xué)反饋某報(bào)表極個(gè)別渠道數(shù)據(jù)為 0,大部分渠道數(shù)據(jù)正常.這個(gè)數(shù)據(jù)是由一個(gè)統(tǒng)計(jì)程序每天凌晨例行更新的,按理來(lái)說(shuō),要么全部正常,要么全部失敗,那會(huì)是什么原因?qū)е聵O個(gè)別數(shù)據(jù)異常呢?MYSQL學(xué)習(xí)

首先我們能想到的自然是根據(jù)統(tǒng)計(jì)任務(wù)日志來(lái)看了,但是看了統(tǒng)計(jì)程序打印的日志沒(méi)有發(fā)現(xiàn)諸如 SQL update 失敗的異常描述,那當(dāng)時(shí)的數(shù)據(jù)庫(kù)究竟發(fā)生了什么呢?在查看 MySQL-server 日志之前,習(xí)慣性的看了下數(shù)據(jù)庫(kù)狀態(tài)：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

恰好看到了凌晨這個(gè) update 發(fā)生了死鎖：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

篇幅所限,上下文我這里省略了很多,從這段日志里可以看到,TRANSACTION 1 和 TRANSACTION 2 分別持有一定數(shù)量的行鎖,然后又等待對(duì)方的鎖,最后 MySQL 檢測(cè)到 deadlock ,然后選擇回滾了 TRANSACTION 1：Innodb目前處理死鎖的辦法是將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾.MYSQL學(xué)習(xí)

那這里就有 3 個(gè)問(wèn)題了：MYSQL學(xué)習(xí)

1、innodb 行鎖不是只鎖一行?MYSQL學(xué)習(xí)

因?yàn)檫@張表是 innodb 引擎的,InnoDB 支持行鎖和表鎖.而InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的,這一點(diǎn)MySQL與Oracle不同,后者是通過(guò)在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來(lái)實(shí)現(xiàn)的.InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過(guò)索引條件檢索數(shù)據(jù),InnoDB才使用行級(jí)鎖,否則,InnoDB將使用表鎖,會(huì)把所有掃描過(guò)的行都鎖定!在實(shí)際應(yīng)用中,要特別注意InnoDB行鎖的這一特性,不然的話,可能導(dǎo)致大量的鎖沖突,從而影響并發(fā)性能.由于MySQL的行鎖是針對(duì)索引加的鎖,不是針對(duì)記錄加的鎖,所以雖然是訪問(wèn)不同行的記錄,但是如果是使用相同的索引鍵,是會(huì)出現(xiàn)鎖沖突的.當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù),并哀求共享或排他鎖時(shí),InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；另外間隙鎖也會(huì)鎖多行,InnoDB除了通過(guò)范圍條件加鎖時(shí)使用間隙鎖外,如果使用相等條件哀求給一個(gè)不存在的記錄加鎖,InnoDB也會(huì)使用間隙鎖!MYSQL學(xué)習(xí)

話都說(shuō)到這了,那就看下咱們業(yè)務(wù)表的索引情況：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

可以看到這張表的索引極不合理：有3個(gè)索引,但是 update 卻沒(méi)有完全的用上索引,導(dǎo)致 update 沒(méi)有精確的用上索引,需要鎖定多行范圍數(shù)據(jù),從而引發(fā)死鎖.MYSQL學(xué)習(xí)

知道原理后,咱們?cè)倬臉?gòu)建一個(gè)四字段的組合索引即可讓 update 精準(zhǔn)的走 innodb 索引,實(shí)際上,我們更新索引后,這個(gè)死鎖問(wèn)題即得到了辦理.
MYSQL學(xué)習(xí)

注：innodb不僅會(huì)打印出事務(wù)和事務(wù)持有和等待的鎖,而且還有記錄本身,不幸的是,它可能超過(guò)innodb為輸出結(jié)果預(yù)留的長(zhǎng)度(只能打印1M的內(nèi)容且只能保留最近一次的死鎖信息),如果你無(wú)法看到完整的輸出,此時(shí)可以在任意庫(kù)下創(chuàng)建innodb_monitor或innodb_lock_monitor表,這樣innodb status信息會(huì)完整且每15s一次被記錄到錯(cuò)誤日志中.如：create table innodb_monitor(a int)engine=innodb;,不需要記錄到錯(cuò)誤日志中時(shí)就刪掉這個(gè)表即可.MYSQL學(xué)習(xí)

2、回滾為什么只有部分 update 語(yǔ)句失敗MYSQL學(xué)習(xí)

回滾的話,為什么只有部分 update 語(yǔ)句失敗,而不是整個(gè)事務(wù)里的所有 update 都失敗?MYSQL學(xué)習(xí)

這是因?yàn)樵蹅兊?innodb 默認(rèn)是自動(dòng)提交的：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

在多個(gè) update 或 insert 語(yǔ)句情況下,每執(zhí)行完一條 SQL,innodb 就立即 commit 一次以持久化變更,同時(shí)釋放鎖,這也正是本例中死鎖回滾事務(wù)后只有極個(gè)別語(yǔ)句失敗的原因.MYSQL學(xué)習(xí)

需要注意的是,通常還有另外一種情況也可能導(dǎo)致部分語(yǔ)句回滾,需要格外把穩(wěn).在 innodb 里有個(gè)參數(shù)叫：innodb_rollback_on_timeoutMYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

官方手冊(cè)里這樣描述：MYSQL學(xué)習(xí)

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If Cinnodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.MYSQL學(xué)習(xí)

解釋：這個(gè)參數(shù)關(guān)閉或不存在的話遇到超時(shí)只回滾事務(wù)最后一個(gè)Query,打開(kāi)的話事務(wù)遇到超時(shí)就回滾整個(gè)事務(wù).MYSQL學(xué)習(xí)

3、怎樣降低 innodb 死鎖幾率?MYSQL學(xué)習(xí)

死鎖在行鎖及事務(wù)場(chǎng)景下很難完全消除,但可以通過(guò)表設(shè)計(jì)和SQL調(diào)整等措施減少鎖沖突和死鎖,包括：MYSQL學(xué)習(xí)

盡量使用較低的隔離級(jí)別,比如如果發(fā)生了間隙鎖,你可以把會(huì)話或者事務(wù)的事務(wù)隔離級(jí)別更改為 RC(read committed)級(jí)別來(lái)避免,但此時(shí)需要把 binlog_format 設(shè)置成 row 或者 mixed 格式MYSQL學(xué)習(xí)

精心設(shè)計(jì)索引,并盡量使用索引拜訪數(shù)據(jù),使加鎖更精確,從而減少鎖沖突的機(jī)會(huì)；MYSQL學(xué)習(xí)

選擇合理的事務(wù)大小,小事務(wù)發(fā)生鎖沖突的幾率也更小；MYSQL學(xué)習(xí)

給記錄集顯示加鎖時(shí),最好一次性哀求足夠級(jí)別的鎖.比如要修改數(shù)據(jù)的話,最好直接申請(qǐng)排他鎖,而不是先申請(qǐng)共享鎖,修改時(shí)再哀求排他鎖,這樣容易產(chǎn)生死鎖；MYSQL學(xué)習(xí)

不同的程序拜訪一組表時(shí),應(yīng)盡量約定以相同的順序拜訪各表,對(duì)一個(gè)表而言,盡可能以固定的順序存取表中的行.這樣可以大大減少死鎖的機(jī)會(huì)；MYSQL學(xué)習(xí)

盡量用相等條件拜訪數(shù)據(jù),這樣可以避免間隙鎖對(duì)并發(fā)插入的影響；MYSQL學(xué)習(xí)

不要申請(qǐng)超過(guò)實(shí)際需要的鎖級(jí)別；除非必須,查詢時(shí)不要顯示加鎖；MYSQL學(xué)習(xí)

對(duì)于一些特定的事務(wù),可以使用表鎖來(lái)提高處理速度或減少死鎖的可能.MYSQL學(xué)習(xí)

Case2：詭異的 Lock wait timeout MYSQL學(xué)習(xí)

連續(xù)幾天凌晨6點(diǎn)和早上8點(diǎn) 都分別有一個(gè)任務(wù)失敗,load data local infile 的時(shí)候報(bào) Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 異常,和平臺(tái)的同學(xué)溝通得知,這是我們自己的業(yè)務(wù)數(shù)據(jù)庫(kù)的 Lock 時(shí)間太短或者鎖沖突的問(wèn)題.但是回頭一想不應(yīng)該啊?這不一直好好的嗎?而且基本都是單表單任務(wù),不存在多人沖突.MYSQL學(xué)習(xí)

甭管誰(shuí)的問(wèn)題,那咱們還是先看自己的數(shù)據(jù)庫(kù)有沒(méi)有問(wèn)題：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

默認(rèn) lock 超時(shí)時(shí)間 50s,這個(gè)時(shí)間真心不短了,估計(jì)調(diào)了也沒(méi)用,事實(shí)上確實(shí)死馬當(dāng)活馬醫(yī)的試了下沒(méi)用...MYSQL學(xué)習(xí)

而且這次 SHOW ENGINE INNODB STATUS\G 也沒(méi)出現(xiàn)任何死鎖信息,然后又將目光轉(zhuǎn)向 MySQL-server 日志,希望能從日志里看一看那個(gè)時(shí)刻前后數(shù)據(jù)究竟在做什么操作.這里先簡(jiǎn)單的介紹下MySQL日志文件系統(tǒng)的組成：MYSQL學(xué)習(xí)

?? (a) error 日志：記錄啟動(dòng)、運(yùn)行或停止 mysqld 時(shí)出現(xiàn)的問(wèn)題,默認(rèn)開(kāi)啟.
?? (b) general 日志：通用查詢?nèi)罩?記錄所有語(yǔ)句和指令,開(kāi)啟數(shù)據(jù)庫(kù)會(huì)有 5% 左右性能損失.
?? (c) binlog 日志：二進(jìn)制格式,記錄所有更改數(shù)據(jù)的語(yǔ)句,主要用于 slave 復(fù)制和數(shù)據(jù)恢復(fù).
?? (d) slow 日志：記錄所有執(zhí)行時(shí)間超過(guò) long_query_time 秒的查詢或不使用索引的查詢,默認(rèn)關(guān)閉.
?? (e) Innodb日志：innodb redo log、undo log,用于恢復(fù)數(shù)據(jù)和撤銷(xiāo)操作.MYSQL學(xué)習(xí)

從上面的介紹可以看到,目前這個(gè)問(wèn)題的日志可能在 d 和 b 中,看了下 d 中沒(méi)有,那就只能開(kāi)啟 b 了,但 b 對(duì)數(shù)據(jù)庫(kù)的性能有一定損耗,由于是全量日志,量非常巨大,所以開(kāi)啟一定要謹(jǐn)慎：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

我這里只是每天在出問(wèn)題的前后半小時(shí)開(kāi)啟下全量日志,結(jié)果沒(méi)有發(fā)現(xiàn)任何 MySQL-client 哀求到我們的業(yè)務(wù)數(shù)據(jù)庫(kù)!該日志格式如下,記錄了所有的連接與命令：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

那問(wèn)題基本確定了,客戶端哀求都沒(méi)到我們這邊就拋出了上述的異常,和平臺(tái)方再三溝通確認(rèn)下,最后平臺(tái)方查證是因?yàn)樵趫?zhí)行插入前他們需要先從 SQL task 表取出 SQL 和更新 task 狀態(tài),結(jié)果這張表由于在整點(diǎn)存在大量 insert 和 update 并發(fā),導(dǎo)致部分 SQL 等待 lock 超時(shí)了...MYSQL學(xué)習(xí)

MySQL 日志分析腳本MYSQL學(xué)習(xí)

由于凌晨是數(shù)據(jù)倉(cāng)庫(kù)的業(yè)務(wù)高峰,很多問(wèn)題都是在這個(gè)時(shí)候爆發(fā),一些詭異的問(wèn)題往往是過(guò)了這個(gè)村就沒(méi)這個(gè)店了,日間無(wú)法復(fù)現(xiàn).如何能捕獲我們關(guān)心的日志,便于快速的定位問(wèn)題,這個(gè)是重中之重,這里我寫(xiě)了個(gè)小腳本,crontab 部署,可以選擇時(shí)間范圍開(kāi)啟,每分鐘采樣一次日志,需要說(shuō)明的是 general log 沒(méi)事別輕易開(kāi)啟,否則對(duì)數(shù)據(jù)庫(kù)性能損耗較大.MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所贊助,也希望大家多多支持維易PHP.MYSQL學(xué)習(xí)

維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql入門(mén)細(xì)說(shuō)MySQL死鎖與日志二三事》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/13594.html