• Post Reply Bookmark Topic Watch Topic
  • New Topic

How do I prevent duplicate keys on insert?  RSS feed

 
verduka fox
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have an application that performs auditing of the user's activity. In one place, the user has the ability to add several items to a list. Each item that is added to the list needs to be audited separately. I am calling a stored procedure which inserts the audit into the database. However, I've run into a problem and I don't know the best way to solve it. The stored procedure inserts some values into the table. Each item has the date/time that it is inserted as a part of the primary key. The problem is that when multiple items are selected, these inserts happen too quickly that the database sees the inserts as happening at the same time and therefore throws a duplicate key error. We are using Sybase 12, and if a date/time occurs within the same three milliseconds as the previous date/time, it assumes that these are the same date/times.
So my question is how do I "stall" the inserts so that they occur every 3 milliseconds instead of every millisecond? I need to prevent the duplicate key errors.
Thanks in advance.
 
Joel McNary
Bartender
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just in general, I strongly disagree with having anything other than an incrementing integer being part of the primary key.
That much being said, (assuming that these inserts are inside a loop in a single thread), you can say:
Thread.sleep(3)
to pause to 3 milliseconds....
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Timestamps are often interesting data, but dangerous keys. Even if you avoid writing the same key twice, another client might be inserting at the same time and collide with you.
Does it have to be a datetime datatype? And a single column key? If either of those is no, you can concatenate some other data or use another column to assure uniqueness. I agree with the last post, I like incremental integers.
The system I work on now has a key vendor component that synchronizes requests for the next sequential key for each table. It gets the current high key from a special table in the database, adds "n", and puts that value back. Then it can vend "n" sequential keys from memory without touching the database.
The only bad news is that every time we shut down there are about "n/2" keys that were reserved but never used. If "n" is too big, you can overflow an integer key much sooner than you might expect.
 
verduka fox
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joel and Stan,
Thank you for your replies. To clarify, we have multiple columns that combine to make the primary key. Therefore, the date time field is not the only key on the table. However, the problem is when all the other columns in the primary key match and the date time is within the 3 millisecond window as the original row.
I am interested in the Thread.sleep(3); suggestion. Do I need a try/catch around this? Are there any caveats when using this method? I have studied threads in classes, but don't have much real-world experience in this area. Your explanations and continued comments are greatly appreciated.
 
Joel McNary
Bartender
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by verduka fox:
Joel and Stan,
I am interested in the Thread.sleep(3); suggestion. Do I need a try/catch around this? Are there any caveats when using this method? I have studied threads in classes, but don't have much real-world experience in this area. Your explanations and continued comments are greatly appreciated.

Of course, the easy way to find this out is to write the code and see if it compiles...the compiler will tell you if you need to catch an exception and which exception to catch...
The short answer, though, is yes, you do need to catch a "java.lang.InterruptedException" As far as cavaets, I suppose that it's theoretically possible for another thread to wake it up during those 3 milliseconds (if you had multiple threads and coded it that way..)
(I assume that you have little say in the DB design, else I think you would be having more questions on why not to use multiple columns as primary keys. I have a plethora of horror stories regarding clients who did that )
 
Ahetuki Neti
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't you use Sequence, one of the Objects available on the databases. The syntax to create a sequence on the database is:
CREATE SEQUENCE seq_my_seq START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER;
Now to get the next sequence, call:
SELECT seq_my_seq.NEXTVAL FROM DUAL;
 
verduka fox
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm still working on this issue. Unfortunately, I cannot dictate the database design. I have to find a way to prevent my code from inserting duplicate keys into the database. I am working on implementing the Thread.sleep(3); suggestion using the following code:

Is this the proper way to implement this? Does Thread.sleep(3) get the currently running thread and make it sleep 3 milliseconds?
I have appreciated your help so far. Your reply to help me complete this is greatly appreciated. Thanks in advance.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!