Friday, January 25, 2013

[ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction'

In situation of table/row locking or deadlock on slave server, slave server let the transaction wait for time configured with  innodb_lock_wait_timeout and retry the transaction for the times configured in  slave_transaction_retries.  After all tries and lock timeout save server throws following error in logs.

130125 13:24:30 [ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'live'. Query: 'INSERT into users (user_id, login, password, first_name, last_name, email, is, created, changed, picture, last_login, token) values (1, 'asad9@yahoo.com', '35ce1d4eb0', 'Prince A', 'S', 'asad9@yahoo.com', 1, 13, 13, 'photo_13.jpg', 13, NULL)', Error_code: 1205

Get all the deadlocks and locking transaction details and tables involved in locking using:

#mysql >show engine innodb status\G;

To avoid further locking you need to do changes at application to prevent bulk updates and select simultaneously. Also change storage engine MyISAM which use table locking to InnoDB which use row level locking.

The simple solution for this problem is to increase the value for innodb_lock_wait_timeout and slave_transaction_retries.

 There are other possible reason for this error: table is corrupt so repair the table.

http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_lock_wait_timeout.html
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#sysvar_slave_transaction_retries

No comments:

Post a Comment