Provide 'IncludeIsolationLevel' (true/false) property... Generated SQL code should not enforce a single level...

[GoogleCodeExporter]

After handing over a project to a client for ongoing maintenance and then 
eventually rejoining them for some enhancements, I discovered that they had 
encountered some performance issues and had attempted to address them 
themselves. They had their 1st challenge in being new to .NET and NetTiers so 
what happened is not pretty - in my mind...

The issue they encountered was related to record locking on SELECT statements 
and their attempt for resolution was to simply write 'overriding' stored 
procedures (i.e. csp_Customer_GetByCustomerId) that would include NOLOCK hints. 
 This was in both purely custom procedures as well they created additional 
custom procedures to override those that were generated by virtue of 
PK,Index,FK,etc...

The application's business logic is exposed as a service-oriented architecture 
and most of the DAL code is being called within an ambient transaction via the 
TransactionScope object (we do NOT use the TransactionManager from NetTiers)... 
The default transaction isolation level is used for the most part... 

What should have been done to alleviate their locking performance problems was 
to address it within the code by adding inner USING statements (with a specific 
transaction isolation level specified as Read Uncommitted) surrounding those 
areas where performing a 'dirty read' operation was appropriate (i.e. 
operations to support search screens, etc)...  

Unfortunately they now have dirty reads EVERYWHERE they applied that technique 
(and no means to not perform this dirty read)... I think they're getting lucky 
and not encountering any problems (known) by sheer luck alone... 

When I inquired as to the when/why this was done, I was told it was because 
NetTiers generated procedures which always include a 'SET TRANSACTION 
ISOLOCATION LEVEL' statement in all of the GET operations.  Which I found to 
unfortunately be true...

Hoping for the best and knowing that there was a preference I could set in the 
CSP properties file - I went looking for a 'None' option to make it exclude the 
statement from the generated scripts...  Unfortunately 'None' is an isolation 
level and selecting that in the list does not prevent NetTiers from generating 
the statement... it uses the 'NONE' level - which given that it is a valid 
level - would be expected...

We need to be able to govern the isolation level from within CODE where the 
use/context is known - the SQL should NOT be making this decision for us - much 
less without an ability to otherwise override it...

What I would propose here would be to either remove the inclusion of the SET 
ISOLATION LEVEL statement and make people address it in their code (as I feel 
it should be in the first place but that's just my opinion) - or allow those of 
us with the need to at least exclude it entirely...

Original issue reported on code.google.com by bh...@questis.com on 21 Sep 2010 at 9:22

[GoogleCodeExporter]

Original comment by bniemyjski on 22 Sep 2010 at 9:39

• Changed state: Investigating
• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

Reviewing old items I logged to follow-up and check for progress...

Original comment by bh...@questis.com on 8 Jun 2011 at 5:23