views:

23

answers:

0

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:

  1. 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?
  2. If my guessing above is correct, how can I fix the problem?

Thank you very much.