wood burning stoves 2.0*
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 Spring in Action this week in the Spring 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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

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 ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: primary key generation and concurrency