IDS Forum

Re: Request for option to disable repeatable read

Posted By: Fernando Nunes
Date: Tuesday, 21 October 2014, at 5:29 p.m.

In Response To: Request for option to disable repeatable read (SCOTT ROBERTS)

This was the most interesting post I've seen for a long time... and just to
be clear, I don't think that this should be given high priority... in fact
I see no need for it. Let me start by explaining why:

1- RR is not the default database isolation level
2- It'r true that for example WAS can use it by default in connections, but
it's also true there is a property to chnage it
3- Web Servers configurations is not something a regular user is able to
change
4- Production environments should go throuth proper tests and if someone
forgets to properly configure it, it's easy to spot, before entering
production
5- Although a user can effectively change the isolation level, only "power"
users should have direct access to SQL coding, and those should know better
6- In recent versions you can control the ammount of locks a user session
can use, so it's also easy to avoid lock escalation

For all these reasons I wouldn't put this request in front of many others
already registered in the RFE site.
But in this case why am I so happy that you posted it? 1st, the above is
just my opinion... so you may get different feedback. But more importantly
because I believe this is what users and customers should be doing around
RFE. Registering their requests and promoting them within the community in
order to get support. I assume that if IBM sees many opinions supporting an
RFE it will understand the importance of it for the customers.

The most usual reason for not implementing something is by far "lack of
market demand". And I believe many of us "in the field" tend to look at
some of the new features as esoteric ones. On the other hand all of us have
a bunch of "pet" RFEs that we think would turn our daily lives much easier.
As usual, both perspectives are probably exaggerated. Occasionally we
understand what's behind the most weird features that are being introduced
in the product. And many times our "pet" RFEs can be handled somehow with
current product functionality or maybe are just too hard to implement. But
in either case it's important to make them clear for the community and
lobby for them.
Again, I won't vote for this, but I truly appreciate the initiative! Good
job, and good luck.

On Tue, Oct 21, 2014 at 9:26 PM, SCOTT ROBERTS <sroberts20@csc.com> wrote:

> If others have problems with Repeatable Read Isolation mode getting set and
> causing problems, I would welcome your vote for Request For Enhancement #
> 60897 Option to disable Repeatable Read Isolation Mode.
>
> http://www.ibm.com/developerworks/rfe/
>
> Description:
> Some option to disable Repeatable Read. At least on the database level if
> not
> the instance level. Once set any set isolation to Repeatable Read statement
> would either return an error, or be transformed into Committed Read, or
> just
> be ignored, what ever is easier to program.
>
> Use case:
> We have a high volume, mixed use (oltp and olap) data mart used by a wide
> and
> diverse user community. The idea is to keep all data available to all
> users.
> We do not want a large repeatable read query disrupting the greater user
> community.
>
> There is no reason to allow repeatable reads in this environment. They
> cause
> nothing but problems.
>
> Business justification:
> Repeatable Read queries have continued to cause problems, even when we get
> all
> the diverse apps to cooperate in not using the statement.
>
> Repeatable read settings can be generated by web servers and by UDB
> Federated
> Database connections. There is some setting on web servers that generates
> repeatable read settings by default. So the problem can re-occur anytime
> someone adds, updates or migrates web servers.
>
> We have also had to change approches because UDB Federated Database
> connections can set Repeatable Read for remote databases.
>
> Once set, these repeatable read queries can lock common info used by a
> great
> number of diverse uses and applications.
>
> Since there is no way to limit total locks, a large number of medium to
> large
> repeatable read queries can cause the engine to allocate large numbers of
> additional locks. We have had cases were repeatable read queries have
> exhausted SHMTOTAL or OS memory and caused server crashes. Even the new Low
> Memory Manager has not resolved the issues. Low Memory manager just changes
> the effects.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--001a113f7d5efb2a810505f58558

Messages In This Thread

• Request for option to disable repeatable read
  SCOTT ROBERTS -- Tuesday, 21 October 2014, at 4:26 p.m.
  • Re: Request for option to disable repeatable read
    Fernando Nunes -- Tuesday, 21 October 2014, at 5:29 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.