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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
