什么是大事務(wù)

運(yùn)行時(shí)間比較長，長時(shí)間未提交的事務(wù)就可以稱為大事務(wù)

大事務(wù)產(chǎn)生的原因

操作的數(shù)據(jù)比較多 大量的鎖競爭 事務(wù)中有其他非DB的耗時(shí)操作 。。。

大事務(wù)造成的影響

并發(fā)情況下，數(shù)據(jù)庫連接池容易被撐爆 鎖定太多的數(shù)據(jù)，造成大量的阻塞和鎖超時(shí) 執(zhí)行時(shí)間長，容易造成主從延遲 回滾所需要的時(shí)間比較長 undo log膨脹 。。。

如何查詢大事務(wù)

**注**：本文的sql的操作都是基于mysql5.7版本

以查詢執(zhí)行時(shí)間超過10秒的事務(wù)為例：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10

如何避免大事務(wù)

通用解法

在一個(gè)事務(wù)里面， 避免一次處理太多數(shù)據(jù) 在一個(gè)事務(wù)里面，盡量避免不必要的查詢 在一個(gè)事務(wù)里面， 避免耗時(shí)太多的操作，造成事務(wù)超時(shí)。一些非DB的操作，比如rpc調(diào)用，消息隊(duì)列的操作盡量放到事務(wù)之外操作

基于mysql5.7的解法

在InnoDB事務(wù)中，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。**如果你的事務(wù)中需要鎖多個(gè)行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放** 通過SETMAX_EXECUTION_TIME命令， 來控制每個(gè)語句查詢的最長時(shí)間，避免單個(gè)語句意外查詢太長時(shí)間 監(jiān)控 information_schema.Innodb_trx表，設(shè)置長事務(wù)閾值，超過就報(bào)警/或者kill 在業(yè)務(wù)功能測試階段要求輸出所有的general_log，分析日志行為提前發(fā)現(xiàn)問題 設(shè)置innodb_undo_tablespaces值，將undo log分離到獨(dú)立的表空間。如果真的出現(xiàn)大事務(wù)導(dǎo)致回滾段過大，這樣設(shè)置后清理起來更方便

附錄查詢事務(wù)相關(guān)語句

**注**：sql語句都是基于mysql5.7版本

# 查詢所有正在運(yùn)行的事務(wù)及運(yùn)行時(shí)間select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t# 查詢事務(wù)詳細(xì)信息及執(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ù)鎖詳細(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)注好吧啦網(wǎng)其它相關(guān)文章！