Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

insert if not exists, hibernate, tapestry

Posted on 2013-05-21
7
Medium Priority
?
870 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 36

Accepted Solution

by:
mccarl earned 2000 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 36

Assisted Solution

by:mccarl
mccarl earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 36

Assisted Solution

by:mccarl
mccarl earned 2000 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 36

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

610 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