Error Code: 1205 Lock wait timeout exceeded in MySQL

Question

I have set InnoDB lock wait time-out to 120 seconds.

When I run the query below for first time, it works fine. However, the second time, the same query with a different value gives me:

Error Code: 1205 Lock wait timeout exceeded; try restarting transaction

INSERT INTO PM_SPEC_DOC_APPROVE (
    SDA_SYS_NUM, 
    SDA_SPEC_SYS_NUM, 
    SDA_SYS_REV, 
    SDA_APPR_UID, 
    SDA_APPR_DATE, 
    SDA_CR_UID, 
    SDA_CR_DT, 
    SDA_SRNO, 
    SDA_NOTE) 
VALUES (
    '7414', 
    '24510', 
    NULL, 
    'E13462', 
    '2012-07-25 16:35:49', 
    'E13462', 
    '2012-07-25 16:35:49', 
    '3', 
    NULL)

We are using MySQL v5.1

Answer 1

innodb_lock_wait_timeout

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. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout option

By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.

Also You can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" on server after getting this error.