Minimize locking on UPDATE with multiple WHERE columns

Question

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:

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 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)?

Answer 1

You will need to lock all the rows yourself before each UPDATE.

See the MySQL Documentation on SELECT ... LOCK FOR UPDATE. This performs an exclusive lock on all the rows you pass through. Then, you can follow up with the needed UPDATE against the table.

In your particular case, you would do this:

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100 

INDEXING

• You should index the table fully to support all possible ways you will be querying the data. Notwithstanding, you must alternate between SELECT ... FOR UPDATE and UPDATE.
• Since you have both row1 and row2 in the WHERE clause, you should have an index with both columns in it.
• There is one warning: If these columns are indexed, expect some slowness because the column is being updated and the BTREE index pages are being updated per row. You should also expect rapid growth of the insert buffer section of ibdata1 (See InnoDB Map)

I have many posts on the subject of SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARED MODE.

• Case Study I went through with @RedBlueThing
  • Reasons for occasionally slow queries?
  • Trouble deciphering a deadlock in an innodb status log
  • Will these two queries result in a deadlock if executed in sequence?
• Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
• Oct 18, 2011 : Preventing mysql deadlocks in your php application that uses SELECT... LOCK IN SHARE MODE
• Jan 02, 2012 : LOCK IN SHARE MODE
• Nov 19, 2012 : How long can Deadlock info hold in innodb status page?
• Dec 13, 2012 : MySQL InnoDB locks primary key on delete even in READ COMMITTED
• Feb 03, 2013 : MySQL InnoDB locking on combined UPDATE-JOIN statements
• Mar 12, 2013 : How I prevent deadlock occurrence in my application?

UPDATE 2013年03月17日 19:21 EDT

Since you have 9 WebServers hitting the DB Server, try this

On WebServer1 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100; 

On WebServer2 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100; 

On WebServer3 run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100; 

All the way to WebServer9, run

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100; 

You will have to place some PHP header file that unique identifies which machine runs which version of the query.

• Noha: Thank you for your reply. However I don't see how that would solve my problem since all servers executing the query you provided will put a lock on the same rows, ending up with a timeout again.
• RolandoMySQLDBA: You need to play "traffic cop" with locks. If you do not, you could face possible overwriting of data because you get either timeouts or MVCC taking turns over overwriting.
• Noha: I'll take a step back because probably my question wasn't clear enough. Optimally no locking should occur at all since the idea behind this query is each server is trying to assign one record to itself. row1 being null means it hasn't been assigned to any server yet so pick it up and assign it to yourself. If all servers look at the same 100 rows at the same time this would be very innefficient that's why my question was how to avoid that locking (maybe some condition in the where clause) or keep it to the minimum (probably by picking random rows which satisfy the condition row1=null)
• Noha: I posted a similar question here
• RolandoMySQLDBA: How many servers are accessing the DB server?