Ignore Uncommitted; Loose Coupling DB - Objectscope

[Martin Gschleiner]

Hello,

I have searched the forums for topics related to the OQL query parameter "Ignore Uncommitted", unfortunately to no avail. Here is my scenario:

In two recent projects I make rather extensive use of "iquery.IgnoreUncommitted = False". Rather late in the process I realized that both applications scale rather badly (frequent "lock request time out" exceptions in spite of using "Optimistic Concurrency Control"). A more thorough dive in the OpenAccess documentation revealed the negative side-effect of "IgnoreUncommitted = False": the loss of the loose coupling between database and objectscope. Before beginning redesigning these rather complex (released) applications to work without "IgnoreUncommitted = False" I'd like to ask if there are other methods to improve scalability without sacrificing the benefits of "IgnoreUncommitted = False".

Thanks in advance,

Martin Gschleiner

EVN, Austria

Below a typical example for object retrieval:

        public static List<SubsidaryCompany> GetAllSubsidaryCompaniesList(IObjectScope objectscope)
        {
            const string query = "Select * from SubsidaryCompanyExtent as s order by s._subsidaryCompanyId";

            IQuery iquery = objectscope.GetOqlQuery(query);
            iquery.IgnoreUncommitted = false;
            IQueryResult result = iquery.Execute();

            List<SubsidaryCompany> m = new List<SubsidaryCompany>();
            foreach (SubsidaryCompany med in result)
                m.Add(med);

            result.Dispose();
            return m;
        }

[Thomas]

Hello Martin,

the IgnoreUncommitted=false will push all changes in memory to the server (without finally committing) so that the server can see the changes and is able to perform queries on the data. This is what IgnoreCommitted=false is for.

The side effect of this is, that the scope gets an pinned connection, as the changes of the scope are already flushed to the server, and it seems, this is what gets you in trouble. 

There can be two reasons why this might be critical: 

(a) The database server is not so efficient when not-yet-committed changes need to be taken care of in queries (duration of the queries is longer, as on-disk-structures must be merged with the changes in memory).

(b) The data that is flushed is also causing locking and isolation activities.

I guess the latter one is the critical part. The only way to improve the scalability is to make the (ObjectScope) transactions really short lived, so that the locks are freed quickly again.

When no flushing is done before a query is executed, the time a server connection is really used is during Commit(), and that is usually quick, no user interaction is possible. However, when you are performing queries with IgnoreUncommitted, the data is flushed to the server and the connection is pinned, even when the user in front of the data has gone for a coffee break. That might cause a lot more locks in the server. In essence, you should try to use flushing queries only when no user interaction is possible.

In your case you should also see how long an object scope is really needed, maybe you can dispose it more quickly, freeing the pinned connection too.

All the best,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.