mysql error: ERROR 1205 (HY000): Lock wait timeout exceeded

SiemensSEN
SiemensSEN used Ask the Experts™
on
Hello,
 I am trying to insert information from one table to another within the same database in mysql. However, I get the error below.

mysql> insert into dev_text
    -> select distinct text.* from text ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Thanks in advance for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Judging by the error message, is there another process looking at that table? some sort of lock already on that table or a record?

Author

Commented:
I am sorry but how can I tell if another process is locking this table

Commented:
are there any other sql statments running at the same time?
also try
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
insert into dev_text
    select distinct text.* from text ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

Ref: http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Top Expert 2006
Commented:
doin a bit of checking, what is the value of your timeout

http://mysqldatabaseadministration.blogspot.com/2006/12/mysql-replication-stopped-lock-wait.html

The timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. Beginning with MySQL 4.0.20 and 4.1.2, InnoDB notices locks set using the LOCK TABLES statement. Before that, if you use the LOCK TABLES statement, or other transaction-safe storage engines than InnoDB in the same transaction, a deadlock may arise that InnoDB cannot notice. In cases like this, the timeout is useful to resolve the situation. The default is 50 seconds.
Top Expert 2006
Commented:
running this command shows the number of db connections  SHOW PROCESSLIST;

Author

Commented:
I don't have any other process running.

Also, the timeout parameter is commented  in my.cnf file because I use the MYISAM engine.
President
Commented:
Hi,

It seems that your transaction tabs a long time to execute and times out. Please try to modify innodb_lock_wait_timeout value.
 
Here's the information from the MySQL 5.1 Reference Manual:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

Excerpt from the manual:

innodb_lock_wait_timeout
Command-Line Format       --innodb_lock_wait_timeout=#
Option-File Format       innodb_lock_wait_timeout
Option Sets Variable       Yes, innodb_lock_wait_timeout
Variable Name       innodb_lock_wait_timeout
Variable Scope       Both
Dynamic Variable       Yes
        Permitted Values
Type       numeric
Default       50
Range       1-1073741824

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.

Cheers,
K.
 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial