MySQL Forums
Forum List  »  InnoDB

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "Lock wait timeout exceeded" time out on insert.
1719
January 26, 2012 05:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.