Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Primary key violation problem

 
Luca kk
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,
I would like to expose my problem.
I created a web application with JSF technology, which interfaces with SQL Server 2005.
The application is quite simple, provides an initial login and allows the logged users to enter
data in the database, such as a new record in a table .
The problem arises when I make the calculation of the ID, which is the primary key of the new
record to insert: as increasing of users logged so grows the probability that two users simultaneously
attempt to insert a new record with the same ID with the problem of violation of the primary key.
Now as the number of users logged each have a different instance of the application, the problem is
unsolvable, and correct me if I'm wrong, if I continue to leave the method of calculation of the ID
in the application itself, such as Session.
I thave to move the metod to the application server as a service (I use Tomcat)? How?
Is there any tool already developed?
Thanks,
Luca
 
Campbell Ritchie
Sheriff
Pie
Posts: 50217
79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How many instances of the database are there? Can you use locking or transactions to prevent duplicate IDs?

And welcome to JavaRanch
 
Luca kk
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ritchie,
there is one istance of DB:

connDb_Suite = UConnections.getConnection();

I use a stored procedure to store the data and i don't
use lock or transactions.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can have your key generation logic in the database. Does your datebase support sequences? If it does not, its possible to us a HiLo pattern to achieve a similar end.
 
Luca kk
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,
I use JDBC, so without persistence.
I use SQL Server 2005 and I never used the sequences.
How can I use them?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL Server does not support sequences. It does however support identity columns. If you are happy enough to tightly couple your application to SQL Server you can use them. Alternatively, if you want it to remain database agnostic, you can use a pattern like the HiLo pattern.
 
Luca kk
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Unfortunately I don't decide for DB and I have to use
SQL Server.
What are identity columns?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic