Skip navigation links

MySQL Forums :: InnoDB :: Minimize locking on UPDATE with multiple WHERE columns


Advanced Search

Minimize locking on UPDATE with multiple WHERE columns
Posted by: Noha Elarief ()
Date: March 17, 2013 09:07AM

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!

Options: ReplyQuote


Subject Views Written By Posted
Minimize locking on UPDATE with multiple WHERE columns 724 Noha Elarief 03/17/2013 09:07AM
Re: Minimize locking on UPDATE with multiple WHERE columns 416 Rick James 03/21/2013 09:28AM
Re: Minimize locking on UPDATE with multiple WHERE columns 437 Noha Elarief 03/21/2013 02:34PM
Re: Minimize locking on UPDATE with multiple WHERE columns 400 Rick James 03/23/2013 12:23AM
Re: Minimize locking on UPDATE with multiple WHERE columns 412 Noha Elarief 03/25/2013 08:13AM
Re: Minimize locking on UPDATE with multiple WHERE columns 438 Rick James 03/28/2013 12:04AM
Re: Minimize locking on UPDATE with multiple WHERE columns 434 Noha Taha 04/05/2013 01:38PM
Re: Minimize locking on UPDATE with multiple WHERE columns 347 Rick James 04/05/2013 07:05PM
Re: Minimize locking on UPDATE with multiple WHERE columns 358 Noha Taha 04/05/2013 07:32PM
Re: Minimize locking on UPDATE with multiple WHERE columns 382 Rick James 04/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.