3

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

  • Can you add the output of SHOW CREATE TABLE PM_SPEC_DOC_APPROVE to the question, as well as SHOW FULL PROCESSLIST while the second one is running (the one that times out)? – Derek Downey Jul 25 '12 at 14:25
3

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.

  • Incrementing value to 120 does not help either, better check with Show engine innodb status; query output – Mahesh Patil Jul 25 '12 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.