Shulou(Shulou.com)06/03 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.