Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

primary key generation and concurrency

 
Ram Vir
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ram,
I'm going to move this to JDBC as it isn't really Oracle specific.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would suggest getting a batch of keys at a time and then give them out using Java.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic