什么是大事務(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ù)導致回滾段過大，這樣設(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ù)詳細信息及執(zhí)行的SQLselect 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 bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = ’Sleep’inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner 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ù)鎖詳細信息 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ù)解決的方法的詳細內(nèi)容，更多關(guān)于MySQL 大事務(wù)的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！