Re: MyISAM tables and locking

[R Menon]

Thank you Ingo, You answered most of my questions. One thing I did not understand is what you meant by "If however
> an insert is running, a select is sent and then
> another insert is sent, then the waiting insert
> will go before the waiting select."

Do you mean that if a select and an insert statement are pending then insert gets higher priority?

In any case, it looks like I should go for the inserts appending at the end and deleting + optimizing the table during low activity times.

Thanx a bunch!!

Menon
Ingo Strüwing wrote:

> Hi,
>
> R Menon wrote:
> > Ingo Strüwing wrote:
> > > Hi,
> ...
> > 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.
>
> No. Not during the select. If you send an insert
> statement while a select statement is running, the
> insert will wait until the selects end. If however
> an insert is running, a select is sent and then
> another insert is sent, then the waiting insert
> will go before the waiting select. But at no time
> two statements work at the same time.
>
> The exception are inserts when there is no deleted
> record in the table. See below.
>
> > 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?
>
> When you delete a record, you create a "hole" in
> the data file. The record remains at its place but
> is marked as deleted. The next insert will reuse
> that record space.
>
> Only if the data file does not contain any holes,
> inserts are done as appends to the data file. In
> this case they can work in parallel to other
> statements on the table. The other statements
> notice the data file size only when they start and
> keep that information during their run. So they
> don't see records that are appended beyond the
> position of the data file that they remeber as the
> data file end. But at every statement start that
> position is taken from the current file size. So
> every statement sees all records that were
> inserted before it started.
>
> Since MySQL 5.0.6 you can force inserts into
> appends with SET concurrent_insert=2. But then
> holes may not be reused and your table may grow
> without limits. You could get rid of the holes
> with OPTIMIZE TABLE. But this locks the table
> during its work.
>
> In prior versions I suggest to do all deletes at
> low traffic times with an OPTIMIZE afterwards.
>
> Normally you don't need to worry about concurrent
> inserts. Only if you have performance problems you
> should consider to force them. And then you may
> want to think about INSERT DELAYED too. If it is
> not a throughput problem, but a response time
> problem (for the inserting threads), the latter
> may be easier as you don't need to worry about
> reorganization (OPTIMIZE).
>
> ...
> > 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.
>
> Yes. This makes sense as you will have an UPDATE
> and an SELECT, which you need to keep together
> with no other thread stepping in between.
>
> > 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.
>
> In this case I see an UPDATE only. This is atomic
> in itself (doing implicit table locking). In this
> case an explicit locking would be unnecessary. But
> if you have a second SQL statement which I don't
> see, then the locking is OK. But please don't
> forget the unlock. ;-)
>
> Regards
>