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

This article introduces how MYSQL discovers and deals with the big trouble left by commit. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

In fact, there is nothing wrong with using different databases to develop applications, but if developers are not familiar with the databases used and still use the way they are familiar with databases to deal with new databases, it will obviously cause a lot of trouble. the same is true for other professions.

What I want to talk about today is how the trouble left by programmers who are accustomed to using ORACLE can be found in MYSQL. The two databases are different in dealing with transactions. Oracle does not automatically commit by default, while mysql defaults to auto commit. Auto commit is the only one of the four major databases that does not default to commit.

What's the problem? if you set auto commit to non-automatic (thread level, or global) on the programmer's use of mysql, and for some later reason, forget to remember that MYSQL itself is auto commit by default. So you will generally look at the history of developer, if you have not used mysql in the history of development, you will pay more attention to it.

Here is an example. There is an update in the system that has been unable to pass and has been reported.

Lock wait timeout exceeded; try restarting transaction

When you encounter such a problem, what will you think of and how to deal with it? The first idea is to take a look.

Show engine innodb stauts

See the figure above, what is the reflection? some threads occupy the row lock of some records for too long, so that other session cannot manipulate the corresponding records. If you think deeply, it may be the problem of session idel and transaction running caused by the absence of commit.

If you encounter such a problem, you need to find out the status of the current session in idel but actually the transaction in running.

1 find the session that is in sleep

2 View the statements running in the time-consuming session

By looking at the longer time-consuming statements, as well as the processlist_id of the statements, you can roughly find the thread ID that is currently acting as a demon.

And then kill him.

Of course, there is another situation, that is, due to the lack of rigor in the program, there are a large number of begin but no commit, so there is no rush with the above method, how to find such a problem more quickly

Through the above statement, to find out whether the relevant counters are rising crazily, it proves that there is or may be such a problem in the current database system.

On how to find and deal with MYSQL without commit left big trouble to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.