MyISAM tables and locking

[R Menon]

Hi guys
I am new to MySQL. Currently, I am working on an application that does the following concurrently using MySQL (5.0.19 ):

1. In thread 1 (single thread), we insert records (one at a time) into a MYISAM table - the inserts happen very fast but sequentially.
2. In a set of concurrent threads, we need to process the unprocessed records (identified by a status column that gets changed once the processing is complete) by doing the following (status values are 'A' for active, 'P' for processed):

select a "chunk" of records with status = 'A' - the chunk size is configurable - typically 100.
process these records.
if processing is successful, update the status to 'P' to indicate that.

There are multiple threads that need to process non-overlapping "chunks" of records.

Following is my proposed way of implementing this.

1. Assign a chunk to a thread by updating the status column for the set of records (e.g. for chunk size = 10, the first 10 values with status 'A') will be picked (by updating them to a value of 'T', say).
2. Process these records.
3. If success in step 2., update status of 10 records by their PK to 'P'.
4. If failure in step 2., update status of 10 records back to 'A' and indicate failure.

I am not familiar with the concurrency model of MySQL that much and would appreciate your inputs on what you think of the above approach. In particular, are there any concurrency or performance issues I can expect and if so any suggestions to remedy them? Also, if I need to upgrade to a higher (stable) version to resolve some issues, that is not a problem.

Thanx a lot!
Menon



Edited 2 time(s). Last edit at 04/07/2006 06:23PM by R Menon.