auto-increment and transient rows during transaction

Consider this sequence of operations (on an InnoDB table):

begin a transaction
insert new row in a table with an auto-increment ID field
note the ID of the new row
delete that row
commit the transaction

Can any subsequent insert (that does not explicitly specify an ID) end
up with the same ID as the row inserted in the above transaction?

The situation here is I've got a table and code inserting into it,
letting the row IDs be generated by the auto-increment field. Much of
this is ugly legacy code that I do not wish to touch. For some new
code, there is a situation where I don't have the data that is to be
inserted, but I need to know what the ID will be when I get around to
inserting it. So, I want to make the auto-increment skip a value, and
note that value, so that later I can use that as the ID when I insert
the data.

The obvious idea of just inserting and leaving a dummy row in the table,
and then updating it when I have the data, is not good, because some of
that horrible legacy code might get confused by that incomplete row.


--
--Tim Smith

> Consider this sequence of operations (on an InnoDB table):
>
> begin a transaction
> insert new row in a table with an auto-increment ID field
> note the ID of the new row
> delete that row
> commit the transaction
>
> Can any subsequent insert (that does not explicitly specify an ID) end
> up with the same ID as the row inserted in the above transaction?

I would say that this would not even need a transaction, but if it is in
one, it would just work. Primary key values are never reused (that is
the very nature of a primary key). But if you really, really want to
make sure, just try.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Tim Smith <reply_in_group@mouse-potato.com> wrote:
>
> Consider this sequence of operations (on an InnoDB table):
>
> begin a transaction
> insert new row in a table with an auto-increment ID field
> note the ID of the new row
> delete that row
> commit the transaction
>
> Can any subsequent insert (that does not explicitly specify an ID) end
> up with the same ID as the row inserted in the above transaction?

Normally not. As soon as an AUTO_INC value is generated, it is "burnt"
and will no be re-used. There is one exception: if there are some of
those "generated but never used" values at the end of the table, then
after a restart of the MySQL server those will be re-used. See here for
an explanation:
http://dev.mysql.com/doc/refman/5.0/...-handling.html

> For some new
> code, there is a situation where I don't have the data that is to be
> inserted, but I need to know what the ID will be when I get around to
> inserting it. So, I want to make the auto-increment skip a value, and
> note that value, so that later I can use that as the ID when I insert
> the data.

Ugly. Why don't you just insert a dummy row and UPDATE it later with
the real values? You have to remember the PK of that row anyway.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

In article <47c3cddc$0$14351$e4fe514c@news.xs4all.nl>,
Willem Bogaerts <w.bogaerts@kratz.maardanzonderditstuk.nl> wrote:
> > Consider this sequence of operations (on an InnoDB table):
> >
> > begin a transaction
> > insert new row in a table with an auto-increment ID field
> > note the ID of the new row
> > delete that row
> > commit the transaction
> >
> > Can any subsequent insert (that does not explicitly specify an ID) end
> > up with the same ID as the row inserted in the above transaction?
>
> I would say that this would not even need a transaction, but if it is in
> one, it would just work. Primary key values are never reused (that is

The purpose of using a transaction is so that nothing else will see the
row. If the row were visible to other processes, they might get
confused. (Yes, I know this design is a bit ugly. If I didn't have
legacy code to deal with that I don't want to change, I'd have another
table being used as a sequence generator to generate the IDs, rather
than using an auto_increment, and then if code wants to preallocate an
ID before it is ready to insert into the table, it could just grab an ID
from the sequence generator).

> the very nature of a primary key). But if you really, really want to
> make sure, just try.

Well, experimentally it works, but I want to make sure I'm not relying
in an accident of implementation, which could change any time we apply
an update to MySQL. Kind of like in C programming, where someone writes
a statement like this:

a[i] = i++;

It may do what the programmer expected to do with the particular version
of the particular compiler he tested on, but he's asking for trouble, as
that expression is undefined.

Because the operations are in a transaction, and so nothing else can see
that transient row, and nothing in the transaction uses the row before
it is deleted, my concern is that the database might be allowed to
optimize away the insert and delete.

--
--Tim Smith

In article <pg6d95-1cp.ln1@xl.homelinux.org>,
Axel Schwenke <axel.schwenke@gmx.de> wrote:
> > For some new
> > code, there is a situation where I don't have the data that is to be
> > inserted, but I need to know what the ID will be when I get around to
> > inserting it. So, I want to make the auto-increment skip a value, and
> > note that value, so that later I can use that as the ID when I insert
> > the data.
>
> Ugly. Why don't you just insert a dummy row and UPDATE it later with
> the real values? You have to remember the PK of that row anyway.

Same reason as 90% of the other ugly hacks: stupid legacy code that I
don't want to disturb. :-)

--
--Tim Smith