Mysql入門細說MySQL死鎖與日志二三事

作者：VEPHP 時間 2017-10-27

《Mysql入門細說MySQL死鎖與日志二三事》要點：
本文介紹了Mysql入門細說MySQL死鎖與日志二三事，希望對您有用。如果有疑問，可以聯系我們。

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

Case1：部分數據更新失敗MYSQL學習

某天渠道同學反饋某報表極個別渠道數據為 0,大部分渠道數據正常.這個數據是由一個統計程序每天凌晨例行更新的,按理來說,要么全部正常,要么全部失敗,那會是什么原因導致極個別數據異常呢?MYSQL學習

首先我們能想到的自然是根據統計任務日志來看了,但是看了統計程序打印的日志沒有發現諸如 SQL update 失敗的異常描述,那當時的數據庫究竟發生了什么呢?在查看 MySQL-server 日志之前,習慣性的看了下數據庫狀態：MYSQL學習

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

恰好看到了凌晨這個 update 發生了死鎖：MYSQL學習

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

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

那這里就有 3 個問題了：MYSQL學習

1、innodb 行鎖不是只鎖一行?MYSQL學習

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

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

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

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

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

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

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

回滾的話,為什么只有部分 update 語句失敗,而不是整個事務里的所有 update 都失敗?MYSQL學習

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

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

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

需要注意的是,通常還有另外一種情況也可能導致部分語句回滾,需要格外把穩.在 innodb 里有個參數叫：innodb_rollback_on_timeoutMYSQL學習

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

官方手冊里這樣描述：MYSQL學習

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學習

解釋：這個參數關閉或不存在的話遇到超時只回滾事務最后一個Query,打開的話事務遇到超時就回滾整個事務.MYSQL學習

3、怎樣降低 innodb 死鎖幾率?MYSQL學習

死鎖在行鎖及事務場景下很難完全消除,但可以通過表設計和SQL調整等措施減少鎖沖突和死鎖,包括：MYSQL學習

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

精心設計索引,并盡量使用索引拜訪數據,使加鎖更精確,從而減少鎖沖突的機會；MYSQL學習

選擇合理的事務大小,小事務發生鎖沖突的幾率也更小；MYSQL學習

給記錄集顯示加鎖時,最好一次性哀求足夠級別的鎖.比如要修改數據的話,最好直接申請排他鎖,而不是先申請共享鎖,修改時再哀求排他鎖,這樣容易產生死鎖；MYSQL學習

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

盡量用相等條件拜訪數據,這樣可以避免間隙鎖對并發插入的影響；MYSQL學習

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

對于一些特定的事務,可以使用表鎖來提高處理速度或減少死鎖的可能.MYSQL學習

Case2：詭異的 Lock wait timeout MYSQL學習

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

甭管誰的問題,那咱們還是先看自己的數據庫有沒有問題：MYSQL學習

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

默認 lock 超時時間 50s,這個時間真心不短了,估計調了也沒用,事實上確實死馬當活馬醫的試了下沒用...MYSQL學習

而且這次 SHOW ENGINE INNODB STATUS\G 也沒出現任何死鎖信息,然后又將目光轉向 MySQL-server 日志,希望能從日志里看一看那個時刻前后數據究竟在做什么操作.這里先簡單的介紹下MySQL日志文件系統的組成：MYSQL學習

?? (a) error 日志：記錄啟動、運行或停止 mysqld 時出現的問題,默認開啟.
?? (b) general 日志：通用查詢日志,記錄所有語句和指令,開啟數據庫會有 5% 左右性能損失.
?? (c) binlog 日志：二進制格式,記錄所有更改數據的語句,主要用于 slave 復制和數據恢復.
?? (d) slow 日志：記錄所有執行時間超過 long_query_time 秒的查詢或不使用索引的查詢,默認關閉.
?? (e) Innodb日志：innodb redo log、undo log,用于恢復數據和撤銷操作.MYSQL學習

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

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

我這里只是每天在出問題的前后半小時開啟下全量日志,結果沒有發現任何 MySQL-client 哀求到我們的業務數據庫!該日志格式如下,記錄了所有的連接與命令：MYSQL學習

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習

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

MySQL 日志分析腳本MYSQL學習

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

Mysql入門細說MySQL死鎖與日志二三事 MYSQL學習