List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 4 2000 11:35pm
Subject:CONCURRENT_INSERT?
View as plain text  
Hi!

This is a little overdue, but still worth an answer...

>>>>> "Dylan" == Dylan Neild <dneild@stripped> writes:

Dylan> Hi There,

Dylan> Of course, one of the new features for MySQL 3.23.7 is the ability to INSERT
Dylan> and SELECT on the same table at the same time. I understand this is
Dylan> accomplished by having the SELECT thread determine the date/time that it
Dylan> starts, and only reading the records that were less then or equal to this
Dylan> time.

Dylan> Is this correct?

Not really;  As we don't have any extra timestamp information in the
tables the above isn't possible to do.

It works instead as follows (this is done in an atomic context without
giving any other threads any possibility to interfere)

- Get a write-insert-lock
- Check if the table has any deleted blocks;  If this is true,
  upgrade the lock to a write_lock and continue without concurrent inserts.
- Copy the number of rows too a local variable.
- When you insert new rows, don't update the global table row count, only
  the local copy.
- For each separate one index, acquire a separate WRITE LOCK on this.
  during the index update.
- Later when the lock is released, copy the number of rows to the
  global table row counter.


- Threads that require a read lock will copy the current number of
  rows to a local variable.  When scanning the table they will stop
  when they have reached the old number of rows.  Any key read will
  ignore any row that is after the current 'end of file.
- While reading indexes, we get a READ_LOCK on the index for the
  time it takes to find one/next key.


Dylan> Second question.. how much testing has this code had internally at TcX?
Dylan> Would you consider this code buggy, or not so buggy? :) (There's an open
Dylan> ended question if I ever heard one).

We haven't got any bug reports for this code and it has been out almost 1
month;  This means that it's tested pretty good already :)

We did of many tests for 4 days on this code until we got
everything right.  The changes needed for this wasn't actually that
big, but it was tricky at some parts

Dylan> Any testing on FreeBSD?

No, but this code should be totally OS independent, so it should work
on FreeBSD.

Dylan> I must say, reliability and speed are very nice on FreeBSD 3.3-STABLE with
Dylan> 3.23.7, so keep up the good work, and let's keep those stability enhancing
Dylan> updates coming. :)

Thanks.

Regards,
Monty
Thread
CONCURRENT_INSERT?Dylan Neild13 Dec
  • Re: CONCURRENT_INSERT?sinisa13 Dec
  • Re: CONCURRENT_INSERT?Tonu Samuel14 Dec
  • CONCURRENT_INSERT?Michael Widenius5 Jan
    • Re: CONCURRENT_INSERT?Tim Bunce5 Jan
      • Re: CONCURRENT_INSERT?Michael Widenius5 Jan
        • Re: CONCURRENT_INSERT?Tim Bunce5 Jan
        • RE: CONCURRENT_INSERT?Steven Roussey6 Jan
          • RE: CONCURRENT_INSERT?sinisa6 Jan
          • RE: CONCURRENT_INSERT?Michael Widenius6 Jan
            • Re: CONCURRENT_INSERT?elble6 Jan
              • Re: CONCURRENT_INSERT?Michael Widenius7 Jan
RE: CONCURRENT_INSERT?Andy6 Jan