Hello Jan, long time talked to you,
On Thu, Oct 26, 2006 at 12:53:31PM +0300, Jan Lindström wrote:
> Hello,
>
> Consider following SQL-queries:
>
> create table t1(a int not null auto_increment primary key)
> engine=soliddb;
> insert into t1 values (null),(3),(null);
>
> at the moment 5.1.12-bk executes insert as follows:
>
> Breakpoint 2, ha_soliddb::write_row (this=0x97f2b08, buf=0x97f2c38 "�")
> at ha_soliddb.cc:3750
> 3750 su_err_t* errh = NULL;
> (gdb) c
> Continuing.
>
> Breakpoint 1, ha_soliddb::get_auto_increment (this=0x97f2b08, offset=1,
> increment=1,
> nb_desired_values=3, first_value=0x8ffe7140,
> nb_reserved_values=0x8ffe7138)
> at ha_soliddb.cc:10276
> 10276 DBUG_ENTER("ha_soliddb:get_auto_increment");
> (gdb) c
> Continuing.
>
> Breakpoint 2, ha_soliddb::write_row (this=0x97f2b08, buf=0x97f2c38 "�
> \003")
> at ha_soliddb.cc:3750
> 3750 su_err_t* errh = NULL;
> (gdb) c
> Continuing.
>
> Breakpoint 2, ha_soliddb::write_row (this=0x97f2b08, buf=0x97f2c38 "�")
> at ha_soliddb.cc:3750
> 3750 su_err_t* errh = NULL;
> (gdb) c
> Continuing.
>
> Breakpoint 1, ha_soliddb::get_auto_increment (this=0x97f2b08, offset=1,
> increment=1,
> nb_desired_values=3, first_value=0x8ffe7140,
> nb_reserved_values=0x8ffe7138)
> at ha_soliddb.cc:10276
> 10276 DBUG_ENTER("ha_soliddb:get_auto_increment");
>
> Now my question is why MySQL calls ::get_auto_increment function second
> time for a above insert? Handler has already generated exactly desired
> number of auto_increment values for the MySQL server. My implementation
> for ::get_auto_increment is as follows:
The queries are:
create table t1(a int not null auto_increment primary key)
engine=soliddb;
insert into t1 values (null),(3),(null);
the write_row() for the first NULL will ask to reserve 3 values, Solid
will probably reserve those: 1, 2 and 3. 1 is immediately consumed by
the first NULL.
Then there is write_row() of the explicitely specified value
3. handler::update_auto_increment() is called and this part of it is
executed:
if ((nr= table->next_number_field->val_int()) != 0 ||
auto_increment_field_not_null &&
thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
{
/*
Update next_insert_id if we had already generated a value in this
statement (case of INSERT VALUES(null),(3763),(null):
the last NULL needs to insert 3764, not the value of the first NULL plus
1).
*/
adjust_next_insert_id_after_explicit_value(nr);
insert_id_for_cur_row= 0; // didn't generate anything
DBUG_RETURN(0);
}
where nr is 3. So next_insert_id is set to 4 (the value which should
be used for a next NULL in this statement).
Then there is write_row() of the second NULL, update_auto_increment()
is called and this part of it is executed:
if ((nr= next_insert_id) >= auto_inc_interval_for_cur_row.maximum())
{
/* next_insert_id is beyond what is reserved, so we reserve more. */
here next_insert_id is 4, auto_inc_interval_for_cur_row.maximum() is 3
(the maximum value reserved from the engine), so a new reservation
has to be made, hence the second call to get_auto_increment().
It could indeed be that, right after the first call to
get_auto_increment() by our connection con1, a concurrent connection
con2 reserved 3, then a concurrent connection con3 reserved 4,5,6,
then con2 rolled back. Then con1 inserted 3. Then con1 must call the
engine again for the 2nd NULL, it must not try to insert 4 (this value
is reserved by somebody else).
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot <guilhem@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com
