2

I have a database table where the primary key is defined as a timestamp. The application inserts records to the database based on a certain event that is triggered and the timestamp used will be the one that created this instance. Since this application is running on multiple servers and that event is triggering at the same time in more than one of these servers at the same time, in some cases, the data doesn't get inserted to the database due to the primary key violation.

What could I possible change in order to avoid this situation? The ideas that I have at the moment are,

  1. Waiting a random time before executing the database insertion. 

    Random r = new Random();
long time = r.nextLong() % 1000;
time = time < 0 ? -time : time; // making sure that the waiting time is positive
try {
    Thread.sleep(time);
} catch (InterruptedException e) {
    throw new CustomeException(e);
}
Table1DataAccess.getInstance().insert(new Date(), "some description"); // inserting data into the table which has two fields.

  2. Making the primary key a composite one.

Neither of them seems like good solutions to me, could anyone point me in the right direction?

P.S. The table to which I tried inserting data had the timestamp column in Date type and I guess it only has it's precision down to seconds. When I used the type timestamp instead, it offered a default precision of microseconds and it can offer nanoseconds at maximum.

Rajith Gun Hewage
  • 532
  • 7
  • 25
  • 3
    Just model reality. If more than one event can occur at the same time(stamp), a timestamp alone is not the correct primary key for this data, but e.g. timestamp + increasing number per same timestamp. Well, and just **do not use timestamp as primary key at all**. – Florian Albrecht Oct 12 '16 at 11:57
  • @FlorianAlbrecht yeah, I agree with you and I understand that there's an issue with the design here. But this is an old code base that I am working on at the moment. Anyway, "increasing number per same timestamp" doesn't seem like a solution here because these events occur on different servers. So, how am I supposed to maintain a counter across these servers? – Rajith Gun Hewage Oct 12 '16 at 12:00
  • Which DBMS are you using? Some of them have Sequences which could be used for this. But if you are able to change the primary key to include such counter field - aren't you able to change the whole key to be an autoincrement field, and the timestamp only being an informational column? – Florian Albrecht Oct 12 '16 at 12:04
  • As it is mentioned in one of the answers, I want to make the changes to the database and the code to a minimum and hence I didn't consider using the DBMS provided sequences to be my primary key. Anyway, if there is no other way of getting rid of my issue, I will use this. – Rajith Gun Hewage Oct 12 '16 at 12:12
  • 1
    How much precision does the timestamp column provide? Instead of inserting `new Date()` try changing the query to the Oracle equivalent of `INSERT INTO ... VALUES (current_timestamp, ...)` so the database determines the value instead of the java code. – Andrew S Oct 12 '16 at 13:07
  • This seemed exactly what I wanted; so I tried this on the database with multiple inserts and I get the same issue again. Maybe ```current_timestamp``` value's precision is not enough. – Rajith Gun Hewage Oct 12 '16 at 13:16
  • My bad, it seems the precision issue was with my table column. The timestamp was in ```Date``` and when I change it into ```timestamp``` your suggestion seemed to do the trick. And I guess it would do fine with the existing implementation as well. – Rajith Gun Hewage Oct 12 '16 at 13:54

4 Answers4

5

Do not use timestamp as primary key field.

Use Integer or BigInt for primary key auto increment fields

Kushan
  • 10,657
  • 4
  • 37
  • 41
1

Define the primary key as an autoincremet integer/long field

Neo
  • 1,337
  • 4
  • 21
  • 50
1

Waiting for a random time to insert may not work if you have a large number of inserts. The answer assumes that you want to keep timestamp and make minimal changes in your code and do not want to go to auto increment features available in DB.

I have seen a similar problem long back and we modified the table to include originating server node to address it. So, either you expand the timestamp column and save timestamp_server or you add an extra column , server_node and then work with it. It will depend upon how you fetch the data. Adding server_node column will be less intrusive. However, do you need to update the data as well? Primary keys cannot be updated. So that will need to be considered in design considerations.

Rishi Goel
  • 670
  • 4
  • 10
  • I can say that the assumptions are correct. And if I add this server_node column and include it in the primary key, then I would have to change all my existing records with some value too, I guess (may be a default value). – Rajith Gun Hewage Oct 12 '16 at 12:12
  • Ok, We manually assigned codes for each server node and used them as a part of primary key for inserts. We ignored the keys while doing selects. Rest will depend upon your application logic. – Rishi Goel Oct 12 '16 at 12:15
0

First of all, it is not wise to use the timestamp as a primary key. The primary key should be something that uniquely identifies your database entry. And that is not the case in your application: even in the same server, you could get a conflict.That is one of the reasons why all major database engines provide a mechanism for automatically generating the primary key. Also check this answer for more details.

In the hypothetical case of using the timestamp (maybe because it is too late to change your overall design now), you could prepend a unique identifier of the respective server to your timestamp. Nevertheless, this will not be a unique identifier in the case of same-time writing within the same server. Then, you will need to append another incremental identifier when catching the error of primary key violation. And this will not entirely solve your problem. You could -for instance- have the same incremental identifier within the same server. You see how messy this process can get.

So, the take-home message is that it would save you a lot of time and pain if you allowed the DB engine to assign the primary key for you.

Community
  • 1
  • 1
katamayros
  • 82
  • 1
  • 10