MySQL Forums
Forum List  »  InnoDB

Re: Minimize locking on UPDATE with multiple WHERE columns
Posted by: Noha Elarief
Date: March 25, 2013 08:13AM

Hi Rick, thanks again for following up!
I tried adding the index you suggested and I got very strange results:
Here is the query again:
UPDATE <some_table> SET row1=<some value>
WHERE row2 > <some value> AND row1 IS NULL (I had the order flipped in the original question)
ORDER BY row2
LIMIT 100
originally I had two indices, on row1 and row2, the result of show innodb engine status was showing that the transaction is holding ~300k (!) locks on row1_index (I would assume it should hold locks on row2_index according to the order in the WHERE clause).
I then added a compound index on row2 and row1 (in this order). I didn't see an difference in the timeouts and the transaction were still holding locks on row1_index
Then I deleted row1_index, the number of transactions timing out were reduced by a factor of 10 BUT they started holding locks on about 700k on the primary key index!
My question:
1. Are there times where innodb just uses the wrong index? Can I force it to use the right one by adding "use index" to the update query?
2. I cannot understand how the transactions are taking less time after I added row2_row1_index and deleted row1_index although it was holding more locks? Does it use different indexes for locking and for executing the query itself?

Options: ReplyQuote




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.