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
Best Answer
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.