MySQL為什么要避免大事務(wù)以及大事務(wù)解決的方法
什么是大事務(wù)
運行時間比較長,長時間未提交的事務(wù)就可以稱為大事務(wù)
大事務(wù)產(chǎn)生的原因
- 操作的數(shù)據(jù)比較多
- 大量的鎖競爭
- 事務(wù)中有其他非DB的耗時操作
- 。。。
大事務(wù)造成的影響
- 并發(fā)情況下,數(shù)據(jù)庫連接池容易被撐爆
- 鎖定太多的數(shù)據(jù),造成大量的阻塞和鎖超時
- 執(zhí)行時間長,容易造成主從延遲
- 回滾所需要的時間比較長
- undo log膨脹
- 。。。
如何查詢大事務(wù)
**注**:本文的sql的操作都是基于mysql5.7版本
以查詢執(zhí)行時間超過10秒的事務(wù)為例:
select \* from information\_schema.innodb\_trx where TIME\_TO\_SEC(timediff(now(),trx\_started))>10
如何避免大事務(wù)
通用解法
- 在一個事務(wù)里面, 避免一次處理太多數(shù)據(jù)
- 在一個事務(wù)里面,盡量避免不必要的查詢
- 在一個事務(wù)里面, 避免耗時太多的操作,造成事務(wù)超時。一些非DB的操作,比如rpc調(diào)用,消息隊列的操作盡量放到事務(wù)之外操作
基于mysql5.7的解法
- 在InnoDB事務(wù)中,行鎖是在需要的時候才加上的,但并不是不需要了就立刻釋放,而是要等到事務(wù)結(jié)束時才釋放。**如果你的事務(wù)中需要鎖多個行,要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放**
- 通過SETMAX_EXECUTION_TIME命令, 來控制每個語句查詢的最長時間,避免單個語句意外查詢太長時間
- 監(jiān)控 information_schema.Innodb_trx表,設(shè)置長事務(wù)閾值,超過就報警/或者kill
- 在業(yè)務(wù)功能測試階段要求輸出所有的general_log,分析日志行為提前發(fā)現(xiàn)問題
- 設(shè)置innodb_undo_tablespaces值,將undo log分離到獨立的表空間。如果真的出現(xiàn)大事務(wù)導(dǎo)致回滾段過大,這樣設(shè)置后清理起來更方便
附錄查詢事務(wù)相關(guān)語句
**注**:sql語句都是基于mysql5.7版本
# 查詢所有正在運行的事務(wù)及運行時間 select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t # 查詢事務(wù)詳細(xì)信息及執(zhí)行的SQL select now(),(UNIX\_TIMESTAMP(now()) - UNIX\_TIMESTAMP(a.trx\_started)) diff\_sec,b.id,b.user,b.host,b.db,d.SQL\_TEXT from information\_schema.innodb\_trx a inner join information\_schema.PROCESSLIST b on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = 'Sleep' inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID inner join performance\_schema.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID; # 查詢事務(wù)執(zhí)行過的所有歷史SQL記錄 SELECT ps.id 'PROCESS ID', ps.USER, ps.HOST, esh.EVENT\_ID, trx.trx\_started, esh.event\_name 'EVENT NAME', esh.sql\_text 'SQL', ps.time FROM PERFORMANCE\_SCHEMA.events\_statements\_history esh JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id LEFT JOIN information\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id WHERE trx.trx\_id IS NOT NULL AND ps.USER != 'SYSTEM\_USER' ORDER BY esh.EVENT\_ID; # 簡單查詢事務(wù)鎖 select \* from sys.innodb\_lock\_waits # 查詢事務(wù)鎖詳細(xì)信息 SELECT tmp.\*, c.SQL\_Text blocking\_sql\_text, p.HOST blocking\_host FROM ( SELECT r.trx\_state wating\_trx\_state, r.trx\_id waiting\_trx\_id, r.trx\_mysql\_thread\_Id waiting\_thread, r.trx\_query waiting\_query, b.trx\_state blocking\_trx\_state, b.trx\_id blocking\_trx\_id, b.trx\_mysql\_thread\_id blocking\_thread, b.trx\_query blocking\_query FROM information\_schema.innodb\_lock\_waits w INNER JOIN information\_schema.innodb\_trx b ON b.trx\_id = w.blocking\_trx\_id INNER JOIN information\_schema.innodb\_trx r ON r.trx\_id = w.requesting\_trx\_id ) tmp, information\_schema.PROCESSLIST p, PERFORMANCE\_SCHEMA.events\_statements\_current c, PERFORMANCE\_SCHEMA.threads t WHERE tmp.blocking\_thread = p.id AND t.thread\_id = c.THREAD\_ID AND t.PROCESSLIST\_ID = p.id
以上就是MySQL避免大事務(wù)以及大事務(wù)解決的方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL 大事務(wù)的資料請關(guān)注腳本之家其它相關(guān)文章!
- 詳解MySQL中事務(wù)隔離級別的實現(xiàn)原理
- MySQL執(zhí)行事務(wù)的語法與流程詳解
- mysql、oracle默認(rèn)事務(wù)隔離級別的說明
- MySQL 事務(wù)autocommit自動提交操作
- MySQL 查看事務(wù)和鎖情況的常用語句分享
- MySQL 主從同步,事務(wù)回滾的實現(xiàn)原理
- MySQL數(shù)據(jù)庫事務(wù)與鎖深入分析
- Mysql事務(wù)中Update是否會鎖表?
- 深入理解PHP+Mysql分布式事務(wù)與解決方案
- MySQL如何實現(xiàn)事務(wù)的ACID
- 詳解MySQL中事務(wù)的持久性實現(xiàn)原理
相關(guān)文章
Mysql中報錯函數(shù)floor()函數(shù)和rand()函數(shù)的配合使用及原理詳解
在項目中的SQL語句中遇到幾個數(shù)值處理函數(shù),看著有些懵,就小小的總結(jié)一下,這篇文章主要給大家介紹了關(guān)于Mysql中報錯函數(shù)floor()函數(shù)和rand()函數(shù)的配合使用及原理的相關(guān)資料,需要的朋友可以參考下2022-07-07MySQL 5.6 中的 TIMESTAMP 和 explicit_defaults_for_timestamp 參數(shù)
這篇文章主要介紹了MySQL 5.6 中的 TIMESTAMP 和 explicit_defaults_for_timestamp 參數(shù),需要的朋友可以參考下2015-08-08mysql實現(xiàn)設(shè)置定時任務(wù)的方法分析
這篇文章主要介紹了mysql實現(xiàn)設(shè)置定時任務(wù)的方法,結(jié)合實例形式分析了mysql定時任務(wù)相關(guān)的事件計劃設(shè)置與存儲過程使用等操作技巧,需要的朋友可以參考下2019-10-10