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> 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
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. :)