0

After updating mysql I get this error

 General error: 1205 Lock wait timeout exceeded; try restarting transaction;

Haven't got the error before and havn't changed the application in the meantime..

Sometimes I have to restart mysql server before I get write access to the table row. Even after several minutes/hours the row is still locked. It locks forever

mysql 5.6.28

I can show all processes and kill the one sleeping, but thats not very handy if I have to do this manually multiple times a day

SHOW PROCESSLIST;

The last process I killed had been running for 1300 secs

How to debug the issue? Until now it has always been the same table/query that locks a row

The lock wait always shows up when a user login.. So I have logged all write SQL queries in the login operation

2015-12-16 13:29 --- REQUEST: action login 
2015-12-16 13:29 BEGIN 
2015-12-16 13:29 INSERT `log_login` SET distributor_id=1,ip=3572642013,time=1450272590,is_error=0,is_client=1,is_distributor=0,is_supervisor=0,is_supervisor_internal=0,client_id=1,user_id=1,distributor_user_id=,supervisor_id=,screen_width=1920,screen_height=1080,country_id=57,city_id=569,browser_id=271,os_id=1 
2015-12-16 13:29 UPDATE `client` SET time_log=1450272590 WHERE id=1 && distributor_id=1 
2015-12-16 13:29 UPDATE `user` SET time_log=1450272590 WHERE id=1 && distributor_id=1 && client_id=1 
2015-12-16 13:29 UPDATE `log_login` SET time_active=0 WHERE id=37771 
2015-12-16 13:29 COMMIT

It doesn't look like the script writes to the client table more than one time

When the lock wait appears this is the row/query that locks (client)

2015-12-16 13:29 UPDATE `client` SET time_log=1450272590 WHERE id=1 && distributor_id=1
clarkk
  • 27,151
  • 72
  • 200
  • 340
  • Well, apparently your table is locked by something or there's a transaction that's not properly closed and is hence keeping the table open. All in all - sounds like an application issue to me. – favoretti Dec 16 '15 at 12:02
  • What does `SHOW ENGINE INNODB STATUS` say? Maybe there's a clue there as to why this lock/deadlock occurred. Also, does your application code support transaction restart in case it fails? – Mjh Dec 16 '15 at 13:58

0 Answers0