Need run a specific query with uncommitted isolation level

8 posts, 1 answers
  1. Tys
    Tys avatar
    14 posts
    Member since:
    Jul 2011

    Posted 17 Feb 2012 Link to this post

    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
  2. Tys
    Tys avatar
    14 posts
    Member since:
    Jul 2011

    Posted 18 Feb 2012 Link to this post

    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. 
  3. DevCraft banner
  4. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 20 Feb 2012 Link to this post

    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 >>
  5. Tys
    Tys avatar
    14 posts
    Member since:
    Jul 2011

    Posted 20 Feb 2012 Link to this post

    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
  6. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 21 Feb 2012 Link to this post

    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,
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 21 Feb 2012 Link to this post

    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 >>
  8. Braden
    Braden avatar
    12 posts
    Member since:
    Oct 2012

    Posted 19 Mar 2013 Link to this post

    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?
  9. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 20 Mar 2013 Link to this post

    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.
Back to Top
DevCraft banner