Re: Locks rows

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:b0j4uc$kjb$1 $830fa7b3_at_news.demon.co.uk:

>
> If you select for update, Oracle has to take
> an ITL entry in the block header, and copy the
> index of that ITL into the lock byte for the row
> so that other processes know that the row is
> locked and can discover who is locking it.
>
> Since this is a change to the block, it has
> to generate undo and redo. In high concurrency,
> high throughput, such pessimistic locking should
> be avoided unless it is really needed, as it could
> be the difference between scalability or failure.

HJR, Niall, Jonathan (and my offline conversation with The Oracle Employee :),

Thank you for the info. I agree 100% with your conclusion Jonathan, that taking such a pessimistic approach would lead one into the toilet. Typically, there's enough randomness within user applications that taking an optimistic approach works.

Without a lock manager (in-memory that is) the penalty is that a "select for update" has a write penalty associated with it. As The Oracle Employee pointed out, you better be darn sure to need the "select for update" if you're going to use it. In other words, use it judiciously.

This makes me wonder why the Oracle lock manager would want to flush the ITL entry. Seems that since we have the buckets in memory, that we can emulate an in-memory lock manager by never flushing out the db block when ITL is affected. This would give me the best of both worlds: in-memory lock manager with infinte, pre-configured of course, locks.

Hope I'm not starting senseless threads! <g>

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com