Re: does oracle use block level locking with RAC?

Hmmm... I have not really worked with RAC since it was called "OPS", so I may be mistaken here, and will be perfectly happy to be corrected if I am.

It seems to me, though, that we are talking about very different *kinds* of locks here, and in so doing we may be giving some people the wrong idea.

The "row-level" locks begin discussed are *transaction* locks -- the locks are held for the duration of an entire transaction, until it either COMMITs or does a ROLLBACK. Transactions can last indefinitely, and these locks can therefore be held indefinitely. More important, a single transaction may require multiple row lows -- these two features together make row-level transaction locks susceptible to deadlocks.

Unless I am mistaken, though, the "block-level" locks used by RAC to maintain cache coherency are really more of a MUTEX operation. They are meant to serialize (write) access to a given block of data, but they are short-lived and -- more important -- independent of one another. These locks are held (with lots of simplification, I am sure) for only as long as it takes to complete a (logical) IO, and then are released automatically. So long as a given session (thread) cannot *hold* one gc buffer lock (indefinitely) while being blocked by another (I cannot imagine how this * could* be) then these locks are *not* susceptible to deadlocks.

Cross-instance buffer locking can certainly have a strong influence on performance, but it should not lead to transaction deadlocks. These locks should lead to deadlocks only very rarely, and only (?) in the case of a bug. Right?

On Fri, May 9, 2008 at 1:04 PM, Baumgartel, Paul < paul.baumgartel_at_credit-suisse.com> wrote:

> With RAC, Oracle has to maintain "cache coherency". Because there's more
> than one instance, and therefore more than one SGA, a block can reside in
> more than one SGA, and the usual locking mechanisms of a single instance
> aren't sufficient. If instance A is making changes to a block, and instance
> B wants to make changes, instance B has to wait, and then have the block
> transferred to its SGA over the cluster interconnect before it can make
> changes. These waits will usually show up as "gc buffer busy" waits, "gc"
> meaning "global cache."
>
> Oracle makes a big deal of "Cache Fusion", which transfers blocks over the
> interconnect, but in reality if you have a lot of inter-instance contention
> for blocks, performance will usually be very poor.
>
>
> *Paul Baumgartel*
> *CREDIT SUISSE*
> Information Technology
> Prime Services Databases Americas
> One Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel_at_credit-suisse.com
> www.credit-suisse.com
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ric Van Dyke
> *Sent:* Friday, May 09, 2008 2:52 PM
> *To:* ricks12345_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* RE: does oracle use block level locking with RAC?
>
> There are locks and then there are locks.
>
>
>
> Your right Oracle locks ROWs not blocks. However, when in RAC only one
> instance "owns" the block at a given time. I forget all the techno garble
> to describe the mechanism right now but when another instance wants to
> change a block it requests to be the owner of that block if it isn't at the
> time the change is being made. So it sure seems like a lock because you
> can't access the block until you own it. But it's not a lock in the sense
> that most of us think of a lock. Is that confusing enough? J
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
> *Hotsos Symposium 2009 dedicated to performance and nothing but
> performance*
>
> *March 8 – 12, 2009 in Dallas, Texas*
>
> *Be there.*
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Rick Ricky
> *Sent:* Friday, May 09, 2008 2:39 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* does oracle use block level locking with RAC?
>
>
>
> I saw on a board somewhere that someone is having deadlock issues due to
> block level locking with Oracle RAC? I thought Oracle always did row level
> locking on everything? Could this be a mis-perception with something else?
>
> ==============================================================================
> Please access the attached hyperlink for an important electronic communications disclaimer:
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
> ==============================================================================
>
>

-- 
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs

--
http://www.freelists.org/webpage/oracle-l