This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes primary key generation and concurrency Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "primary key generation and concurrency" Watch "primary key generation and concurrency" New topic
Author

primary key generation and concurrency

Ram Vir
Greenhorn

Joined: Jul 23, 2003
Posts: 5
Hi,

I am not sure if i am posting this question in the right place,but any help is appreciated.
What is the best way to generate a primary key for a system that has high concurrent database hits.If i use locking mechanism then it leads to a deadlock.Is there any work around for this.

Thanks
RV
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30138
    
150

Ram,
I'm going to move this to JDBC as it isn't really Oracle specific.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30138
    
150

I would suggest getting a batch of keys at a time and then give them out using Java.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
go for timestamp like, YYYYMMDDHHMISS. You can go till millis if there is a high possibility of inserting more than one record in a second.

for more i would like to refer you to this thread
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I believe that the key generation for Oracle provides a batch of keys to each Connection, so you shouldn't need to worry. Not sure I like the milliseconds solution, providing your own implemenation of a GUID provider (Globally unique id - search the web!) would be a simple solution on the application end.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
are you people talking about sequences as batch of keys??

if it is sequences then i read in oracle dba certification guide for 9i that it is not recommended to use sequences. you can use sequences if you just want something unique for your key and not going to manipulate the records based on that. because of some key points,

- max limit
- min limit
- cycle | nocycle
- it is a seperate object inside DB
- we can use same sequence for multiple tables

and we need to tune it after sometime in a regular fashion or better say time to time.
[ October 07, 2004: Message edited by: adeel ansari ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: primary key generation and concurrency
 
Similar Threads
Hibernate Mapping One to One relationship with composite id
rs.updateRow()
hashcode() & equals() method in Entity Bean
Composite primary key
Table without Primary key