Oracle Database Tips by Donald Burleson

Oracle10g Grid Computing with RAC
Chapter 7 - Cache Fusion and Inter Instance Coordination

Block Transfers Using Cache Fusion - Example

In Stage (2), user B issues the same select statement against the 'salesman' table. Instance 2 will need the same block; therefore, the block is shipped from instance 3 to instance 2 via the cache fusion interconnect. There is no disk read at this time. Both instances are in shared (S) mode and the role is local (L). So far, no buffer is dirtied.

In Stage (3), user B decides to update the row and commit at instance 2. The new sales rank is 24. At this stage, instance 2 acquires XL0 (Exclusive Local) at instance 2 and the share lock is removed on instance 3.

Update salesman set sales_rank = 24
SEE CODE DEPOT FOR FULL SCRIPT

In Stage (4), user A decides to update on instance 1 the same row (and therefore the block) with the salesrank value of 40. It finds that the block is dirty in instance 2. Therefore, the data block is shipped to instance 1 from instance 2, however, a past image of the data block is created on instance 2 and the lock mode is also converted to Null with a global role. Instance 2 now has a NG1 (Null Global with past image). At this time, instance 1 will have exclusive lock with global role (XG0).

In Stage (5), user C executes select on instance 3 on the same row. The data block from instance 1 being the most recent copy, it is shipped to instance 3. As a result, the lock on instance 1 is converted to shared global with past image (SG1). On the requesting instance (Instance 1), the SG0 lock resource is created.

Select sales_rank from salesman
SEE CODE DEPOT FOR FULL SCRIPT