select for update clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: select for update clause

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Question

    Here is an excerpt from Oracle Doc re. the select for update clause:

    "locks the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement (not in subqueries)."


    When is the lock removed? By 'end your transaction' do they mean when the select is over ???

    Thanks in advance.
    Share on Google+

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    No. A transaction is only completed by a COMMIT or ROLLBACK.

    - Chris
    Share on Google+

  3. #3
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Thanks Chris,

    To all:

    What is the purpose of the 'select for update clause' ? Just to be able to lock the rows ?

    If so, then do you have to issue a commit after the 'select for update clause' - is that the only way ?
    Share on Google+

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    After updating any row, Lock stays on until you commit or rollback.

    Share on Google+

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    It sounds like you may want to read up on transactions before writing *any* code. Transactions are pretty much the backbone of the database.

    However, to answer your immediate question. SELECT..FOR UPDATE is used to *guarantee* that nobody messes with the record between when you *read* it and when you *update* it.

    SELECT ...
    -- look at a few things
    -- okay, I want to update the record
    UPDATE
    -- wait a minute, how do I know that nobody else did an update while I was looking at the record? I may have just wiped out their changes

    - vs. -

    SELECT ... FOR UPDATE
    -- look at a few things
    -- okay, I want to update the record
    UPDATE
    -- I *know* that nobody else could have updated the record since I read it, because I had an exclusive lock on it the whole time.


    However, using optimistic concurrency is a much better approach in general. SELECT...FOR UPDATE should only be used in very special cases, not in general.

    - Chris
    Share on Google+

  6. #6
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Talking

    It's noe CLEAR - Thanks for the reponses.
    Share on Google+

  7. #7
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Cool


    you DA MAN Chris!

    - Magnus
    Share on Google+

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