List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:February 18 2001 12:07am
Subject:Re: "DELETE FROM mergetable" clears merge definition
View as plain text  
Hi!

>>>>> "Fred" == Fred van Engen <fred.van.engen@stripped> writes:

Fred> Hi Monty,
Fred> To elaborate on this:

Fred> On Sat, Feb 17, 2001 at 04:41:17PM +0100, Fred van Engen wrote:
>> On Sat, Feb 17, 2001 at 12:28:48PM +0200, Michael Widenius wrote:
>> > 
>> > When manipulating the MERGE table we lock all the tables underneath;
>> > This means that DELETE, UPDATE should work ok execpt when using DELETE
>> > with a where.
>> > 
>> > Note that because of the above, INSERT 'should' work on an underlaying
>> > tables.  We will check out why this doesn't work!
>> > 
>> 
>> This is all great news.
>> 
>> In the mean time I browsed throught the sources and did some more

Fred> I'm not into the intricacies of MySQL locking, but with concurrent
Fred> inserts you seem to lock per index while updating the indexes and
Fred> *afterwards* append the new record to the table.

That's correct.

Fred> If I understand this correctly, then myrg_rlast may find the new
Fred> record's file position from an index using mi_rlast (locking the
Fred> index of course) and do a mi_rrnd, all before the record is written
Fred> by mi_write. The mi_rrnd will fail in this situation, causing
Fred> opt_sum_query to return an 'Impossible query'. I sometimes saw this
Fred> as a comment in an 'explain select ...'.

No, this should not be the case.  All MyISAM functions will silently
skip all rows that has been inserted since one got the locks for the
MERGE table.  For example, if mi_rlast() would find a row that is
located on a position that is larger than the maximum file position at
the time of the lock, it will automaticly do mi_rprev() until it finds
a row that satisfies this condition.

This is basicly how concurrent_insert works and allows one thread to
do insert while many threads are doing selects;  All the other threads
will just ignore all newly inserted rows.

Fred> Then again, I may be completely off track here off course. I've
Fred> only looked into the MySQL code since a few days ago.

The code that does this is myisam/mi_rprev.c:

    while (info->lastpos > info->state->data_file_length)
    {
      /* Skip rows that are inserted by other threads since we got a lock */
      if  ((error=_mi_search_next(info,share->keyinfo+inx,info->lastkey,
                                  info->lastkey_length,
                                  SEARCH_SMALLER,
                                  share->state.key_root[inx])))
        break;
    }                                                                           
Regards,
Monty
Thread
Re: "DELETE FROM mergetable" clears merge definitionMichael Widenius18 Feb
  • Re: "DELETE FROM mergetable" clears merge definitionFred van Engen18 Feb
    • Re: "DELETE FROM mergetable" clears merge definitionMichael Widenius18 Feb
      • Re: "DELETE FROM mergetable" clears merge definitionFred van Engen18 Feb
        • Re: "DELETE FROM mergetable" clears merge definitionMichael Widenius21 Feb