What is the big deal

A transaction that has not been committed for a long time can be called a large transaction

Causes of large transactions

  • The operation has a lot of data
  • Lots of lock contention
  • There are other non-DB time-consuming operations in the transaction
  • .

The impact of big things

  • In concurrent cases, the database connection pool is easily overwhelmed
  • Locking too much data causes a lot of blocking and lock timeouts
  • The execution takes a long time, which may cause primary/secondary delay
  • The rollback takes a long time
  • The undo log inflation
  • .

How do I query large transactions

Note: The SQL operations in this article are based on mysql5.7 version

For example, query transactions that take more than 10 seconds to execute:

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

How to avoid big things

General solution

  • Avoid processing too much data at once in a transaction

  • Avoid unnecessary queries within a transaction

  • Within a transaction, avoid actions that take too long and cause the transaction to time out. Some non-DB operations, such as RPC calls and message queue operations, should be kept outside of transactions

Solution based on mysql5.7

  • In InnoDB transactions, row locks are added when they are needed, but are not released immediately when they are not needed, but wait until the end of the transaction. If you need to lock more than one row in your transaction, put the locks that are most likely to cause lock conflicts and affect concurrency as far back as possible

  • You can run the SETMAX_EXECUTION_TIME command to control the maximum query duration for each statement

  • Monitor the Information_schema. Innodb_trx table and set the long transaction threshold to alarm/kill if it is exceeded

  • In the business function testing phase, all general_logs are required to be outputted to analyze the log behavior to find problems in advance

  • Set innodb_unDO_TABLespaces to separate undo logs into separate tablespaces. This makes it easier to clean up if a rollback segment is too large due to a really large transaction

Appendix Query transaction related statements

Note: SQL statements are based on mysql5.7 version

Select t.*,to_seconds(now())-to_seconds(t.trx_started) IDLE_time from information_schema.innodb_trx t SQL select now(),(UNIX_TIMESTAMP(now()) -unix_timestamp (a.t_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; 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; Innodb_lock_waits select * from sys.innodb_lock_waits 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.idCopy the code

reference

MySQL- Long transaction details

Interviewer: Do you know the problems that come with big things and how to solve them?