MySQL regular log row error

MySQL version: 5.0.82

In the test environment, the developer told me that the database could not insert the insertion prompt lock wait timeout;

The first impression is to be locked by a statement, to perform show full processlist to see the actions performed on the same table, to view shows engine InnoDB status\g only display the locked statements, Not shown which statement is locked (in the official 5.5 version is also shown in this case, in the Mariadb branch, the display of more detailed information!) ）；

This time to take the approach is to open the InnoDB Monitor, Innodb_lock_monitor 16s detection, in the test process still nothing; At this time we can only start to suspect OS problems, CPU load, disk IO; Because the network is isolated, initially suspected of network reasons, Causes the connection to be often broken, adjusted to the same network is still invalid, and then all the data mysqldump out (this eliminates the problem of disk corruption, at least read)

This situation can only be suspected of the problem of the program, Java using a connection pool, the original suspected that an uncommitted thread has been reused; this time, the general log was opened; The format shown is: Time thread ID SQL statement; we have a big harvest. By comparing COM MIT and set autocommit the number of different, corresponding to the specific thread, you can know which thread uncommitted transaction; the rest of the time is for developers to modify the code.

Summary: For the official original database. 5.5 and the following version of the lock information display is not comprehensive, can be switched to MARIADB version; For lock information viewing, you can use the new features in 5.5: Performance_schema, this lock problem is very convenient; general log to make good use of, he is a diagnostic fault, Test the performance of life-saving straw!!

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1200443

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/