Hello.
The logic of your application is clear and should work (though I haven't
been digging deeply inside the code). Check that the table type is
InnoDB. 4.0.1 version is rather old and could have lots of bugs, I
recommend you to upgrade to the latest release. Another reason, is that
your algorithm generates overlapped intervals of transerid values, but
this is not a MySQL issue. Enable the general log and check the
sequences of queries produced by your container.
Patrick Duda wrote:
> Hi,
>
> I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems
> trying to figure out why I am not getting the results I am expecting.
>
> I have a table that is used for generating primary keys. It only has
> one item, an int that is incremented each time a key is needed. This is
> not my code or my design so using something like auto_incrament is not
> an option.
>
> The code runs under a container and our desire is to have several
> different containers running at the same time, all accessing the same
> database. Each container is independent so the controls need to be on
> the database side.
>
> The solution also needs to be portable to other databases so I am trying
> to stay with standard JDBC or SQL options.
>
> The code for generating a new key is this:
>
> try {
> c = DatabaseSetup.getDBConnection();
>
> c.setAutoCommit(false);
>
> statement =
> c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> rs = statement.executeQuery("select transfer_id from transferid for
> update");
>
> if (!rs.next()) {
> nextTransferId = nextTransferId + 1;
> StringBuffer query = new StringBuffer();
> query.append("insert into transferid(transfer_id) values
> (");
> query.append(nextTransferId);
> query.append(")");
> tempStatement = c.createStatement();
> // Now Update the old value with new value
> tempStatement.executeUpdate(query.toString());
> } else {
> rs.previous();
> while( rs != null && rs.next() ) {
> nextTransferId = rs.getInt(1);
> // Get the transfer Id and increment it instead of
> using
> // Db Specific sequence
> nextTransferId = nextTransferId + 1;
> // Now Update the old value with new value
> tempStatement = c.createStatement();
> tempStatement.executeUpdate("update transferid set "+
> "transfer_id= "+ nextTransferId);
> }
> }
> } catch (SQLException e) {
>
> if( c != null )
> {
> try
> {
> c.rollback();
> c.setAutoCommit(true);
> }
> catch( SQLException ex )
> {
> }
> }
> throw new DBException(i18n.getMessage("dbInsertErr"),
> e);
> } finally {
> try {
> c.commit();
> c.setAutoCommit(true);
> if (statement != null) {
> statement.close();
> }
> if (tempStatement != null) {
> tempStatement.close();
> }
> if (rs != null) {
> rs.close();
> }
> if (c != null) {
> DatabaseSetup.returnDBConnection(c);
> }
> } catch (SQLException sql) {
> logger.warn(i18n.getMessage("dbStatementErr"), sql);
> }
> }
> return nextTransferId;
> }
>
> I thought, that if I turned off autocommit I would enter a transaction.
> Then, by using the "select...for update", that I would take and hole a
> lock on the table. That no other transaction would be able to read the
> table until I released the lock. However, this is not what I am seeing
> when I run some tests. I start up a number of containers and then fire
> off a bunch of jobs to each. Each of these jobs will hit the above
> code. The problem is that every so often I see the following error
> message.
>
> Error inserting records into database [Caused by: Duplicate entry '131'
> for key 1]
>
> What am I doing wrong?
>
> How am I suppose to be doing this via JDBC? I know it should work...
>
> Thanks
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
