You Asked

HI Connor

Readers don't block Readers, is this true?

As I understand waiting for a resource is also a kind of blocking. I am waiting because the access is blocked.

Then please help understanding the below

" ---- Another example of a problem is of multiple users running full table scans on the same large table at the same time. One user will actually read the block physically off disk, and the other users will wait on Buffer Busy Wait for the physical I/O to complete."

The above I find in the below link

https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_buffer_busy_waits_pct.html

Just wanted to understand, where my understandings need more enlightenment.

Thanks and Regards,
Sandeep

and Connor said...

Different orders of magnitude here.

At all layers in computer processing, ultimately, some operations have to be atomic, ie, only one person can do them at a time, eg storing a value in a register, gaining exclusive access to a mutex, and the like.

They are typically very fast operations, with a known lifespan.

Writers-blocking-readers (for example) is quite different, because

a) the durations could be very long,
b) the duration is not generally predictable in advance (it could be "forever")

For me, I want my applications to be able to have as much concurrent access to resources as possible, without it jeopardising the integrity of the data I'm dealing with. For example, some databases offer "increased" concurrency but letting you read dirty data (eg uncommitted transactions)...That's not the kind of concurrency I want :-)

So concurrent access is not a boolean, ie, a "yes" or "no" proposition. It's a sliding scale. We have readers-do-not-block-readers, which puts us further along the concurrency scale than some other platforms. But we also have concurrency *controls* (eg buffer busy wait) to ensure that the concurrency does not compromise the integrity of the data.

Hope this helps.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sandeep Mishra, August 26, 2016 - 10:03 am UTC

HI Connor,

Thank you for the clarification.

So what is the triggering point, where a reader need to block another reader?

Does 'Select for update' is a triggering event, to block one more reader?

Thanks and Regards,
Sandeep

August 26, 2016 - 1:31 pm UTC

You only need to block... when the integrity and/or validation of your data needs it.

For example, using the standard DEPT and EMP tables.

If you have a rule "no-one's salary can be more than $100", then a simple check constraint will do.

But if you have a rule "no department can have more than 10 employees", then doing this:

- before insert on EMP do a 'select count(*) from DEPT where deptno = ...'

is *not* good enough, because there may be 4 people doing this insert all at once, and we dont see each other's uncommmitted transactions. So we might all see total of "9", then we all commit, and ... bang...now there are 13 people in a department and we've broken our rule.

One way we could stop that is to do:

- before insert on EMP
- select for update the row in DEPT for that department
- now do my 'select count(*)' for that department, because I know that I have exclusive access
- if I come in less than 10, then I insert and commit (thus allowing the next person to get a lock if they need it)

So blocking is sometimes needed to ensure data is correct. And thats fine. What is often a pain is blocking that occurs just because the database technology you are using has no other mechanisms available. (and Oracle doesn't do that :-))

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer