Lock timeout exceed: explaination
Posted by: Benny Pei
Date: July 28, 2017 05:07PM

Hello all,

I have identified a transaction when it reported a Lock timeout exceeded error. I can only gather some information from the slow query log and the query developer ran. (I am unable to see much detail in show engine innodb status since it was overwritten by other deadlock information)

The query our developer ran was an insert statement to a tableA. I found in the same period (3 minutes) there was a couple of long update statements to tableA doing a full table scan. I don't know the order of the transactions. But the insert statement was kicked out with the error message.

e.g.

update tableA set colx=1 where colY=2;
update tableA set colx=1 where colY=2;
insert into tableA values (...)


There is no index on colY so it is a full table scan. The insert statement experienced a lock time error message.

I want to know why the insert statement got kicked out? I can't seem to explain this. There was a primary key in the table and also a couple of foreign keys on tableA.

Could someone help me piece them together and explain what might have caused this? perhaps a workaround to avoid this?

Your input is appreciated.

thank you

benny

Options: ReplyQuote


Subject
Written By
Posted
Lock timeout exceed: explaination
July 28, 2017 05:07PM


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.