List:Maria Storage Engine« Previous MessageNext Message »
From:Michael Widenius Date:September 29 2008 10:02pm
Subject:Re: Maria Concurrent inserts
View as plain text  

>>>>> "Guilhem" == Guilhem Bichot <guilhem@stripped> writes:


>> Both of those are not what I expected. I expected a SHOW PROCESSLIST 
>> output where almost all all threads are in INSERT , but they are all 
>> able to move forward.
>> Am I doing something wrong? The inserts all insert a null into a pk 
>> auto_increment column

Guilhem> auto_increment column => no concurrent inserts.


Guilhem> I don't remember why this concurrency limitation is also currently 
Guilhem> applied to row-based replication too. I imagine that, just before 
Guilhem> locking the table (when we decide to take an exclusive lock on the table 
Guilhem> or not), we could do
Guilhem> if (autoinc and this statement is statement-based)
Guilhem>     take exclusive lock;

Guilhem> Monty, do you remember why this isn't possible?

This is becasue of the way auto-increment are currently allocated in
Maria (the same as in MyISAM):

The current algorithm (which will change in Maria 2.0 or 3.0) is:

- Ask engine for the next available auto-increment
- Put it in record
- Write it
- Store as the next available auto-increment number the used number + 1

Because of the above we can't have two threads inserting on the same
table at the same time;  We would use the same auto-increment number.

To fix the above, we would need to change MySQL/Maria to first reserve
auto-increment numbers and in case of duplicate key errors give back
the not used ones if there was no other thread using a higher number
in between.  Some of this work (but not all) is already done on the
MySQL server side. The Maria side would need notable more work (2 days
of work).


Re: Maria Concurrent insertsMichael Widenius30 Sep