Re: Locking Question (nowait, UPDATE etc)

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 04 Apr 2003 08:51:42 +0200
Message-ID: <3E8D2B7E.9040104@science-computing.de>

Alex Filonov wrote:
[...]

>>I still don't get the point. But I believe that the reason why you use the
>>select for update is just because you found no other way to start a trans-
>>action which as a result gets you read consistency.
>>

>
>
> Wrong. If you have a cursor loop and want to update rows of the cursor
> tables inside the cursor, you HAVE TO make cursor statement FOR UPDATE,
> and use WHERE CURRENT OF clause in update statements.
> Otherwise you'll get ORA-1555. Of course, you can write pure SQL update
> statements, but not everybody is good at it.

If you open an explicit cursor that does a select on a table but instead of looping through the cursor delete from the table , do a commit and then start looping over the cursor, guess what you get? A read consistent view of the data as it was when opening the cursor. If you're hit by ORA-1555, that's a story of to small rollback segments, too frequent commits where they are not appropriate, or not enough undo retention if you're on 9i with UNDO.

>
>

>>Have you tried to set the isolation level to serializable? (I don't have
>>the semantics at hand, sorry). If I'm not misled by my memory, just setting
>>the transaction level to read only (not applicable in your case) or serializable
>>will implicitly start a transaction even with a select statement (until you
>>commit of course).
>>

>
>
> Wrong again. Read only transaction doesn't allow updates. Serializable
> doesn't start at select. Even though you can trigger it with some fake
> update.
>

I'm well aware that read only doesn't allow updates (I stated that this is not applicable for the OP), but if 'set transaction isolation level serializable' does not gives me immediately a transaction where phantom reads are not possible, then why would the authors of Beginning Oracle Programmming (T.Kyte, Chr. Beck, S.Dillon, H.J. Rogers et.al) state:

'Once you issue this statement, the database will apear frozen in time for you, regardless of the changes taking place. You are completely isolated from the effects of other transactions [...]'

Personably, I'd trust them on explaining things right more than I'd trust the Oracle docs, although of course, even they, at times, can be wrong :-)

Regards,

Holger Received on Fri Apr 04 2003 - 00:51:42 CST