Solved

insert if not exists, hibernate, tapestry

Posted on 2013-05-21
7
810 Views
Last Modified: 2013-05-27
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
0
Comment
Question by:5rProgres
  • 4
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 39186302
Are you able to add a "Unique Constraint" to your MySQL database table so that you don't have the duplicate results?
0
 

Author Comment

by:5rProgres
ID: 39186510
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
 
LVL 35

Assisted Solution

by:mccarl
mccarl earned 500 total points
ID: 39186524
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:5rProgres
ID: 39188813
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
 
LVL 35

Assisted Solution

by:mccarl
mccarl earned 500 total points
ID: 39189647
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
 

Author Closing Comment

by:5rProgres
ID: 39199784
Thank you for pointing me to right direction.
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39199995
Not a problem, glad to help!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question