insert if not exists, hibernate, tapestry

I have tapestry based web-service application which will run on (for now two) servers with replicated MySQL database.

I have a problem with following scenario (simplified to basic):

      
interface
@CommitAfter:
public void addUrl(Url url);


implementation:

	public void addUrl(Url url)
	{
		CriteriaBuilder builder = em.getCriteriaBuilder();
		CriteriaQuery<Url> criteria = builder.createQuery(Url.class);
		Root<Url> urlRoot = criteria.from(Url.class);
		List<Predicate> predicates = new ArrayList<Predicate>();
		predicates.add(builder.equal(urlRoot.get(Url_.url), url.getUrl()));
                            criteria.where(builder.and(predicates.toArray(new Predicate[] {})));		
		List<Url> urls = em.createQuery(criteria).getResultList();
		
		if(urls.size()==0)
		{
			Url urlToInsert = new Url();
			urlToInsert.setUrl(url.getUrl());
			em.persist(urlToInsert);
		}
		else 
		{
			Url urlToUpdate = urls.get(0);
			urlToUpdate.setSomeProperty("abc");
			em.persist(urlToUpdate);
		}		
	}
	

Open in new window


It can happen that two different services insert same url at the same time
and check for if url exists in database fails, because first transaction is
not being commited yet. So both requests insert same url into database and I end up by having two results.

Any help appreciated.

5r
5rProgresAsked:
Who is Participating?
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
Are you able to add a "Unique Constraint" to your MySQL database table so that you don't have the duplicate results?
0
 
5rProgresAuthor Commented:
In one of my test I had unique primary key and one of two requests raised constraint exception.

You are aiming that I should catch that exception and restart transaction in that case?
0
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
You are aiming that I should catch that exception and restart transaction in that case?
If by "restart transaction" that will cause the entire addUrl() method above to start again so that the "update" code branch will run instead of the "insert" code branch? Then YES, that is what I was getting at.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
5rProgresAuthor Commented:
Do you have any idea how will this behave in replicated server environment?

Since If I have auto inc fields one server assigns odd values and second one even, that way it can never happen that two servers will create same key...

With this insert on both servers could go trough, but then replication would probably fail.
0
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
Ahhh, I saw the word "replicated" in your original question but it wasn't clear how you were using (or more to the point, it wasn't clear that you were using it in a non-standard way). You should read the MySQL documentation on Replication. In particular, here are some relevant excerpts...

Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 17, MySQL Cluster).
The target uses for replication in MySQL include:

 - Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.

(Note: the above links may not apply to you specific version, but they are there for the general idea)

Notice the emphasis that I applied in the second quote. There is no way to get the functionality that you are trying to get, with your current "replicated" setup. To do what you are trying to do, you really should be looking at MySQL Cluster, otherwise you would need to follow the documentations advice and only insert/update to one of the servers!
0
 
5rProgresAuthor Commented:
Thank you for pointing me to right direction.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Not a problem, glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.