SQL Error (1205): Lock wait timeout exceeded; try restarting

SQL Error (1205): Lock wait timeout exceeded; try restarting

SQL Error (1205): Lock wait timeout exceeded; try restarting - Hello. Back again with me Bangkit Ade Saputra, this time I will share one article from my experience, which happened to be just used earlier after looking for a solution to the problem "SQL Error (1205): Lock wait timeout exceeded; try restarting the transaction ”.

Issue

When I update the row in a database table, an error occurs with the output error as I said above.

Cause

For this problem, I can conclude that the problem is that there is a database user who orders the update row command and during the update process there is a technical problem, in my case this user lost his laptop internet connection which is connected to his database server. So that it causes the command that is in progress to hang or pause in the middle.

Resolution

For the issues and causes above, you must first shut down the process that was started by the last database user who executed it and restart the command from the beginning.

Because in this case I have got the last executing data, so it's clear, and I just have to look in the database for the PID of the process by:

mysql > SHOW FULL PROCESSLIST;

Show Full Processlist

After seeing whether or not the user depends on a process, if there are many you can also filter again specifically for that user to get the pid of the many processes and it is certain that the user executes by:

mysql > SELECT concat('KILL ',id,';') from information_schema.processlist where user='kitsake';

After getting the PID process, then we kill.

mysql > KILL 635125; 

Trying

If you have killed all the PIDs, the process must first make sure that all PIDs of the user are clear or that there are no more problems;

mysql> SHOW FULL PROCESSLIST;

If you are sure, then you can hit the command again that was blocked by this one error.

For this case my command is :

mysql> UPDATE T_TRANSACTION SET PRODUCT_VALUE = "123456789" WHERE TX_ID = "ABC12345678";