Hi

This is a problem with our Sequence Generator

Like always , the working is an update after a select.

But sometimes it is not updating the table and the log shows that transaction commits successfully

Hence getting DUPLICATE KEY ERROR for the next selected value

We r using JBOSS3.0,8 and MySQL 4.0.13

The code comes in an EJB helper class and we r using Stateless Seession Beans


*******************************************************************************************************************************
String query = "SELECT max_number FROM document_series WHERE series='" + series + "' FOR UPDATE";

rs = statement.executeQuery( query );

if( rs.next() ){

maxValue = rs.getLong(1);

query = "UPDATE document_series SET max_number = " + ( maxValue + 1 ) + " WHERE series LIKE '" + series + "'";

statement.execute( query );

}
*****************************************************************************************************************************************

Is the problem related with table locking ?

Most of the times the above helper function is calling from another EJB ,is that a problem?

Expecting comments

Any help is highly appreaciated..........