Re: retrieving auto-generated keys on bulk insert

Hi,

I believe this entire discussion highlights a long standing design standard.
That is that you should not use database-auto-generated IDs in your 
applications.

Instead you should build a "key service" that provides keys to all of your 
enterprise
applications. There are many reasons why using autogenerated keys is simply
a bad design choice, as well as many more reasons why a key service is a
superior choice, and a google search on the topic will yield many good
discussions on the topic.

tim.

> 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@xxxxxxxxxxx>
> > To: "Dane Foster" <dfoster@xxxxxxxxxxxx>
> > Cc: <java@xxxxxxxxxxxxxxx>
> > 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@xxxxxxxxxxx>
> > > To: "Christopher Taylor" <cstaylor@xxxxxxxxxx>
> > > Cc: <java@xxxxxxxxxxxxxxx>
> > > 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@xxxxxxxxxxxx>
> > > > To: "Paul DuBois" <paul@xxxxxxxxx>
> > > > Cc: <java@xxxxxxxxxxxxxxx>
> > > > 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@xxxxxxxxx>
> > > > > To: "Dane Foster" <dfoster@xxxxxxxxxxxx>; <java@xxxxxxxxxxxxxxx>
> > > > > 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@xxxxxxxxxxxxxxx>
> > > > > To unsubscribe, e-mail
> > > > <java-unsubscribe-dfoster=equitytg.com@xxxxxxxxxxxxxxx>
> > > > >
> > > > >
> > > > >
> > > > > ---------------------------------------------------------------------
> > > > > 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@xxxxxxxxxxxxxxx>
> > > > > To unsubscribe, e-mail
> > > > <java-unsubscribe-cstaylor=nanshu.com@xxxxxxxxxxxxxxx>
> > > > >
> > > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > 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@xxxxxxxxxxxxxxx>
> > > > To unsubscribe, e-mail
> > <java-unsubscribe-jmathis=predict.com@xxxxxxxxxxxxxxx>
> > >
> > > --
> > > Jeff Mathis, Ph.D. 505-995-1434
> > > The Prediction Company jmathis@xxxxxxxxxxx
> > > 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@xxxxxxxxxxxxxxx>
> > > To unsubscribe, e-mail 
> > > <java-unsubscribe-dfoster=equitytg.com@xxxxxxxxxxxxxxx>
> > 
> > --
> > Jeff Mathis, Ph.D. 505-995-1434
> > The Prediction Company jmathis@xxxxxxxxxxx
> > 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@xxxxxxxxxxx
> 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-thread4871@xxxxxxxxxxxxxxx>
> To unsubscribe, e-mail <java-unsubscribe-time=ice.com@xxxxxxxxxxxxxxx>
> 


---------------------------------------------------------------------
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-thread4875@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <java-unsubscribe-gcdmj-java=m.gmane.org@xxxxxxxxxxxxxxx>