Error 1205: Lock Wait Timeout

[maxhugen]

I'm using SQLyog to sync a production database to a dev db. On 4 tables, I'm getting:

Error No. 1205 Lock wait timeout exceeded; try restarting transaction

Researching the web seems to indicate that a transaction has begun, locked tables, but has not committed. One post said to SHOW PROCESSLIST; but the only processes appear to be my own, via SQLyog.

I have also tried a Restart of MySQL, but that didn't help either.

As a relative novice in MySQL, I'm stuck: I can't determine what transaction or process is locking the tables, nor how to clear this situation.

Any suggestions would be gratefully accepted!

MTIA

[peterlaursen]

After receiving this try execute SHOW ENGINE INNODB STATUS, locate the 'deadlocks' section in the output and you will see both the 'offending' (ie. the one causing the lock)  and the 'suffering' (ie. the one waiting for lock to be released) listed.

It is a possibility that you bumped into a MySQL/InnoDB bug.  What is the server version?

[Mahesh]

1) please execute

SHOW GLOBAL VARIABLES LIKE '%innodb_lock_wait_timeout%';

. and paste the result here.

It looks very much like some other application used and locked the tables (for backup for instance) while sync was running. If a transaction on an InnoDB table cannot be committed in innodb_lock_wait_timeout-time it is rolled back. Do you get the same error for the same tables if you try again?

'innodb_lock_wait_timeout' variable cannot be set dynamically – it must be set at startup (typically in configuration file).

2) Also, please give us the sample data for both source and target databases along with their structure.

3) Please tell exact MySQL server version?

FYI : http://forums.mysql.com/read.php?22,206434,246034#msg-246034

[maxhugen]

Hi Peter, Mahesh

I suspect the problem was my fault. After rebooting my workstation, I no longer had any issues. I think I may have had a second instance of the frontend application open, and halted in the middle of a transaction for debugging purposes. Thus my dev db was in a locked state, not the production db.

Many thanks for your responses.

Cheers

[Author]

Posts