I have a problematic query that's causing a lock timeout:
UPDATE <some_table> SET col1=<some value> WHERE col1 IS NULL AND col2 > <some value> ORDER BY col2 LIMIT 100
I have two issues here:
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?
If I can't avoid locking, and I already have an index for col1 and another one for col2, 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)?