Record "Blocking" ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Record "Blocking" ?

  1. #1
    Join Date
    Jan 2003
    Posts
    33

    Record "Blocking" ?

    Hello,

    I am looking for a kind of Record Blocking,
    where a query block a record in the way that a following query cannot see (select) this record.

    the "select for update" is therefore not usefull then it locks for updates but allows to read (select).

    To give an idea, a approach could be to have a column (status) where there is a flag (beingUsed) and in the selects you query with "where status not like 'beingUsed'".

    As you probably know this is not "thread safe". If the select happends right when the flag is about to be set by another thread you can select the record in the actual thread and the flag is set right after it by the other thread.

    So the idea is wrong but it explain a bit more the problem.

    Any ideas?

    Im using 9i and programming in Java.

    Thank you

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hello,
    "select for update" IS useful, IMHO.
    If a session locks a record with "select for update", another session using "select for update nowait" on the same record, gets the ORA-00054 error.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you talking about the problem of updating a record here, or were you wanting to use the status of that record itself as the locking mechanism for another process?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2003
    Posts
    33
    ok
    the status column would be only be used for the thread-problem...

    thanks for the "for update" option
    I also was thinking in using it...

    The question is now

    if I use "select ... for update nowait"

    but the web application uses only one connection-configuration
    (one userName, one password) for all threads.
    Is the DB stopping a second "select ... for update nowait"
    even if it is being made using the same connection-configuration (but from a different thread).
    Or is the DB 'thinking': ok is the same user, I allow it...???

    Thanks
    Ter
    Last edited by ter055; 06-25-2003 at 09:58 AM.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Or is the DB 'thinking': ok is the same user, I allow it...???
    no, it wouldn't.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2003
    Posts
    33
    ok

    good to know,
    so I can use it...

    You know why it is so?

    Thanks, regards
    Ter

  7. #7
    Join Date
    Jan 2003
    Posts
    33
    ok

    its working with
    "select ... for update nowait"

    my problem now is that the lock to the record stays there even after the resulset is being closed!!

    is there a configuration issue to check???

    Thanks, regards,
    Tero

Posting Permissions

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



Click Here to Expand Forum to Full Width