Re: "Lock wait timeout exceeded" time out on insert.
Posted by:
Nic Whale
Date: January 26, 2012 05:27AM
RJ>"275460 locked 4 rows (and maybe gaps) before rolling back. Since it took more than 3 minutes, that is a lot of time to be hanging onto locks. I am unclear on whether it actually unlocked and rolled back each INSERT because of the 50-second timeout. Can you tell if those rows were actually inserted?"
NW> Correct, this is the same query timing out for 50 seconds and then trying again.
RJ>Do you check for errors after _every_ Query?
NW> I do not explicitly check - but just let the JDBC driver take control of this. So in my code i do try the action 4 times before throwing an exception which would then cause the transaction to roll back. So there are some times (ie say 50%) where it does not fail all 4 times - but this means it times out 1/2/3 times - which is still a long time.
RJ>OK, maybe that is the same INSERT 4 times?
NW> correct
RJ>(I can't tell because the queries are truncated.)
NW> Sorry just trying to get the balance right between enough and not too much data.
RJ>You imply that the code does check for errors, tries 4 times, then gives up.
NW> Correct.
RJ>Let's try the following, in an attempt to solve your problem:
NW> So are you suggesting instead of using the methods provided by the Java objects like "conn.setAutoCommit(false);" and "conn.commit();" etc - I should use the explicit MySQL commands?
NW> So if you do not want to use standard Java methods - is this because you think there is less chance of problems using what you suggest? Or the replacement process may higlight something in the code? The main issue is that I can rarely reproduce this in our test system so this must be done in our production system, and there is a lot of changes required. So I need to think about this .... see final point below.
RJ>"* Be sure to keep transactions "short", less than, 1 second in normal situations"
NW> As a general rule this is the case.
RJ> "In particular, do not hang onto a 'transaction' across a user interaction!"
NW> Never!
NW> Thanks again for the continual support and feedback on this. Sorry if there are delays in getting back to you (and I will need to delay implementing your suggestions until I consider the consequences), mainly cause this has been an ongoing issue for a while. And while it is extremely annoying, the end users only see it once a day generally.