Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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