Getting Lock wait timeout exceeded try restarting transaction even though I m not using a transaction

Question

I'm running the following MySQL UPDATE statement:

mysql> update customer set account_import_id = 1; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I'm not using a transaction, so why would I be getting this error? I even tried restarting my MySQL server and it didn't help. The table has 406,733 rows.

Answer 1

 To answer your question, I have written down some steps which should help you force unlock for the locked tables in MySQL. In order to break the locks which causes an atomicity in the database, but not to enforce on the SQL statements that caused the lock as it is a proper solution to resolve your application causing the locks. That being said, when there are dollars on the line, things will get moving swiftly again. Enter MySQL as mentioned below:- 

mysql -u your_user -p

Following which, let us see the list of locked tables using:-

mysql> show open tables where in_use>0;

Let's see the list of the current processes, one of them is locking your table(s):-
 

mysql> show processlist;

Finally, kill one of these processes:-
 

mysql> kill <put_process_id_here>;

Hope this helps!

Enroll in Web Development Course online to learn more in detail.

Thank You!