大事務

戎·碼一生發表於2020-11-23
一言以蔽之，對於MySQL資料庫來說，任何時刻不允許有大事務執行。 若要執行，則將大事務拆成一個個小的子事務來執行。這是最基本心法口訣，但卻又和Oracle有著很大不同。總之，氣宗、劍宗，本無好壞，學會理解其中的差異，融會貫通方可達風清揚般的致臻境界。
 
大事務產生場景
  1. 一次性的用delete語句刪除太多資料；
  2. 大表的DDL;
  3. 避免跨庫事務；
  4. 避免在事務中做外部呼叫：rpc、MQ，查詢，迴圈呼叫；
 
大事務的危害及避免方法？[應用端3+服務端2]
危害：
  • (1) 浪費系統記憶體：長事務意味著系統裡面會存在很老的事務檢視，在這個事務提交之前，回滾記錄都要保留，這會導致大量佔用儲存空間；
  • (2) 佔用鎖資源：造成請求擠壓，連線池滿，吞吐量下降，可能拖垮整個資料庫；
  • (3) 主從延遲：事務執行完才會寫binlog，這樣會導致主從同步延遲；
 
避免大事務方法：
應用端
  • 1、使用set autocommit=1；顯示語句來啟動事務，並且讓事務自動提交。避免長連線導致的意外長事務；
  • 2、確定是否有不必要的只讀事務。比如好幾個select語句，其實沒有必要使用事務，這樣的可以去掉。
  • 3、使用set MAX_EXECUTION_TIME=3000 來控制每個語句的執行最長時間，避免單個語句執行時間太長，出現長事務；
                   ps（不過global設下去恐怕擔心如果真有需要執行久的，（比如備份），會不會被誤傷?
                          可以考慮設定成session內有效，讓業務程式碼主動去做？）
  • 4、避免跨庫事務
  • 5、避免在事務中做外部呼叫：rpc、MQ，查詢，迴圈呼叫；
資料庫端
  • 1、監控information_schema.InnoDB_trx表，設定長事務閾值，超過就報警／記錄／kill掉；
  • 2、測試階段可以開啟general_log，分析日誌行為提前發線問題；
 
監控大事務，kill掉的sql
你可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務，比如下面這個語句，用於查詢持續時間超過 60s 的事務。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
處理策略：
  • ( 1 ) 超時設定：innodb_lock_wait_timeout，根據具體的業務場景來設定，預設50s；
  • ( 2 ) 死鎖檢測：innodb_deadlock_detect 設定為on；告警
實際經驗：可以按照順序加鎖來避免死鎖。比如先拿 t1 ，再拿 t2 ；
 
監控長事務的命令
 
問題思考：
1. 作為業務負責人要儘量避免長事務的產生:
(1)培訓業務開發人員，避免長事務
(2)在code review中仔細檢查資料庫的相關配置
(3)讓測試人員建立長事務的相關用例
 
2. 作為資料庫負責人
(1)要求業務組上生產之前必須將資料庫的相關配置、表結構設計以及SQL語句提交到DBA部門稽核，由DBA把關，從源頭杜絕長事務
(2)定期到各業務組進行資料庫相關知識的培訓
(3)建立長事務監控指標，發現之後及時報警

相關文章