061214 21:17:41 [ERROR] Slave: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'fl_db1'. Query: 'UPDATE user SET total_photos = total_photos + 1 WHERE user_name = '666damy666'', Error_code: 1205
061214 21:17:41 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'db2-bin.003509' position 342646547
I tried to learn more about the error code using perror and got
-bash-3.00$ perror 1205
Illegal error code: 1205
Information about the error is available on MySQL server error messages page of the manual.
Error: 1205 SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT)
Message: Lock wait timeout exceeded; try restarting transaction
The InnoDB error codes page lists the following information:
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
So how can we prevent that from happening?
This happens because the value of innodb_lock_wait_timeout was not enough for InnoDB to acquire the lock. On my servers it was set to default: 50.
The manual has following information:
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.
I am going to investigate reasons behind the lock wait timeout exceeding. For now, let's see if increasing innodb_lock_wait_timeout to 120 will be enough on this slave server.
Unfortunately, we need to restart the server for the new values to take effect.
3 comments:
This has happened to me on 4.0.26. It was not a problem in 4.0.20. My problem occurs because other sessions were doing 'insert ... select foo' where the SQL replication thread was trying to update foo. In some versions of MySQL, InnoDB will get S locks on foo. This is required on the master to guarantee consistency of the transaction between a master on slaves. But the lock was also obtained when the binlog was disabled. See ha_innobase::store_lock for details.
Hi Frank!
slave_transaction_retries (default value 10) was added in 4.1.11 to help fix this issue. The SQL thread should retry the transaction 10 times before giving up. If this is set to the default value The SQL retried 10 times before giving up. This means that a lock was held for ~500 seconds. Try to find what is causing this lock and fix it. 500 seconds is a long time to hold on to a lock.
where I have to mention this lock time out property i am using java I didn't found any option to set this.is this should be done on mysql side
Post a Comment