I don't think you need to worry about LAST_INSERT_ID so long as you
have the same connection that inserted it. With IBatis this is not a
problem.
"The last ID that was generated is maintained in the server on a
per-connection basis. This means the value the function returns to a
given client is the most recent AUTO_INCREMENT value generated by that
client. The value cannot be affected by other clients, even if they
generate AUTO_INCREMENT values of their own. This behavior ensures
that you can retrieve your own ID without concern for the activity of
other clients, and without the need for locks or transactions."
Full text from manual is below.
Brandon
>From the MySQL manual:
LAST_INSERT_ID() , LAST_INSERT_ID(expr)
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column.
mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. This means the value the function returns to a
given client is the most recent AUTO_INCREMENT value generated by that
client. The value cannot be affected by other clients, even if they
generate AUTO_INCREMENT values of their own. This behavior ensures
that you can retrieve your own ID without concern for the activity of
other clients, and without the need for locks or transactions.
The value of LAST_INSERT_ID() is not changed if you update the
AUTO_INCREMENT column of a row with a non-magic value (that is, a
value that is not NULL and not 0).
If you insert many rows at the same time with an insert statement,
LAST_INSERT_ID() returns the value for the first inserted row. The
reason for this is to make it possible to easily reproduce the same
INSERT statement against some other server.
On Sun, 27 Mar 2005 08:55:15 -0500, John Fereira <[EMAIL PROTECTED]> wrote:
> At 01:58 PM 3/23/2005 +0000, James, Steven wrote:
> >hi steven
> >
> >it was actually $$ not ##.
> >this works for me it will also get the last insert key change to suit db
> >ie @@identity sqlserver
> >
> ><insert id="test" parameterClass="string">
> > $value$
> > <selectKey resultClass="int">
> > select LAST_INSERT_ID()
> > </selectKey>
> > </insert>
>
> You might want to be careful about using the LAST_INSERT_ID(). That
> function returns the last auto_incremented value maintained by the
> server. In a multiuser environment their is the possibility of a race
> condition. The function is also not portable.
>
> As an alternative I create a Sequence table containing "name, id"
> fields. When an insert to a table is performed, first query the sequence
> table to get the last id, use it for the value for the primary key (don't
> use an auto-increment value), then increment the value after the insert
> succeeds.
> John Fereira
> [EMAIL PROTECTED]
> Ithaca, NY
>
>
