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