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

HOW TO FORCE UNLOCK for locked tables in MySQL:

Breaking locks like this may cause atomicity in the database to not be enforced on the sql statements that caused the lock.

This is hackish, and the proper solution is to fix your application that caused the locks. However, when dollars are on the line, a swift kick will get things moving again.

1) Enter MySQL

mysql -u your_user -p

2) Let's see the list of locked tables

mysql> show open tables where in_use>0;

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

mysql> show processlist;

4) Kill one of these processes

mysql> kill <put_process_id_here>;