This discussion is archived
10 Replies Latest reply: Dec 7, 2012 6:29 AM by BluShadow RSS

Select for update

suzvino Newbie
Currently Being Moderated
I have table where i am storing max employee number.

table name: emp_number_control
Column name: max_emp_no

In my program for generating next employee number i will pick the max employee number and add 1.
this select statement i am using select for update clause.

If i run this program in two session i dont want to another session to select until this is updated.

I dont want missing sequence. How to achieve this..

Thanks
Vinoth
  • 1. Re: Select for update
    LPS Journeyer
    Currently Being Moderated
    use For UPDATE NOWAIT.....clause
  • 2. Re: Select for update
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Two things,

    1. why not use sequence which are more scalable then self created sequence.

    2. if you are doing you entire process in one procedure (code unit) and you have executed select for update on your table, unless your procedure completes and returns the control to calling environment and commit / rollback is performed the second session even if executes select for update on the same table will wait for completion of work by first session. This is called session blocking.
  • 3. Re: Select for update
    908002 Expert
    Currently Being Moderated
    Then why dont u use sequence instead of max(id) +1..
  • 4. Re: Select for update
    BluShadow Guru Moderator
    Currently Being Moderated
    suzvino wrote:
    I dont want missing sequence. How to achieve this..
    You can never guarantee that in a live system, even if you try and code your own sequences.

    Such a requirement indicates that the use of a sequence number is being used for the wrong reasons.
    Oracle provides sequences to guarantee a unique value, whilst at the same time catering for multi-user environments.
    Coding your own sequence is only guaranteed to prevent your application working truly multi-user.
  • 5. Re: Select for update
    6363 Guru
    Currently Being Moderated
    suzvino wrote:

    I dont want missing sequence. How to achieve this..
    By building an application that does not reliably support more than one user.

    Do you want that?
  • 6. Re: Select for update
    suzvino Newbie
    Currently Being Moderated
    I dont want missing employee number. Sequence will skip some numbers.
  • 7. Re: Select for update
    BluShadow Guru Moderator
    Currently Being Moderated
    suzvino wrote:
    I dont want missing employee number. Sequence will skip some numbers.
    So what are you going to do when you encounter exceptions after the next employee number has been queried? How are you going to ensure that the data is rolled back correctly in such events? How can you truly guarantee that maintaining your own sequence number will prevent any numbers from being skipped (I can tell you now the answer is that you cannot make such a guarantee).
  • 8. Re: Select for update
    6363 Guru
    Currently Being Moderated
    suzvino wrote:

    I dont want missing employee number. Sequence will skip some numbers.
    Please answer this question - {message:id=10734265}

    You can only create a consecutive gap free set of numbers if you only allow one user at a time to enter values. This means your application may work well if it only has one use and will lock users out with increasing frequency the more users you add.

    Do you want that?

    That is the reason storing cross row values, such as a max or a count in each row is not a relational design, because it will only reliably support one user at a time, and using a database for an application that is limited like that is simply throwing money away.

    This is nothing to do with technological features, it is simply the laws of physics, mathematics and logic.
  • 9. Re: Select for update
    kendenny Expert
    Currently Being Moderated
    suzvino wrote:
    I dont want missing employee number. Sequence will skip some numbers.
    So if an employee quits today, you want to keep him in the table forever? Because if you delete him, you'll have that number skipped.
    As others have pointed out, in a multiuser environment, this is going to cause problems.
    If you really don't want to have numbers skipped and you know you will never delete any rows, then the way to do it is to use a sequence for the id, then create a view which has a column defined as row_number() over (order by sequence_column) and use that column as your id that the user sees. If you want your id's to start at some number other than 1, simply add the starting number -1 to that column definition in the view.
    Of course the drawback to this approach is that if you ever delete a row, then every row above that will have its id reduced by 1.
  • 10. Re: Select for update
    BluShadow Guru Moderator
    Currently Being Moderated
    kendenny wrote:
    suzvino wrote:
    I dont want missing employee number. Sequence will skip some numbers.
    So if an employee quits today, you want to keep him in the table forever? Because if you delete him, you'll have that number skipped.
    Perhaps all employees will shift down one number.... and if they make it to number one they become CEO of the company. :D
    Of course the drawback to this approach is that if you ever delete a row, then every row above that will have its id reduced by 1.
    ... heading for that CEO position... ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points