Patrick,
>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.
With a transaction-capable table, eg InnoDB, otherwise neither setting
autocommit off nor adding FOR UPDATE has any effect.
PB
-----
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
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.5/256 - Release Date: 2/10/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]