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@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
>
> ---------------------------------------------------------------------
> 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@stripped>
> To unsubscribe, e-mail <java-unsubscribe-time=ice.com@stripped>
>
