This is a migrated thread and some comments may be shown as answers.

Need run a specific query with uncommitted isolation level

7 Answers 139 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Tys
Top achievements
Rank 1
Tys asked on 17 Feb 2012, 10:02 PM
Hi,

I have a big database with many records that are updated constantly. Updating is done by a backend application and run fine. But, our frontend webapplication needs to search through these records and this search sometimes results in a "Lock request time out period exceeded. at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery().. "

After doing some research i have found that the best way to make this query to run without problems is to make it run with "read uncommitted isolation level". I've found that this setting can be made in the OpenAccess settings, but that's a setting that affects the complete project.
Is there a way to make a specific query to run in this uncommitted isolation level?

Thanks for helping! Tys

7 Answers, 1 is accepted

Sort by
0
Tys
Top achievements
Rank 1
answered on 18 Feb 2012, 04:46 PM
If someone knows how to implement / make the linq query to use a WITH NOLOCK hint, that would be helpful as well.
We are really looking for speed here, we'll take small bit of inconsistency in the data for granted. 
0
Accepted
Thomas
Telerik team
answered on 20 Feb 2012, 04:11 PM
Hello Tys,

thanks for the question, I will file a product enhancement request for that. At the moment, we have only a dirty trick that I would not recommend if it wasn't to just get you around the issue:

Action<DbConnection, string> AlterConnection = (con, msg) =>
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = msg;
                    cmd.ExecuteNonQuery();
                }
            };
 
using (var con = Context.Connection)
{
    try
    {
        AlterConnection(con, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
        // your special query here
        var query = from p in Context.Products where p.SupplierID == 2 select p;
        var result = query.ToList();
        Assert.AreEqual(4, result.Count);
    }
    finally
    {   // an absolute must!
        AlterConnection(con, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
    }
}


The trick here is to pin the connection on the context, set the isolation level via ADO API, run the query with the altered isolation level and reset the isolation level to the previous value.

It is a must to reset the isolation level in any case, otherwise nothing can be guaranteed afterwards as we are not resetting the isolation level. It would be best to reset it to the previous value, which I have not done there.  Also, the connection must be returned.

In general, I would refrain from using such techniques, but sometimes they make sense.... We will think about how we can incorporate a NOLOCK on the LINQ level in a future version.

All the best,
Thomas
the Telerik team
Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
0
Tys
Top achievements
Rank 1
answered on 20 Feb 2012, 11:06 PM
Hey Thomas,

Thanks for you reply. Although it's a dirty trick, it's better than nothing :)
It would be nice if there can be a feature in the product that enables users to support this kind of queries. Although it might seem 'dangerous' to some, it still might be useful for others... and you don't HAVE to use it if you don't want it (or don't understand the risks).

Best Regards, Tys
0
XXXX
Top achievements
Rank 1
answered on 21 Feb 2012, 11:34 AM
I have very similar requirements. What I have been trying is to get a command object, either OACommand or DbCommand. That would have different isolation level or be outside the contexts transaction.
Would that require a separate conntection to work ?

I have couple of datamaintainance and "precalculations" queries that should not be in transactions due to performance,
0
Thomas
Telerik team
answered on 21 Feb 2012, 02:55 PM
Hi Björn,

you can use the context.Connection property in the same way, but do not forget to Dispose it once you are done. In order to get a connection that is not bound to the context just use another context (again, to be disposed afterwards).

When you are altering the isolation level, please make absolutely sure that it is set back to the original value, as OpenAccess does not do this automatically when you are returning the connection.

All the best,
Thomas
the Telerik team
Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>
0
Braden
Top achievements
Rank 1
answered on 19 Mar 2013, 08:26 PM
When the context gets disposed, it also disposes of the connection.  If the connection in one context is specifically set to read uncommitted, is it safe to assume that a connection emanating from any other context will still default to read committed, or is there some sharing of connections going on behind the scenes?
0
Thomas
Telerik team
answered on 20 Mar 2013, 12:03 PM
We initially set the isolation level upon creation of the connection, after the BackendConfiguration.ConnectionPool.InitSQL strings are processed. When the connection is changed in it's isolation level by the user, the original value is never restored.
Again: You must make sure that the isolation level change is reverted before the connection is returned.

Kind regards,
Thomas
the Telerik team
Free Webinar: OpenAccess Integration in Sitefinity. SIGN UP NOW.
Tags
General Discussions
Asked by
Tys
Top achievements
Rank 1
Answers by
Tys
Top achievements
Rank 1
Thomas
Telerik team
XXXX
Top achievements
Rank 1
Braden
Top achievements
Rank 1
Share this question
or