Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction

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.

like image 889
Jason Swett Avatar asked Apr 29 '11 19:04

Jason Swett


People also ask

What Causes lock wait timeout MySQL?

The common causes are: The offensive transaction is not fast enough to commit or rollback the transaction within innodb_lock_wait_timeout duration. The offensive transaction is waiting for row lock to be released by another transaction.

How can I tell if a MySQL table is locked?

In MySQL, locked tables are identified using the SHOW OPEN TABLES command. In its simplest form is displays all locked tables. All open tables in the table cache are listed, but the IN_USE column indicates of the table is locked. When the first lock is taken, the value increments to 1.

What is a lock wait time exceeded error?

When you establish a connection for a transaction, you acquire a lock before performing the transaction. If not able to acquire the lock, then you try for sometime. If lock is still not obtainable, then lock wait time exceeded error is thrown. Why you will not able to acquire a lock is that you are not closing the connection.

Why am I getting lock timeout error when rolling back a query?

If you issue another query before the killed query is done rolling back, you might get a lock timeout error. That's what happened to me. The solution was just to wait a bit.

Why is my thread getting timed out in SQL?

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. after the event (in SQL editor). Ideally do this on a quiet test-machine.

Why does autocommit disable transactions?

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. after the event (in SQL editor).


2 Answers

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>; 
like image 167
Eric Leschinski Avatar answered Sep 21 '22 17:09

Eric Leschinski


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.

like image 27
MarkR Avatar answered Sep 19 '22 17:09

MarkR