How to avoid long transactions in mysql

Editor to share with you mysql how to avoid long business, I hope you have something to gain after reading this article, let's discuss it together!

1. Confirm whether set autocommit=0 is used.

This confirmation can be carried out in the test environment, open the general_log of MySQL, and then run any business logic to confirm it through the log of general_log.

2. Confirm whether there are unnecessary read-only transactions.

Some frameworks are accustomed to using begin/commit to box any statement first. Some businesses do not need to do this, but also put several select statements into the transaction. This kind of read-only transaction can be removed.

3. When the business connects to the database, control the maximum execution time of each statement to avoid the unexpected execution of a single statement for too long.

Example

#! / bin/bash mysql-N-h227.0.0.1-userver_234-pserver_234-P8002-e "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 an inner joininformation_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 | | while read a b c d e f g hdoif ["$c"-gt 30] thenecho $(date + "% y-%m-%d% h:%m:%s") echo "processid [$d] $estrangf in db [$g] hold transaction time $c sql:$h" fidone > > / tmp/longtransaction.txt after reading this article, I believe you have a certain understanding of "how to avoid long transactions in mysql". If you want to know more about it, welcome to follow the industry information channel. Thank you for reading!