1

Against my will, I am told to use the following method to generate a new pk value.

INSERT  
    INTO
        SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP, SERVER_NAME, OBSTACLE_YN, CHECK_TIME)
    VALUES((SELECT MAX(SM_SEQ)+1 FROM SERVER_MONITERING), #cpuStat.combinedUsed#, #memStat.total#, #memStat.used#, #diskStat.size#, #diskStat.used#, #serverIP#, 'KW', 'N', TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'))

I told him it still looks like there could be duplicate SM_SEQ values.

However, I don't know for sure whether this approach will eliminate duplication or not.

  1. Will the approach above eliminate the chance of duplicate values ?
  2. Is this a good practice ?
  3. Will a sequence.nextval eliminate the chance of duplicate values ?
  4. What is the best practice ?

Edit

Sorry, I forgot to mention.
There are multiple devs and their own code that inserts into this table and they all use select max()+1. I am a "guest" dev at this company. So I have little influence on their decision. If I switch to using a sequence only by myself, other devs will still select max()+1 and there will be collision between the two approaches.
Probably, I would have to tell all of them why even nesting a select max()+1 won't solve their problem, if it truly would not.

Improve this question
2
  • is there a reason you can't use the actual sequence to generate the SM_SEQ value?
    – Mr.Brownstone
    Commented Dec 7, 2015 at 2:11
  • 1
    I was told by my superior to use the approach above. I would have to tell him why a sequence is better than his approach. So I asked this question and specifically, question #3. Also, technically, I'm not allowed to modify their database. I only asserted my feeling of impending doom but I don't have the knowledge to prove.
    – Ascendant
    Commented Dec 7, 2015 at 2:21

1 Answer 1

Reset to default
2

Assuming that you allow multiple users in your application, max()+1 will guarantee that you will get duplicate values. Therefore, it is a bad practice. It's also less efficient but that is a lesser concern. If you have two sessions, session 1 gets the max() + 1, and session 2 tries to get the max() + 1 before session 1 commits (or rolls back), session 2 will get the same value that was given to session 1.

Using a sequence will eliminate the possibility of duplicates. Use a sequence.

Improve this answer
4
  • Using a sequence will be indeed my preference. However, in my specific case, that could cause some issue with the code along with bothering all the other devs to change their code. Do you have any knowledge regarding my question #1 ?
    – Ascendant
    Commented Dec 7, 2015 at 4:48
  • @Ascendant I answered #1 already. Max()+1 guarantees duplicates in multiuser systems.
    – Justin Cave
    Commented Dec 7, 2015 at 4:50
  • Sorry to bother you but I wanted to make it extra clear. So nesting a select max() within the insert statement does not eliminate the chance of duplicate values ? I ask you this because my superior specifically told me to do that because he said this(nesting select max() within insert) will eliminate duplicate values or give less chance of multiple sessions mixed over because of little time between select max() and insert.
    – Ascendant
    Commented Dec 7, 2015 at 5:07
  • @Ascendant - Nesting has nothing to do with it. As I said, max()+1 does not guarantee uniqueness. If you have multiple users, you will get duplicates. Do the experiment that I outlined in my answer-- open two session, do an insert in session 1, don't commit, go to session 2, do the same insert. If you're using max()+1, session 2 will get the same value that session 1 got.
    – Justin Cave
    Commented Dec 7, 2015 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.