MySQL死鎖與日誌分析-mysql教程-PHP中文網

本文主要跟大家聊一聊MySQL死鎖與日誌二三事，實際業務當中如何快速的定位線上MySQL問題，修復異常？本文根據兩個實際case，分享下相關的經驗與方法，有興趣的夥伴們可以參考一下，希望能幫助大家。

最近線上 MySQL 接連發生了幾起資料異常，都是在凌晨爆發，由於業務場景屬於典型的資料倉儲型應用，白天壓力較小無法復現。甚至有些異常還比較詭異，最後 root cause 分析頗費周折。那實際業務當中咱們如何能快速的定位線上 MySQL 問題，修復異常呢？下文我會根據兩個實際 case，分享下相關的經驗與方法。

Case1：部分資料更新失敗

某天通路同學回饋某報表極個別頻道資料為0，大部分頻道資料正常。這個數據是由一個統計程序每天凌晨例行更新的，照理來說，要嘛全部正常，要嘛全部失敗，那會是什麼原因導致極個別數據異常呢？

首先我們能想到的自然是根據統計任務日誌來看了，但是看了統計程式列印的日誌沒有發現諸如 SQL update 失敗的異常描述，那當時的資料庫究竟發生了什麼事？在查看MySQL-server 日誌之前，習慣性的看了下資料庫狀態：

#剛好看到了凌晨這個update 發生了死鎖：

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

那這裡就有 3 個問題了：

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

因為這張表是 innodb 引擎的，InnoDB 支援行鎖定和表格鎖定。而InnoDB行鎖是透過在索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是透過在資料塊中對對應資料行加鎖來實現的。 InnoDB這種行鎖實現特點意味著：只有透過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖，會把所有掃描過的行都鎖定！在實際應用中，要特別注意InnoDB行鎖的這項特性，不然的話，可能導致大量的鎖定衝突，進而影響並發效能。由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是存取不同行的記錄，但如果是使用相同的索引鍵，是會出現鎖定衝突的。當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；另外間隙鎖也會鎖多行，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

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

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

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

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

2、回溯為什麼只有部分 update 語句失敗

回滾的話，為什麼只有部分 update 語句失敗，而不是整個交易裡的所有 update 都失敗？

這是因為咱們的 innodb 預設是自動提交的：

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

要注意的是，通常還有另一種情況也可能導致部分語句回滾，需要格外留意。在innodb 裡有個參數叫做：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) .

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

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

死鎖在行鎖及交易場景下很難完全消除，但可以透過表格設計和SQL調整等措施減少鎖定衝突和死鎖，包括：

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

精心設計索引，並儘量使用索引存取數據，使加鎖更精確，從而減少鎖定衝突的機會；

#選擇合理的事務大小，小事務發生鎖定衝突的幾率也更小；

給記錄集顯示加鎖時，最好一次要求足夠等級的鎖定。例如要修改資料的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖；

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

盡量用相等條件存取數據，這樣可以避免間隙鎖對並發插入的影響；

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

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

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

甭管誰的問題，那咱們還是先看自己的資料庫有沒有問題：

預設lock 超時時間50s，這個時間真心不短了，估計調了也沒用，事實上確實死馬當活馬醫的試了下沒用。。。

而且這次SHOW ENGINE INNODB STATUS\G 也沒出現任何死鎖信息，然後又將目光轉向MySQL-server 日誌，希望能從日誌裡看一看那個時刻前後數據究竟在做什麼操作。這裡先簡單的介紹下MySQL日誌檔案系統的組成：

(a) error 日誌：記錄啟動、執行或停止 mysqld 時出現的問題，預設為開啟。

(b) general 日誌：通用查詢日誌，記錄所有語句和指令，開啟資料庫會有約 5% 效能損失。

   (c) binlog 日誌：二進位格式，記錄所有變更資料的語句，主要用於 slave 複製和資料復原。
   (d) slow 日誌：記錄所有執行時間超過 long_query_time 秒的查詢或不使用索引的查詢，預設為關閉。
   (e) Innodb日誌：innodb redo log、undo log，用於還原資料和撤銷操作。

從上面的介紹可以看到，目前這個問題的日誌可能在d 和b 中，看了下d 中沒有，那就只能開啟b 了，但b 對資料庫的效能有一定損耗，由於是全量日誌，量非常巨大，所以開啟一定要謹慎：

#我這裡只是每天在出問題的前後半小時開啟下全量日誌，結果沒有發現任何MySQL-client 請求到我們的業務資料庫！此日誌格式如下，記錄了所有的連線與指令：

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

MySQL 日誌分析腳本

由於凌晨是資料倉儲的業務高峰，很多問題都是在這個時候爆發，一些詭異的問題往往是過了這個村就沒這個店了，白天無法復現。如何能捕捉我們關心的日誌，方便快速的定位問題，這個是重中之重，這裡我寫了個小腳本，crontab 部署，可以選擇時間範圍開啟，每分鐘採樣一次日誌，需要說明的是general log沒事輕易開啟，否則對資料庫效能損耗較大。