Incomplete transaction in Sequence Generator

[karumady]

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..........

[adrian.brock]

This is a MySQL issue. In particular make sure you are using
the table type that supports this.
MySQL didn't even support transactions until recently.

Also this is not a JCA problem.

Regards,
Adrian

[karumady]

hai Adrian,

thanks for ur response

we r using InnoDB,
and 98% of our transactions commit successfully.
Since JBOSS begins the transaction,it should commit also, but here sometimes it shows some in-between commits and rollbacks in the
MySQL query log.
We just want to confirm that the problem is not from the JBOSS side, is it so??

hoping comments