Request for option to disable repeatable read

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.

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