Re: MyISAM tables and locking
Posted by:
R Menon
Date: April 08, 2006 07:40AM
Ingo Strüwing wrote:
> Hi,
>
> if you never do deletes on the table, your inserts
> will be done in parallel to all other work on the
> table. The new rows wll be appended at the end of
> the file.
>
> The other threads will however take table locks so
> that they execute their SQL statements
> sequentially. (Assuming you use MyISAM tables as
> you ask in this forum.)
>
> You could use LOCK TABLES/UNLOCK TABLES if you
> need to make a sequence of SQL statements atomic.
>
> Regards
>
Thanx a lot Ingro!
I reproduce the earlier steps I proposed below:
"
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."
One question is that if a table is being inserted into, does another thread get locked even during the select? It does seem like the case from the documentation.
I guess for deleting records - we can do it at a time when there is no activity in the database. In any case as you say, we can delete records by locking/unlocking the table. But in such cases, if I do delete records, what effect does it have on my selects or updates in other steps?
An important thing I forgot to mention is that the step 2 of "Processing these records" is being done outside MySQL and can be done in parallel. Thus, it still makes sense to use multiple threads. Thus, I can change my steps to the following.
1. Assigning a chunk to a thread - procedure get_records_to_be_processed, say
a. Lock the table.
b. Update the status of the first 10 records with status 'A' (to be processed) using limit clause to 'T' ('T' means marked for processing by a thread.)
c. Return these 10 records to the client
d. unlock the table.
2. Process these records (processing is outside MySQL).
3. If success in step 2., invoke release_records_to_be_processed
3a. Lock the table.
3b. Update status of 10 records by their PK to 'P'
4. If failure in step 2., invoke release_records_to_be_processed
3a. Lock the table.
3b. update status of 10 records back to 'A' and indicate failure.
Could you please
a. comment on the above - does it make sense?
b. comment on any complications that may arise if periodic deletes that create "holes" in the table are done.
c. any other threads.
Thank you for your help!
M
Subject
Views
Written By
Posted
2773
April 07, 2006 06:21PM
1924
April 08, 2006 03:53AM
Re: MyISAM tables and locking
2026
April 08, 2006 07:40AM
2110
April 10, 2006 03:30AM
2186
April 10, 2006 09:30AM
1641
April 11, 2006 01:15AM
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.