Results 1 to 11 of 11

Thread: Lock for select

  1. #1
    Join Date
    Oct 2008
    Posts
    7

    Unanswered: Lock for select

    Hello,
    i have a little trouble with oracle locks.

    I have an application running in 2 servers with a shared Oracle db. In this application i (must!) use hibernate so i can not use hibernate to manage locks (due to double server).

    I need to manage a column as a sequence-like number with this couple of query:

    SELECT maxSeq FROM table
    UPDATE table SET maxSeq=maxSeq+1

    But before SELECT i need to lock the table because other transactions must wait the maxSeq update before their select.

    I know it will be better to use a oracle sequence, but i have to process (between select and update) the maxSeq by my application, this is not just an id increment.

    I was looking for LOCK TABLE... but this can not lock also SELECT queries.

    Can you help me, thanks.
    Bye

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    There are no read locks in Oracle.

    There is a simple answer to your problem and a serious one:

    First the simple answer:

    Use

    Code:
    SELECT maxSeq FROM table FOR UPDATE
    This command is placing a write lock on the selected row(s). If this is done, every other process that selects the same row(s) for update will wait (default) until the first transaction finishes.

    Or (if specified

    Code:
    SELECT maxSeq FROM table FOR UPDATE NOWAIT
    will raise ORA-00054).


    However (and that's the serious part), you wrote:

    application running in 2 servers with a shared Oracle db
    This looks like a RAC.

    The problem with that is, that you are creating a bottleneck on this record that will serialize all transactions.
    So this will NOT scale on a single instance system. And on of the ground rules for RAC is, that every scalability and performance issue on a single instance will multiply on a RAC (resource migration).

    For your type of problem, you should use your sort of algorithm in ONLY 1 case:

    If you need contiguous numbers without omissions between them (i.e. creating bank account numbers)

    So - if you cannot avoid doing it this way- you should set up a service for this application that only uses one node of the RAC for doing this.

    In every other case, use sequences instead (and on a RAC, cache at least a a couple of hundreds of sequences)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I still don't understand why an oracle sequence will not work.
    currval, nexval seems it would work for you.
    Can you explain further?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Duck,

    that's what I meant by

    If you need contiguous numbers without omissions between them (i.e. creating bank account numbers)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I was asking the OP. Sorry for the confusion
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't always write SQL for Oracle and sometimes get confused by what can be done by one DBMS and not in the other so bear with me. Oracle does not allow for a select from update? There would still be a bottle neck, but it would be more of a large mouth rather than a small one.
    Dave

  7. #7
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    O.K.,

    this may get kind of long and boring ...

    What you seem to try is, to generate unique, strictly monotonically increasing numbers (i.e. for a uniqe key).

    (correct me, if I'm wrong)
    Your approach to accomplish this is, to store the "next maximal number" in a record in a database table.

    Something like:
    Code:
     Name                                      Null?    Type
     ----------------------------------------- -------- -------------
     APPLICATION                               NOT NULL VARCHAR2(64)
     MAXNUM                                    NOT NULL NUMBER
    Then a process demanding a new unique number will have to:

    - SELECT this row FOR UPDATE (which establishes a write lock on row level)
    - use maxnum
    - increase maxnum number by 1
    - UPDATE the record to the new value of maxnum
    - COMMIT (!) or ROLLBACK to close the transaction to release the write lock on the record.

    Until the release of the locks, no other process can write to this row.

    So, as you can see immediately, you will be in deep trouble, as soon as more than one process needs such a maxnum to load data in parallel.
    You may have tens of processor cores and IO channels. it would make no difference, because each process would have to queue for this row in this table.

    Even worse ist the fact, that you have to COMMIT after each insert.

    This means, an insert cannot be part of a transaction.

    And a commit is one of the most expensive operations for a database:
    - the redolog information has to be written synchronously (!) on disk
    - the controlfile has to be updated with the new transaction number

    Without going into detail, believe me: all this trouble multiplies on a RAC

    Most RDBMS vendors support so called SEQUENCES.
    These are database objects that deliver numbers like your approach did, but without its disadvantages.
    They are also faster by factors than a table access, because they can be cached.

    And there lies a potential problem:

    Each time, the database instance is shut down, you lose the sequences in the cache.
    Let's say, the maxnr at a certain point is 1000, and (by default) 20 numbers are cached.
    When the instance is shutdown and started again, the next number you will get is 1021

    If you just need unique numbers, that's no problem at all.
    But in some applications you don't want to have this sort of "holes" in the data (banks i.e. like their account numbers this way).

    This requirement is very rare, but if it exists, you have to compromise.

    If you are working on a RAC, you should ensure that this application is not running on several instances in parallel, but only on one instance
    Otherwise you might be in for the surprise, that 3 RAC nodes need up to 20 times as long for a task, than one node did.

    The way you accomplish this, are "SERVICES" (that's what I meant in my first post. Your DBA will know what to do)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  8. #8
    Join Date
    Oct 2008
    Posts
    7
    Thank you all for your replies.

    I can not use sequence because I must check the number in my application, sometimes i have to skip a number (special numbers), and i must reset the number every first day of the year... and so on. Infact this number is not a table primary key (I use a sequence for it) it is just a practice number that must follow some rules.

    Maybe i can solve with the "FOR UPDATE", but because this provide a write lock (not also the read lock) can happend that two transaction read the same value. Am I right?

    Thanks.

  9. #9
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Tobia,

    yes, but you have to ensure that ALL applications that get values from there use SELECT FOR UPDATE and not only SELECT (Oracle doesn't know your intentions, so it does what is is supposed do do: it provides a read consistent image of the original value in an undo segment and selects from there until the update commits. Until the commit, only the updating session sees the new value)
    Last edited by magicwand; 04-29-10 at 21:13.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Tobia View Post
    I can not use sequence because I must check the number in my application, sometimes i have to skip a number (special numbers), and i must reset the number every first day of the year
    Both requirements can be fullfilled with a sequence.

    To ignore certain numbers, simply call nextval. If that is not a "valid" number, simply call nextval again. Resetting the sequence every year can easily be accomplished with a dbms_job that is scheduled to re-create the sequence on January 1st.

    Unless you have not told us all requirements (e.g. that you do need a gap-less sequence) then I don't see a reason to use the non-scaling blocking solution

    Tom Kyte once said: "When rolling your own solution, you can either get it right, or you can get it fast, but not both" - which fits your problem as well (although he was referring to constraints in the database)

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by shammat View Post
    both requirements can be fullfilled with a sequence.

    To ignore certain numbers, simply call nextval. If that is not a "valid" number, simply call nextval again. Resetting the sequence every year can easily be accomplished with a dbms_job that is scheduled to re-create the sequence on january 1st.
    ^ this yo!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Tags for this Thread

Posting Permissions

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