Re: retrieving auto-generated keys on bulk insert

[Dane Foster]

<Jeff wrote>
Allright. I'll buy that.

One more caveat though -- how much control do you realistically have
here? Will there be other applications hitting the database when you
are, or is it really only you? If there are others, will you cause them
to wait too long before they can hit the database?
</Jeff wrote>

The correct answers to the questions you are asking in your 'caveat' are
specific to your database and the programs using the database, but I can give
some generalities based upon what I know (or think I know ;)

As far as control is concerned, MySQL guarantees deadlock free exclusive
(depending on lock type) locks, so you don't have to worry about some process
outside your Java code getting in the way.  As for waiting, it depends on how
long after you have locked the table(s) before you unlock the table.  Just to
give you an idea, I've used this algorithm to add 50,000 rows to a table with 23
columns and 3 indexes, it took approximately 12.64 seconds to complete, meaning
the table was locked for 12.64 seconds. Imagine how long single inserts would
take.

Unless you are inserting millions of rows of binary data that causes the MySQL
packet size to approach the max packet size, I wouldn't worry about it.  There
are tons of ways to tune the MySQL server and the network connecting you to the
server to get the performance that you want.


Dane Foster
Equity Technology Group, Inc.
http://www.equitytg.com
954.360.9800
----- Original Message -----
From: "Jeff Mathis" <jmathis@stripped>
To: "Dane Foster" <dfoster@stripped>
Cc: <java@stripped>
Sent: Wednesday, January 08, 2003 13:57
Subject: Re: retrieving auto-generated keys on bulk insert


Allright. I'll buy that.

One more caveat though -- how much control do you realistically have
here? Will there be other applications hitting the database when you
are, or is it really only you? If there are others, will you cause them
to wait too long before they can hit the database?
Dane Foster wrote:
>
> <Jeff wrote>
> This looks like it would work, but I would be hesitant to increment my
> own counter rather than getting it from the database. What if someone
> else comes in and commits rows while you are doing the same? then your
> counter *may* be off.
> </Jeff wrote>
>
> I thought about that, that is why we obtain a read lock on the table to
prevent
> anyone from writing to it while we do our thing.  But you made me thing about
> something that I didn't think about before.  What happens if some other code
is
> doing the exact same thing we are doing at the same time?  Simple, obtain a
> write lock along w/ the read lock.  This guarantees that no reading or writing
> can take place until we are done with our update, and since the table can't be
> modified by anyone but us it seems pointless to ask the server for the keys
when
> we can easily figure out what they are going to be.  The other benefit of
> calculating the keys ourselves is it saves us a network trip.


>
> Dane Foster
> Equity Technology Group, Inc.
> http://www.equitytg.com
> 954.360.9800
> ----- Original Message -----
> From: "Jeff Mathis" <jmathis@stripped>
> To: "Dane Foster" <dfoster@stripped>
> Cc: <java@stripped>
> Sent: Wednesday, January 08, 2003 13:20
> Subject: Re: retrieving auto-generated keys on bulk insert
>
> This looks like it would work, but I would be hesitant to increment my
> own counter rather than getting it from the database. What if someone
> else comes in and commits rows while you are doing the same? then your
> counter *may* be off.
>
> j
> Dane Foster wrote:
> >
> > So far the best/fastest solution I've come up w/ thus far is this algorithm.
> >
> > Lets suppose we have a table named AUTO_TBL with a auto-generated column
> called
> > pk.  The code would look something like this:
> >
> > Statement st = conn.createStatement();
> > long lastKey;
> > try
> > {
> >     st.executeUpdate( "LOCK AUTO_TBL READ" );
> >     ResultSet rs = st.executeQuery( "SELECT pk FROM AUTO_TBL ORDER BY pk
> > DESC" );
> >     rs.next();
> >     lastKey = rs.getLong( 1 );
> >     st.executeUpdate( bulk_insert_statement );
> > }
> > catch( SQLException ex )
> > {
> >    //do something w/ ex
> > }
> > finally
> > {
> >     try
> >     {
> >         st.executeUpdate( "UNLOCK AUTO_TBL" );
> >     }
> >     catch( SQLException sqlx ){}//safe to ignore
> > }
> >
> > // No exception was thrown so we assume that our bulk insert succeeded
> > long[] keys = new long[ number_of_inserts ];
> > for( int i = 0; i < number_of_inserts; i++ )
> >     keys[ i ] = ++lastKey;
> >
> > Dane Foster
> > Equity Technology Group, Inc.
> > http://www.equitytg.com
> > 954.360.9800
> > ----- Original Message -----
> > From: "Jeff Mathis" <jmathis@stripped>
> > To: "Christopher Taylor" <cstaylor@stripped>
> > Cc: <java@stripped>
> > Sent: Wednesday, January 08, 2003 10:57
> > Subject: Re: retrieving auto-generated keys on bulk insert
> >
> > The code that we have has a low-level save() method that all database
> > objects use which issues a select statement after a new row insert to
> > get the auto-generated key (id in our case). Doing this extra select
> > statement after an insert really does not add overhead -- I timed it
> > with and without and saw no reproducible difference. I hadn't thought
> > about using an updatable ResultSet, mainly because I'm still in the dark
> > ages (I'll look into it though).
> >
> > By far, the biggest performance boost we see is to make sure auto-commit
> > is off and then issue comit statements only after a few thousand inserts
> > or so.
> >
> > jeff
> >
> > Christopher Taylor wrote:
> > >
> > > Not to get into an English parsing war here, but there's also the word
> > > "any", which means some (including the singular).
> > >
> > > Unless Mark can work some magic with the MySQL protocol, there's no way
> > > (AFAIK) that this can be done... unless he parses the statement and issues
> > > INSERT calls one at a time, fetching the LAST_INSERT_ID() on each
operation.
> > > Not very efficient, but it might just work...
> > >
> > > -Chris
> > >
> > > ----- Original Message -----
> > > From: "Dane Foster" <dfoster@stripped>
> > > To: "Paul DuBois" <paul@stripped>
> > > Cc: <java@stripped>
> > > Sent: Wednesday, January 08, 2003 4:20 PM
> > > Subject: Re: retrieving auto-generated keys on bulk insert
> > >
> > > > Paul DuBois wrote:
> > > >
> > > > "It certainly does.  The behavior you describe below is how INSERT is
> > > > documented in the manual to work with respect to LAST_INSERT_ID()
> > > > and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
> > > > *first* auto-generated ID value.
> > > >
> > > > This isn't a JDBD issue."
> > > >
> > > > I would like to point out that yes it is a JDBC issue because (the
> > > following is
> > > > copied from the JDBC javadoc):
> > > > <copy>
> > > > getGeneratedKeys
> > > > public ResultSet getGeneratedKeys()throws SQLExceptionRetrieves any
> > > > auto-generated keys created as a result of executing this Statement
> > > object. If
> > > > this Statement object did not generate any keys, an empty ResultSet
object
> > > is
> > > > returned.
> > > >
> > > >
> > > >     Returns:
> > > >     a ResultSet object containing the auto-generated key(s) generated
> by
> > > the
> > > > execution of this Statement object
> > > >     Throws:
> > > >     SQLException - if a database access error occurs
> > > >     Since:
> > > >     1.4
> > > > </copy>
> > > > Notice that the word key is plural, meaning if a statement causes the
> > > creating
> > > > of more than one key then the ResultSet object should return all
> keys,
not
> > > just
> > > > the first key.  So from the JDBC perspective the MySQL JDBC driver's
> > > > implementation of the getGeneratedKeys method is not consistent with
> the
> > > JDBC
> > > > documentation.
> > > >
> > > > Dane Foster
> > > > Equity Technology Group, Inc.
> > > > http://www.equitytg.com
> > > > 954.360.9800
> > > > ----- Original Message -----
> > > > From: "Paul DuBois" <paul@stripped>
> > > > To: "Dane Foster" <dfoster@stripped>;
> <java@stripped>
> > > > Sent: Tuesday, January 07, 2003 23:31
> > > > Subject: Re: retrieving auto-generated keys on bulk insert
> > > >
> > > >
> > > > At 16:41 -0500 1/7/03, Dane Foster wrote:
> > > > >Hi Mark.
> > > > >
> > > > >The Connector/J JDBC driver does not correctly handle returning
> > > auto-generated
> > > > >keys on multi row inserts.
> > > >
> > > > It certainly does.  The behavior you describe below is how INSERT is
> > > > documented in the manual to work with respect to LAST_INSERT_ID()
> > > > and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
> > > > *first* auto-generated ID value.
> > > >
> > > > This isn't a JDBD issue.
> > > >
> > > > >  I'm not sure if this is a bug because the multi row
> > > > >INSERT syntax in MySQL is MySQL specific therefore the JDBC rules
> > > > >may not apply.
> > > > >On the other hand, the ANSI/ISO SQL standard supports multi row
inserts,
> > > and
> > > > >since the JDBC API is geared towards the ANSI/ISO SQL standard
> maybe
the
> > > JDBC
> > > > >rules do apply and it is indeed a bug.
> > > > >
> > > > >Let my back up for a moment and clarify in case I lost anyone. 
> Lets
> > > > >pretend we
> > > > >have a database table named 'MY_TABLE' that looks something like
> this:
> > > > >
> > > > >CREATE TABLE MY_TABLE(
> > > > >pk    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
> > > > >misc  VARCHAR (255),
> > > > >PRIMARY KEY (pk));
> > > > >
> > > > >Using Connector/J I have code similar to:
> > > > ><code>
> > > > >String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test')";
> > > > >Statement st = connection.createStatement();
> > > > >
> > > > >st.executeUpdate( sql );
> > > > >ResultSet rs = st.getGeneratedKeys();
> > > > >
> > > > >if( rs.next() )
> > > > >     System.out.println( "The auto-generated key is " +
> rs.getLong(
> > > 1 ) );
> > > > >else
> > > > >     throw new IllegalStateException( "No auto-generated key
> found." );
> > > > ></code>
> > > > >
> > > > >For a newly created 'MY_TABLE' table the code snippet above would
> > > produce:
> > > > >The auto-generated key is 1
> > > > >
> > > > >The next snippet of code is where the problem lies.
> > > > ><code>
> > > > >String sql = "INSERT INTO MY_TABLE (misc) VALUES
> ('Test1'),('Test2')";
> > > > >Statement st = connection.createStatement();
> > > > >
> > > > >st.executeUpdate( sql );
> > > > >ResultSet rs = st.getGeneratedKeys();
> > > > >
> > > > >while( rs.next() )
> > > > >     System.out.println( "The auto-generated key is " +
> rs.getLong(
> > > 1 ) );
> > > > ></code>
> > > > >
> > > > >For a newly created 'MY_TABLE' table the code snippet would
> produce:
> > > > >The auto-generated key is 1
> > > > >
> > > > >If you missed it; There is no second line of output!
> > > > >
> > > > >Mark, please get back to me about this when you can.  It's kind
> of
> > > > >important for
> > > > >a project that I'm working on.  I would make recommendations on a
> > > > >fix but I know
> > > > >absolutely zilch about the MySQL network protocol.  Thanx.
> > > > >
> > > > >
> > > > >Dane Foster
> > > > >Equity Technology Group, Inc.
> > > > >http://www.equitytg.com
> > > > >954.360.9800
> > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > Before posting, please check:
> > > >    http://www.mysql.com/doc/         (the manual)
> > > >    http://lists.mysql.com/           (the list archive)
> > > >
> > > > To request this thread, e-mail
> <java-thread4855@stripped>
> > > > To unsubscribe, e-mail
> > > <java-unsubscribe-dfoster=equitytg.com@stripped>
> > > >
> > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > Before posting, please check:
> > > >    http://www.mysql.com/doc/         (the manual)
> > > >    http://lists.mysql.com/           (the list archive)
> > > >
> > > > To request this thread, e-mail
> <java-thread4856@stripped>
> > > > To unsubscribe, e-mail
> > > <java-unsubscribe-cstaylor=nanshu.com@stripped>
> > > >
> > > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > >    http://www.mysql.com/doc/         (the manual)
> > >    http://lists.mysql.com/           (the list archive)
> > >
> > > To request this thread, e-mail <java-thread4857@stripped>
> > > To unsubscribe, e-mail
> <java-unsubscribe-jmathis=predict.com@stripped>
> >
> > --
> > Jeff Mathis, Ph.D. 505-995-1434
> > The Prediction Company jmathis@stripped
> > 525 Camino de los Marquez, Ste 6 http://www.predict.com
> > Santa Fe, NM 87505
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/doc/         (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <java-thread4859@stripped>
> > To unsubscribe, e-mail
<java-unsubscribe-dfoster=equitytg.com@stripped>
>
> --
> Jeff Mathis, Ph.D. 505-995-1434
> The Prediction Company jmathis@stripped
> 525 Camino de los Marquez, Ste 6 http://www.predict.com
> Santa Fe, NM 87505

--
Jeff Mathis, Ph.D. 505-995-1434
The Prediction Company jmathis@stripped
525 Camino de los Marquez, Ste 6 http://www.predict.com
Santa Fe, NM 87505