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
1771
March 17, 2013 09:07AM
1064
March 21, 2013 09:28AM
989
March 21, 2013 02:34PM
926
March 23, 2013 12:23AM
944
March 25, 2013 08:13AM
974
March 28, 2013 12:04AM
1004
April 05, 2013 01:38PM
918
April 05, 2013 07:05PM
968
April 05, 2013 07:32PM
987
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.