select for update clause

[khussain]

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.

[chrisrlong]

No. A transaction is only completed by a COMMIT or ROLLBACK.

- Chris

[khussain]

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 ?

[irehman]

After updating any row, Lock stays on until you commit or rollback.

[chrisrlong]

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

[khussain]

It's noe CLEAR - Thanks for the reponses.

[jgmagnus]

you DA MAN Chris!

- Magnus