Block level contention

[satish]

Dear Erman,

Need your expertise

Lets say user A want to update a record in some table.The block is loaded into memory and a latch is acquired in exclusive mode and the corresponding record is updated.

Before user A releases the latch,If some other user B wants to update a different record in the same block,will he be able to update it OR does he needs to wait till user A completes his updation?

Also,will multiple selects on the same block creates multiple versions of that block in dbbuffer cache?

Thank you

[ErmanArslansOracleBlog]

NO! user B doesn't have to wait for user A.

The block header of every segment block contains an interested transaction list (ITL). The database uses the ITL to determine whether a transaction was uncommitted when the database began modifying the block.

Entries in the ITL describe which transactions have rows locked.. "ROWS"

In your case;
Both User A and B both are interested  in updating the same block, but different rows..
In buffer cache, they will get the latch for checking the ITL entires in the same block..
So, When User B 's shadow process will read the ITL entries of the buffer, it will see that there is a transactions (user A) updating the same block (not committed yet), but it is actually updating a different row.. Thus, User B will proceed and update the block..

My answer for your  second question -> INo, they will use the same block..  It will be a buffer cache hit.

[satish]

Thanks for the update erman

Can both the users read the block header at the same time?

Oracle says "Cache buffers chains latches are taken when a process wants to walk through a cache buffer hash chain, looking if the block with required DBA (data block address) is in buffer cache. If the block happens to be in cache, then in most cases it has to be pinned first before use and unpinned after use, to make sure no-one else can perform an incompatible operation on that block at the same time. The modification of pin structures (pinning/unpinning) is also protected by CBC latches."

So,if multiple selects or multiple updates occurs on same block,then each one has to wait to get the latch?


Thanks for all the support.

[ErmanArslansOracleBlog]

You are talking about hot blocks and you are talking about cache buffers chains latch.
They are for protecting the accesss to these hash chains.
I mean, this latch protects the array of pointers, which point to the lists of blocks.
So, after getting the cache buffer chains latch, we get a block out of one of these lists.
In other words, the blocks in the buffer cache are stored on linked lists (cache buffer chains), and that latch is protecting these linked lists.

Now, lets go a little bit further.

When Oracle updates a block in the buffer cache, Oracle copies the relevant buffer and updates that copy.
Similarly, if a query wants to query a row from a block on a certain SCN nuber, Oracle copies the relevant buffer and returns the data from that copy.
So, there are versions/copies.. (Consistent Read versions and XCUR -- exclusive current copies)

Updates are done on copies.

In short, the operation "updating a different row in the same block" will not be blocked.
You can do it.. Locking mechanism will not stop you.. Latches will not stop you..
However, if you do several updates for the same block in parallel , then your updates will slow down..
you will slow down because of the cache buffer chain contention. (hot block)
While your updates are slowing down, your system's load will be increased..
Remember, we try to get the latches by spinning on Cpu.

I hope you understand what I mean.

[satish]

Thanks for the update.

Can multiple processes get the latch at the same time to check ITL entries?
Can multiple processes access the block header at the same time?

Thank you