MySQL中死鎖與日志的示例分析

這篇文章將為大家詳細講解有關MySQL中死鎖與日志的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

在冷水江等地區(qū)，都構建了全面的區(qū)域性戰(zhàn)略布局，加強發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務理念，為客戶提供成都網(wǎng)站建設、成都網(wǎng)站制作 網(wǎng)站設計制作按需求定制制作,公司網(wǎng)站建設,企業(yè)網(wǎng)站建設,品牌網(wǎng)站制作,成都全網(wǎng)營銷,成都外貿(mào)網(wǎng)站建設,冷水江網(wǎng)站建設費用合理。

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

Case1：部分數(shù)據(jù)更新失敗

某天渠道同學反饋某報表極個別渠道數(shù)據(jù)為 0，大部分渠道數(shù)據(jù)正常。這個數(shù)據(jù)是由一個統(tǒng)計程序每天凌晨例行更新的，按理來說，要么全部正常，要么全部失敗，那會是什么原因?qū)е聵O個別數(shù)據(jù)異常呢？

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

恰好看到了凌晨這個 update 發(fā)生了死鎖：

篇幅所限，上下文我這里省略了很多，從這段日志里可以看到，TRANSACTION 1 和 TRANSACTION 2 分別持有一定數(shù)量的行鎖，然后又等待對方的鎖，最后 MySQL 檢測到 deadlock ，然后選擇回滾了 TRANSACTION 1：Innodb目前處理死鎖的方法是將持有最少行級排他鎖的事務進行回滾。

那這里就有 3 個問題了：

1、innodb 行鎖不是只鎖一行？

因為這張表是 innodb 引擎的，InnoDB 支持行鎖和表鎖。而InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖，會把所有掃描過的行都鎖定！在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發(fā)性能。由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。當我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；另外間隙鎖也會鎖多行，InnoDB除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

話都說到這了，那就看下咱們業(yè)務表的索引情況：

可以看到這張表的索引極不合理：有3個索引，但是 update 卻沒有完全的用上索引，導致 update 沒有精確的用上索引，需要鎖定多行范圍數(shù)據(jù)，從而引發(fā)死鎖。

知道原理后，咱們再精心構建一個四字段的組合索引即可讓 update 精準的走 innodb 索引，實際上，我們更新索引后，這個死鎖問題即得到了解決。

注：innodb不僅會打印出事務和事務持有和等待的鎖，而且還有記錄本身，不幸的是，它可能超過innodb為輸出結果預留的長度（只能打印1M的內(nèi)容且只能保留最近一次的死鎖信息），如果你無法看到完整的輸出，此時可以在任意庫下創(chuàng)建innodb_monitor或innodb_lock_monitor表，這樣innodb status信息會完整且每15s一次被記錄到錯誤日志中。如：create table innodb_monitor(a int)engine=innodb;，不需要記錄到錯誤日志中時就刪掉這個表即可。

2、回滾為什么只有部分 update 語句失敗

回滾的話，為什么只有部分 update 語句失敗，而不是整個事務里的所有 update 都失敗？

這是因為咱們的 innodb 默認是自動提交的：

在多個 update 或 insert 語句情況下，每執(zhí)行完一條 SQL，innodb 就立即 commit 一次以持久化變更，同時釋放鎖，這也正是本例中死鎖回滾事務后只有極個別語句失敗的原因。

需要注意的是，通常還有另外一種情況也可能導致部分語句回滾，需要格外留意。在 innodb 里有個參數(shù)叫：innodb_rollback_on_timeout

官方手冊里這樣描述：

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_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.

解釋：這個參數(shù)關閉或不存在的話遇到超時只回滾事務最后一個Query，打開的話事務遇到超時就回滾整個事務。

3、怎樣降低 innodb 死鎖幾率？

死鎖在行鎖及事務場景下很難完全消除，但可以通過表設計和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：

盡量使用較低的隔離級別，比如如果發(fā)生了間隙鎖，你可以把會話或者事務的事務隔離級別更改為 RC(read committed)級別來避免，但此時需要把 binlog_format 設置成 row 或者 mixed 格式

精心設計索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機會；

選擇合理的事務大小，小事務發(fā)生鎖沖突的幾率也更小；

給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖；

不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；

盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響；

不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖；

對于一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。

Case2：詭異的 Lock wait timeout

連續(xù)幾天凌晨6點和早上8點 都分別有一個任務失敗，load data local infile 的時候報 Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 異常，和平臺的同學溝通得知，這是我們自己的業(yè)務數(shù)據(jù)庫的 Lock 時間太短或者鎖沖突的問題。但是回頭一想不應該??？這不一直好好的嗎？而且基本都是單表單任務，不存在多人沖突。

甭管誰的問題，那咱們還是先看自己的數(shù)據(jù)庫有沒有問題：

默認 lock 超時時間 50s，這個時間真心不短了，估計調(diào)了也沒用，事實上確實死馬當活馬醫(yī)的試了下沒用。。。

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

(a) error 日志：記錄啟動、運行或停止 mysqld 時出現(xiàn)的問題，默認開啟。
  (b) general 日志：通用查詢?nèi)罩荆涗浰姓Z句和指令，開啟數(shù)據(jù)庫會有 5% 左右性能損失。
  (c) binlog 日志：二進制格式，記錄所有更改數(shù)據(jù)的語句，主要用于 slave 復制和數(shù)據(jù)恢復。
  (d) slow 日志：記錄所有執(zhí)行時間超過 long_query_time 秒的查詢或不使用索引的查詢，默認關閉。
  (e) Innodb日志：innodb redo log、undo log，用于恢復數(shù)據(jù)和撤銷操作。

從上面的介紹可以看到，目前這個問題的日志可能在 d 和 b 中，看了下 d 中沒有，那就只能開啟 b 了，但 b 對數(shù)據(jù)庫的性能有一定損耗，由于是全量日志，量非常巨大，所以開啟一定要謹慎：

我這里只是每天在出問題的前后半小時開啟下全量日志，結果沒有發(fā)現(xiàn)任何 MySQL-client 請求到我們的業(yè)務數(shù)據(jù)庫！該日志格式如下，記錄了所有的連接與命令：

那問題基本確定了，客戶端請求都沒到我們這邊就拋出了上述的異常，和平臺方再三溝通確認下，最后平臺方查證是因為在執(zhí)行插入前他們需要先從 SQL task 表取出 SQL 和更新 task 狀態(tài)，結果這張表由于在整點存在大量 insert 和 update 并發(fā)，導致部分 SQL 等待 lock 超時了。。。

MySQL 日志分析腳本

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

關于“MySQL中死鎖與日志的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

網(wǎng)站題目：MySQL中死鎖與日志的示例分析
當前網(wǎng)址：http://bm7419.com/article36/psdgsg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設計公司、網(wǎng)站制作、企業(yè)網(wǎng)站制作、面包屑導航、小程序開發(fā)、網(wǎng)站改版

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)