Re: Changing isolation level?

"Hemant Shah" <shah_at_typhoon.xnet.com> wrote in message news:bqlt2j$j25$2_at_flood.xnet.com...
| While stranded on information super highway D Alpern wrote:
| :)SELECT... FOR UPDATE {WAIT | NOWAIT} springs to mind...
| :)
|
| No, you cannot select for update when you just want to read the data, it
will
| lock out everyone else.
|
|

it will lock out anyone that has the same level of interest -- ie, select for update, update, delete, it will not block other readers who do not request the select for update.

if the application needs to be sure that it is seeing the 'correct' value (i.e., does not want to see the record if it has a pending transaction) then you have to consider your timeslice -- in the scenarios being discussed, it does no good to get the committed value and allow another process to update/delete the row while my process still thinks it's working with the value just read.

options:

select -- no guarantee that the record is free of a pending transaction -- if there is a pending transaction, i'm working with a potentially incorrect value
-- if there is no pending transaction, i'm working with a value that was correct only at the time i retrieved it, it could change at any moment select for update -- guarantee that the record is free of any pending transaction, and will remain so until commit or rollback

• mcs