Search code examples
mysqlperformancetimeoutlocking

ERROR 1205 (HY000): Lock wait timeout exceeded on update mysql

I am running following update -

update table_x set name= 'xyz' where id = 121;

and getting - ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I googled it number of times and adding extra time to innodb_lock_wait_timeout not helping me out.

Please let me know the root cause of this issue and how I can solve it. I am using mysql 5.6(master-master replication) on dedicated server. Also table_x(Innodb table) heavily used in database. Autocommit is on.

Solution

  • I have solved the problem. I tried different values for innodb_lock_wait_timeout, also tried to change queries but got the same error. I did some research and asked my colleagues about hibernate.

    They were doing numbers of transaction which include updating main table and committing in the end. So, I suggested them to use commit on each transaction. Finally I am not getting any lock wait time out errors.