jlzzjlzz亚洲乱熟在线播放

系統城裝機大師 - 唯一官網:www.farandoo.com!

當前位置:首頁 > 數據庫 > MsSql > 詳細頁面

mysql死鎖和分庫分表問題詳解

時間:2021-04-19來源:www.farandoo.com作者:電腦系統城

業務場景與問題描述

請求一個外部接口時,每天的請求量在900萬左右。

分為請求項目和回執這兩個項目。請求是用來調用外部接口,回執是接收發送的接口。

在發送請求前會先插入數據庫。

在請求后,如果接口返回調用失敗,會更新數據庫狀態為失敗。

如果發送成功,則會等待上游給出回執消息后,然后更新數據庫狀態。

而在生產運行過程中,半年出現過兩次mysql導致的mq消費者堆積的問題。

問題分析

記錄兩次不同的原因導致的生產問題及原因分析。

mysql死鎖問題

查看mq聚合平臺TPS
上生產發現mq數據一直堆積,且不斷上升。而TPS僅為30左右,一直上不去。

這就會使mq消費變慢了,導致不斷堆積。具體什么原因導致mq一直堆積,需要繼續排查。

查看生產服務器日志

查看生產服務器日志,發現有報錯dead Lock的錯誤。

1 error response from MySQLConnection [node=24, id=277499, threadId=2735941, state=borrowed, closed=false, autocommit=true, host=10.1.10.74, port=3306, database=sep_4, localPort=27744, isClose:false, toBeClose:false, MySQLVersion:5.7.25], err: Deadlock found when trying to get lock; try restarting transaction, code: 1213

具體的sql如下:

1 update stage set status = 'success',reply_time = '2021-03-07 10:40:11'  where code = '000123' and create_time > '2021-03-03 00:00:00';

也就是說在執行服務時出現了死鎖的情況。

具體有多少條以及耗時,在生產服務器看著不直觀,于是就讓dba將慢sql的語句和耗時查出來。

查出后發現最長的慢sql的耗時長達7780ms。

仔細查看會發現,sql會發現相同的id一個在執行中,一個在Lock Wait狀態。

而這慢sql中有大量的Lock Wait狀態。

什么原因導致的死鎖

mysql使用的數據庫引擎時InnoDB。先了解下什么是死鎖:

所謂死鎖: 是指兩個或兩個以上的進程在執行過程中,
因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.
此時稱系統處于死鎖狀態或系統產生了死鎖,這些永遠在互相等竺的進程稱為死鎖進程.

通過上面的排查可以看出,出現死鎖的問題就是:

在執行sql更新一條數據時,會將這一行數據鎖定,執行完成后會釋放行鎖,而沒有執行的sql處于Lock Wait狀態。

而程序中導致此原因在于,在發送前后和回執時,頻繁操作數據庫,可能會出現同時操作同一條數據的情況。

所以在執行中就出現了鎖等待的情況。

分庫分表未帶分片鍵

首先告警的是stage_prod庫的CPU飆到了85%。

數據庫線程數是否被打滿

經過查看數據庫連接情況可知,數據庫連接數并沒有被占滿。

查出慢sql和耗時

查出的問題sql:

1 update stage set status = 'success',reply_time = '2021-03-07 10:40:11'  where create_time > '2021-03-03 00:00:00';

查看sql會發現,這條sql竟然沒有帶分片鍵code字段。而這條sql是回執時執行的。

排查生產服務器日志

代碼中有做判斷,如果code值不為空,sql會帶上code的值。那么沒帶上,就需要查看為何沒有帶上。

查看代碼會發現,code是從redis中獲取的,是在發送時set到redis中的。但是沒有set進去就很奇怪了。

初步懷疑是redis問題,然后就與redis維護的平臺溝通,發現果真是因為redis故障導致的問題。

為什么不帶分片鍵CPU就會飆升

首先公司用的是hotdb分庫分表,因為每天的入庫量是在900萬左右,一個表是上億條數據。

如果只是單純用索引,是無法滿足要求的。

分庫分表hotdb,根據code值做hash分片,做了64個分片。也就是說64個數據庫,分布在8臺服務器上的16個實例里面。

這樣可以避免各分片數據不均,理論上避免了過度集中在某個分片上。

而如果不帶分片鍵code的sql,所有的dml操作全部下發到所有的底層庫上進行執行,相當于遍歷了一遍庫。

這樣就可能會導致CPU直接飆到99%,甚至直接導致服務器直接崩掉,這樣操作是很可怕的。

解決辦法

應急處理:先停掉幾臺服務減少數據庫操作

數據持續堆積,會影響數據處理速度。那么,就要先降低操作的速度,最快速的辦法就是停服務,減少數據庫的操作頻率。

減少數據庫操作避免數據庫死鎖

死鎖一般時由于程序上沒有控制好dml操作的提交,沒有及時提交.

減少重復操作同一條數據。在批量操作時減少每批dml數,保證快速提交,避免長事務,避免重復提交dml。

那么怎樣減少操作呢?

合并sql

將發送前插入和發送失敗時更新,直接合并到一條sql,這樣就可以避免多次操作同一條數據的情況。

批量執行時減少長事務和條數

執行時發現,每次批量執行20條sql,比一次性執行200條的效率更快。

所以盡可能避免這種問題。

每條sql必須帶分庫分表分片鍵

原則就是不能因為一條數據就拖累整個數據庫的操作速度。

分片鍵必須帶上,如果不帶分片鍵,就拋錯。

增加時間區間開閉區間

用code來做分片鍵,用createTime做分區。那么在保證code存在的情況下,可以寫上開閉區間,可以提高執行效率。

更優解:sql順序執行

這種方案可以通過把將要執行的sql統一發到一個mq來消費執行,這樣可以保證sql順序執行,從而避免死鎖的產生。

但是這個需要根據業務場景來區分。

復盤

mysql死鎖問題,要盡可能避免頻繁操作同一條數據,也要避免長事務;
針對分庫分表問題,一定要帶上分片鍵;
監控機制不可少;

分享到:

相關信息

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載