Doubts about the "For Read Only" clause

[Rahul Babbar]

Hi,

I had the following doubts about the "For Read Only" clause.

1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?
Can someone clarify on that?

Thanks a lot.

Rahul

[Mark A]

"Rahul Babbar" <ra***********@gmail.comwrote in message
news:55**********************************@s13g2000 prd.googlegroups.com...

Hi,

I had the following doubts about the "For Read Only" clause.

1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?

Can someone clarify on that?

Thanks a lot.

Rahul

The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.

WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..

[Rahul Babbar]

On Feb 8, 12:56 pm, "Mark A" <[e-mail]:

"Rahul Babbar" <[e-mail] in message

news:55**********************************@s13g2000 prd.googlegroups.com...

Hi,

I had the following doubts about the "For Read Only" clause.

1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?

Can someone clarify on that?

Thanks a lot.

Rahul

The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.

Thanks a lot.

How does the DB decide whether the cursor "intent" in ambigous?
Can you please explain that?

WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..

Also,

I read that "for read only" may improve the performance because the
DBM can retrieve blocks of data.
What exactly does that mean?
If it does increase the performance of the DB, will it be better "With
UR"? (i personally don't think so, i mean if the DBM can retrieve
blocks of data in a read only clause, why not it can do the same thing
in "with UR")

Thanks a Lot

Rahul

[Serge Rielau]

Rahul Babbar wrote:

On Feb 8, 12:56 pm, "Mark A" <[e-mail]:

>"Rahul Babbar" <[e-mail] in message

news:55**********************************@s13g200 0prd.googlegroups.com...

>>Hi,
I had the following doubts about the "For Read Only" clause.
1. How does a "for Read only" clause improve the performance?
2. How does a "for Read only" clause compare with "With UR" clause in
performance? Which is faster?
Can someone clarify on that?
Thanks a lot.
Rahul

The "for read only" can minimize the degree of locking in certain cases to
prevent anything higher than a share lock from being taken where DB2 thinks
your cursor "intent" is ambiguous and it takes a lock stronger than share.
It can also affect cursor blocking.

Thanks a lot.

How does the DB decide whether the cursor "intent" in ambigous?
Can you please explain that?

The intent is ambigous if you have NOT specified our intent (read,
update, delete), but the cursor is "deletable".
I like this link to define what that means (never mind that it talks
about views. One set of rules for all):
http://publib.boulder.ibm.com/infoce.../c0052324.html

>WITH UR prevents even a share lock on a SELECT statement. This potentially
might be help prevent lock contention in some cases, so long as you are
willing to live with any issues caused by inconsistent data being read
(where another transaction has not finished its unit of work and you are
seeing only some of the updates in an uncommitted transaction). But keep in
mind that multiple share locks on the same resource (row, index, table, etc)
do not conflict (and do not cause lockwaits), so WITH UR will only help in
"reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
on the statement that has the WITH UR, or another transaction that is trying
to update a row the WITH UR is selecting..

I read that "for read only" may improve the performance because the
DBM can retrieve blocks of data.
What exactly does that mean?

This is what Mark referred to as "blocking".
Blocking means that DB2 will toss a "block" of rows over to the client.
Subsequent FETCH operation by the client are served from this buffer,
greatly reducing latency. So blocking is very important for application
performance.

If it does increase the performance of the DB, will it be better "With
UR"? (i personally don't think so, i mean if the DBM can retrieve
blocks of data in a read only clause, why not it can do the same thing
in "with UR")

These are really orthogonal. The point is that if a cursor is FOR UPDATE
or FOR DELETE then DB2 has to keep the position of the cursor because it
expects an DELETE/UPDATE WHERE CURRENT OF.
That is the reason to not do blocking.

Aside from the concurrency, semantic differences WITH UR has one more
effect:
A lock that is not taken does not have codepath overhead.
In a data warehouse environement the difference between CS and UR can be
significant (high single to low double digit percent!)

So what you want to do is this:
If a cursor is meant to be read only: Mark it as READ ONLY
AND
If you don't mind reading uncommitted data specify WITH UR

Always tell DB2 what you expect to get the best performance.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab