Re: "serializable" isolation level not really serializable?

On Fri, 06 Jul 2001, pak.wing_at_capgemini.co.uk wrote:

> Is the scenario above possible with Oracle

No. Upon reread, you will still see the same values as before. Upon trying to update the data, the second transaction will fail with non-serializable error.

> and if so is the only way around it to use select for update?

I believe so. If you want to make sure the row is yours, I believe this is the only sure-fire way. Remember once a process has a lock, any subsequent select for updates will wait unless they do a nowait.

> If so, aren't "select for updates" against the philosophy of
> optimistic locking

Yes they are, but that doesn't mean select for updates are bad. If I have it correct, serializable follows the optimistic locking philosophy, (ie, lets assume that we won't clash with anyone, but if we do, error out and try again)

You could do select for update with nowait and then have a loop that keeps trying until the resource becomes available.

> and hence not scalable and to be avoided?

I don't understand how optimistic vs nonoptimistic locking strategies make anything more scalable than the other.

-- 
Galen
I don't want to be the rock.  Yeah, okay, what do you want to be?
I want to be the piece of glass.