?
Solved

insert if not exists, hibernate, tapestry

Posted on 2013-05-21
7
Medium Priority
?
841 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

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…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

762 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