Re: does oracle use block level locking with RAC?

I agree with Mark.

In fact, block-level locking is not a RAC-only feature. The "cache buffers chain" latch is used to lock groups of blocks in non-RAC Oracle databases. In RAC there is also an additional "global enqueue" layer which is used to coordinate reads/writes to the block across the entire cluster but these can't lead to deadlocks.

In the very unlikely chance that they did, it would be a serious bug. But I've never heard of it happening.

On Fri, May 9, 2008 at 9:01 PM, Mark Brinsmead <pythianbrinsmead_at_gmail.com> wrote:

> 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

-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

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