ISOLATION UR

Sairamjaya · New User Joined: 21 Feb 2006 Posts: 4

Hello!!

Can we use the isolation UR along with Select count(*) from table. ?
Here the table is a production table. We have to be very cautious while using Isolation UR right?. What would happen if we use in the above select. Is it necessary?

Please advice me a brief reply and please share your experience.

Thank you very much.

DavidatK · Posted: Tue Feb 21, 2006 11:01 pm

Sairamjaya

Yes, of course. This is probably a very good use of it. There is a small likelihood that you will get an incorrect count using the ?WITH UR?. See the post below on what the UR means and how it handles locks.

Previous Post WITH UR

If this does not answer you question, please come back,

Dave

Sairamjaya · New User Joined: 21 Feb 2006 Posts: 4

Do you mean we can use the isolation UR with Select Count(*) from table
is good?

Please explain me.
Thanks.

DavidatK · Posted: Wed Feb 22, 2006 1:46 am

Sairamjaya,

The WITH UR ignores the locks placed on any rows that have been updated, but not yet committed. This allows your SELECT to run even if another program is updating the table. If you do not specify the WITH UR, you program will stop when it encounters a row lock, until the lock has been released. Depending on how many locks are placed, the duration of the locks, you could be looking at a timeout for your program/SQL.

The drawback to using WITH UR is that you are counting rows that have been updated and not yet committed. There is a possibility that a row you counted could be rolled back and if the columns that were updated would affect your count, then your count will be off. It's a trade-off.

Using WITH UR isn't going to impact the production table other than the resource it's taking up doing the select.

I don't think twice about doing selects from production tables. I usually test the SQL on one of the TEST tables to make sure it's efficient and then run it on the production tables. Now, I do run in an initiator that gives production programs a higher priority.

Dave,

Sairamjaya · New User Joined: 21 Feb 2006 Posts: 4

Thank you very much for your explanation Davidatk. It is really helpful.