Solved

insert if not exists, hibernate, tapestry

Posted on 2013-05-21
7
796 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Should localization be done inside spring controller 5 32
tomcat administrtor 12 58
Unhandled exception type Exception 18 42
Chrome and Firefox Java 5 50
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

809 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