MySQL: "lock wait timeout exceeded"

Question

I am trying to delete several rows from a MySQL 5.0.45 database:

delete from bundle_inclusions;

The client works for a while and then returns the error:

Lock wait timeout exceeded; try restarting transaction

It's possible there is some uncommitted transaction out there that has a lock on this table, but I need this process to trump any such locks. How do I break the lock in MySQL?

Answer 1

Linux: In mysql configuration (/etc/my.cnf or /etc/mysql/my.cnf), insert / edit this line

innodb_lock_wait_timeout = 50

Increase the value sufficiently (it is in seconds), restart database, perform changes. Then revert the change and restart again.

Answer 2

I agree with Erik; TRUNCATE TABLE is the way to go. However, if you can't use that for some reason (for example, if you don't really want to delete every row in the table), you can try the following options:

• Delete the rows in smaller batches (e.g. DELETE FROM bundle_inclusions WHERE id BETWEEN ? and ?)
• If it's a MyISAM table (actually, this may work with InnoDB too), try issuing a LOCK TABLE before the DELETE. This should guarantee that you have exclusive access.
• If it's an InnoDB table, then after the timeout occurs, use SHOW INNODB STATUS. This should give you some insight into why the lock acquisition failed.
• If you have the SUPER privilege you could try SHOW PROCESSLIST ALL to see what other connections (if any) are using the table, and then use KILL to get rid of the one(s) you're competing with.

I'm sure there are many other possibilities; I hope one of these help.