Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Question Unanswered: Table locking and transaction isolation level

    Hi,
    I am currently writing a procedure which will first update a row in a table and run a "select" statement against the table. The "select" statement will extract the row and thereafter display some values. The value will be unique per transaction

    If I have multiple transactions executing the procedure and I want each individual transaction to display a new unique value, should I look at setting the transaction to serializable in the procedure and maybe consider table row locking?

    Regards,

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    if you want to get new row values after UPDATE statement, use RETURNING clause of the UPDATE statement itself. There is no need for SELECT, and so no place for issues you mentioned.

    It is described in SQL Language Reference (UPDATE statement) and PL/SQL Language Reference (its use in procedure), which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.
    Here are the relevant chapters for 11gR2 with examples:
    UPDATE statement http://docs.oracle.com/cd/E11882_01/...8.htm#i2067715
    RETURNING clause in PL/SQL http://docs.oracle.com/cd/E11882_01/...e.htm#CJAGFGDE

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It sounds like you are trying to implement your own sequence logic. If that is the case, then don't do this. Use a sequence to generate unique values. Everything that you can possibly write is either not scalable or not correct (in a multi-user environment).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Jan 2013
    Posts
    5
    @Flyboy,
    Cheers for the response. I forgot about using RETURNING and will look at implementing that.

    @Shammat,
    We have looked at using Sequences however we use various lookup tables for an application. I have seen a few articles about Sequences missing a value and that is a risk which we can't afford to take.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by bravomedic View Post
    We have looked at using Sequences however we use various lookup tables for an application. I have seen a few articles about Sequences missing a value and that is a risk which we can't afford to take.
    There is absolutely nothing wrong with "missing values" or gaps in the values of a generated primary key (aka "surrogate keys").

    If you rely on gapless primary key values then you have a serious design issue.

    A PK value has no meaning and thus it is completely irrelevant if there are gaps in the values.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •