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 the 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

As you have used a transaction, auto-commit won't we able for transactions.

Autocommit: This only makes transactions automatically commit at the end of the statement. 

What is happening here's, another thread is holding a record lock on some record (you're changing each record within the table!) for too long, and your thread is getting timed out.

You can see a lot of details about the event when you issue this:

SHOW ENGINE INNODB standing

After the event (in SQL editor). Ideally, try this on a quiet test-machine.

If you wish to learn What Is SQL then visit this SQL Course.