CODEWITHSUNDEEP
mysqldatabase
Adrian Dunston
Adrian Dunston

Reputation: 2940

MySQL: "lock wait timeout exceeded"

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?

Upvotes: 12

Views: 47435

Answers (4)

Eric Cope
Eric Cope

Reputation: 877

I had the same issue, a rogue transaction without a end. I restarted the mysqld process. You don't need to truncate a table. You may lose data from that rogue transaction.

Upvotes: 3

Lars Bohl
Lars Bohl

Reputation: 1011

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.

Upvotes: 6

Matt Solnit
Matt Solnit

Reputation: 33534

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.

Upvotes: 27

Erik
Erik

Reputation: 4105

Guessing: truncate table bundle_inclusions

Upvotes: 2

Related Questions