Re: Minimize locking on UPDATE with multiple WHERE columns
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?
Subject
Views
Written By
Posted
1763
March 17, 2013 09:07AM
1055
March 21, 2013 09:28AM
984
March 21, 2013 02:34PM
918
March 23, 2013 12:23AM
Re: Minimize locking on UPDATE with multiple WHERE columns
937
March 25, 2013 08:13AM
969
March 28, 2013 12:04AM
1001
April 05, 2013 01:38PM
914
April 05, 2013 07:05PM
960
April 05, 2013 07:32PM
984
April 05, 2013 10:16PM
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.