Transaction/concurrency problem with find & create

[pbriggs]

Hi all,

I'm using JBoss 3.0.4 + Tomcat 4.1.12 and CMP with an Oracle 8 db.

I've been having a lot of problems with duplicate "phantom" rows being created in my database by my session bean.

I have a method called setHits in my session bean that is passed a URL string. What I want to do is to create a new row in the database for that URL if none already exists. If there IS a row for that URL, I want to increment the "hits" column (an Integer) of that row. To check if the URL is already present of not, I have a findByUrl() method in my UrlBean. If this generates an ObjectNotFoundException then I catch it and create a new row/LocalUrl, otherwise I just update the hits. Here's some of the setHits code from my session bean:

public void setHits(String url) {
 LocalUrl urlL;
 int hits;
 Integer sequenceNum;
 Integer urlId;
 boolean created = false;

 try {
 urlL = urlHome.findByUrl(url);
 } catch (ObjectNotFoundException obEx) {
 //System.out.println("Url not found in database - creating new entry");
 sequenceNum = new Integer((int)IdGen.nextVal());
 urlId = sequenceNum;
 this.createUrl(urlId, url);
 created = true;
 } catch (Exception ex) {
 throw new EJBException("Error in setHits: "+ex.getMessage());
 }

 if (!created) {
 System.out.println("MatrixBean - Setting url hits");
 hits = urlL.getHits();
 urlL.setHits(hits + 1);
 urlId = queryL.getUrlId();
 }
}

public void createUrl(Integer urlId, String url) {
 System.out.println("MatrixBean createURL" +" "+urlId+" "+url);
 try {
 LocalUrl urlL = urlHome.create(urlId, url);
 } catch (Exception ex) {
 throw new EJBException(ex.getMessage());
 }
}

This all works fine when only one user is using the web app at a time, but if there are a few concurrent calls to setHits I get duplicate rows occurring in the database (which is disasterous for my application). For example, if setHits is passed the same URL simultaneously by 2 or more users then I get 2 or more rows with the same URL when what I need is just one row with the "hits" column updated correctly.

The transaction attributes for all of my session and entity bean methods are set to "Required" (although I've tried "RequiresNew" too). I've also tried setting different isolation levels with no success. My latest idea is that the ObjectNotFoundException thrown by the findByUrl method is causing the transaction to abort. If this is the case, then how can I execute the findByUrl and create methods as one atomic chunk of work?

I've spent days searching forums and documentation to try and sort this out, so any input would be greatly appreciated!

Cheers,
Pete

[pbriggs]

ooops, that code didn't come out too well! Here it is...

public void setHits(String url) {
LocalUrl urlL;
int hits;
Integer sequenceNum;
Integer urlId;
boolean created = false;

try {
urlL = urlHome.findByUrl(url);
} catch (ObjectNotFoundException obEx) {
//System.out.println("Url not found in database - creating new entry");
sequenceNum = new Integer((int)IdGen.nextVal());
urlId = sequenceNum;
this.createUrl(urlId, url);
created = true;
} catch (Exception ex) {
throw new EJBException("Error in setHits: "+ex.getMessage());
}

if (!created) {
System.out.println("MatrixBean - Setting url hits");
hits = urlL.getHits();
urlL.setHits(hits + 1);
urlId = queryL.getUrlId();
}
}

public void createUrl(Integer urlId, String url) {
System.out.println("MatrixBean createURL" +" "+urlId+" "+url);
try {
LocalUrl urlL = urlHome.create(urlId, url);
} catch (Exception ex) {
throw new EJBException(ex.getMessage());
}
}

[l.g.]

You can create unique index on url column
and catch CreateException or make url column part of PrimaryKey and catch DuplicateKeyException.

[pbriggs]

Thanks for the reply!

Like you suggested I've made the URL column unique and am catching the Exception after I try to create the row, and it seems to have solved the problem :)

Thanks again!
Pete