MySQL Forums
Forum List  »  InnoDB

Re: "Lock wait timeout exceeded" time out on insert.
Posted by: Nic Whale
Date: December 13, 2011 05:38AM

Hi Rick,

Now recorded an example of this error occurring. The issue repeats cause I try to re-do the command several times.

Firstly, in the few cases I have had a look at there is an incredibly low level of actions happening in the system in most cases after the error and in some cases leading up. While normally there can be hundreds of records per second, in these cases there is very little in the whole log (i.e. no filtering what so ever).

Case 1: Here you will see no actions between 9:11:57 and 09:12:05, and then no actions for 13 seconds afterwards.


2011-12-13 09:11:57 ssra 275465 INSERT INTO activity( >> The previous unrelated command.
2011-12-13 09:11:57 ssra 275459 INSERT INTO activity( >> The previous unrelated command.
2011-12-13 09:12:05 ssra 275458 INSERT INTO activity( >> The previous unrelated command.
2011-12-13 09:12:05 ssra 275465 INSERT INTO activity( >> The previous unrelated command.
2011-12-13 09:12:05 ssra 275460 INSERT INTO attachment( >> This is what failed.
2011-12-13 09:12:18 ssra 275459 SET autocommit=0 >> This is the next unrelated command.
2011-12-13 09:12:18 ssra 275459 SELECT lastupdated from operative where id =65277 LOCK IN SHARE MODE

Case 2: Here there is no action for 6 seconds afterwards.

2011-12-13 09:12:56 ssra 275465 INSERT INTO activity( >> The previous unrelated command.
2011-12-13 09:12:56 ssra 275460 INSERT INTO attachment( >> This is what failed.
2011-12-13 09:13:02 ssra 275459 SET autocommit=0 >> This is the next unrelated command.
2011-12-13 09:13:02 ssra 275459 SELECT lastupdated from


When you say "I am assuming the Thread Id does represent one connection?" . I have filtered all of the commands by thread ID from just prior to the first error:

2011-12-13 09:10:20 ssra 275460 SELECT id, lastupdated ,title, status, type, >> The previous unrelated command.
2011-12-13 09:10:22 ssra 275460 SELECT id,operativeid,emailtype, status, >> The previous unrelated command.
2011-12-13 09:10:23 ssra 275460 SET autocommit=0
2011-12-13 09:10:23 ssra 275460 INSERT INTO attachment( >> This is what failed 1st time.
2011-12-13 09:11:14 ssra 275460 INSERT INTO attachment( >> This is what failed 2nd time.
2011-12-13 09:12:05 ssra 275460 INSERT INTO attachment( >> This is what failed 3rd time.
2011-12-13 09:12:56 ssra 275460 INSERT INTO attachment( >> This is what failed 4th time.
2011-12-13 09:13:48 ssra 275460 rollback

Let me know if you want more data. I was unsure exactly what you wanted to see, and obviously wanted to filter and analyse as much as possible before providing it to you.

Thanks in advance,

Nick

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "Lock wait timeout exceeded" time out on insert.
2616
December 13, 2011 05:38AM


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.