Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

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.

share|improve this answer
    
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. –  Noha Mar 17 '13 at 22:05
    
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. –  RolandoMySQLDBA Mar 17 '13 at 22:13
    
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 Mar 17 '13 at 22:17
    
I posted a similar question here –  Noha Mar 17 '13 at 22:19
    
How many servers are accessing the DB server? –  RolandoMySQLDBA Mar 17 '13 at 22:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.