Minimize locking on UPDATE with multiple WHERE columns
I have a problematic query that's causing a lock timeout:
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2
LIMIT 100
I have two issues here:
1. I have multiple servers committing the query simultaneously, they lock each other out and I get lock timeout or even deadlock sometimes. Optimally the servers should update mutually exclusive rows so locking shouldn't happen at all. Is there a way I can skip any locked row in the update?
2. If I can't avoid locking, and I already have an index for row1 and another one for row2, will Innodb lock all rows that satisfy any condition in the WHERE clause or only the ones that satisfy both conditions? If the answer is the former, can I add an index for the two columns together or do I also need to remove the indexes that I have (for each column separately)?
Thank you!
Subject
Views
Written By
Posted
Minimize locking on UPDATE with multiple WHERE columns
1932
March 17, 2013 09:07AM
1165
March 21, 2013 09:28AM
1063
March 21, 2013 02:34PM
1016
March 23, 2013 12:23AM
1016
March 25, 2013 08:13AM
1057
March 28, 2013 12:04AM
1081
April 05, 2013 01:38PM
992
April 05, 2013 07:05PM
1046
April 05, 2013 07:32PM
1087
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.