Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction
Solution 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>;
Solution 2:
You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statement.
What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.
You can see more details of the event by issuing a
SHOW ENGINE INNODB STATUS
after the event (in SQL editor). Ideally do this on a quiet test-machine.
Solution 3:
mysql> set innodb_lock_wait_timeout=100;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100 |
+--------------------------+-------+
Now trigger the lock again. You have 100 seconds time to issue a SHOW ENGINE INNODB STATUS\G to the database and see which other transaction is locking yours.
Solution 4:
Take a look on if your database is fine tuned. Especially the transactions isolation. Isn't good idea to increase the innodb_lock_wait_timeout variable.
Check your database transaction isolation level in the mysql cli:
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)
You could get improvements changing de isolation level, use the oracle like READ COMMITTED instead REPEATABLE READ (InnoDB Defaults)
mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql>
Also try use SELECT FOR UPDATE only in if necesary.
Solution 5:
None of the suggested solutions worked for me but this did.
Something is blocking the execution of the query. Most likely another query updating, inserting or deleting from one of the tables in your query. You have to find out what that is:
SHOW PROCESSLIST;
Once you locate the blocking process, find its id and run :
KILL {id};
Re-run your initial query.