MySQL Forums
Forum List  »  InnoDB

Re: Minimize locking on UPDATE with multiple WHERE columns
Posted by: Rick James
Date: March 21, 2013 09:28AM

> I have multiple servers committing the query simultaneously

Why? It is probably not any faster; it is probably slower than if a single client did all the work.

> Optimally the servers should update mutually exclusive rows so locking shouldn't happen at all.

Since there is no communication between the queries, there is no way for them to coordinate like that.

> Is there a way I can skip any locked row in the update?

No.

> WHERE row1 IS NULL AND row2 > <some value> ORDER BY row2
> can I add an index for the two columns together

This should be optimal (in this order!):
INDEX(row1, row2)

> do I also need to remove the indexes that I have (for each column separately)?

With the above index, this is redundant: INDEX(row1). This is different, and may be useful for some other query.

For a discussion and example:
http://mysql.rjweb.org/doc.php/index1

Do you have autocommit=1? Are you using BEGIN...COMMIT?

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.