I am fighting with lock timeout problem on mysql right now. The front-end code is in Ruby on Rails, which updates some tables. In mysql log, I see things like the following
Transaction 1 ... process no 3353, OS thread id 1094527296 inserting
INSERT INTO ... (caused by code in Ruby on Rails)
record lock for index 'PRIMARY' table 'A', lock mode S locks rec but not gap..
Transaction 2 ... process no 3353, OS thread id 1097165120
record lock for index 'PRIMARY' table 'A', lock mode X locks rec but not gap..
Transaction 1 timeout because the shared lock for the record can not be granted due to transaction 2. However, there two transactions seem to be caused by a single process. My questions are:
- how do I find out what caused transaction 2? It seems it is just updating the index of table A. Is index update in mysql done in a different thread than say jobs for processing update/insert/delete record?
- If my guessing above is correct, how can I fix the problem?
Thank you very much.