Re: does oracle use block level locking with RAC?

  • From: "Jeremy Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: pythianbrinsmead@xxxxxxxxx, "Rick Ricky" <ricks12345@xxxxxxxxx>
  • Date: Sat, 10 May 2008 12:35:35 -0500

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@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx
>> www.credit-suisse.com
>>
>>
>>  ------------------------------
>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Ric Van Dyke
>> *Sent:* Friday, May 09, 2008 2:52 PM
>> *To:* ricks12345@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>> *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@xxxxxxxxxxxxx [mailto:
>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Rick Ricky
>> *Sent:* Friday, May 09, 2008 2:39 PM
>> *To:* oracle-l@xxxxxxxxxxxxx
>> *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

Other related posts: