I have a big SQL Server 2008 R2 database with many rows that are updated constantly. Updating is done by a back end service application that calls stored procedures. Within one of those stored procedures there is a SQL cursor that recalculates and updates data. This all runs fine.
But, our frontend web application needs to search through these rows 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 Telerik OpenAccess settings, but that's a setting that affects the complete database ORM project. That's not what I want! I want this level for this query only.
Is there a way to make this specific LINQ query to run in this uncommitted isolation level? Or can we make this one query to use a WITH NOLOCK hint?
Use
SET LOCK_TIMEOUT -1
in the beginning of your query.
See the reference manual
Runnung the queries in read uncommitted isolation level (and using NOLOCK hint) can cause many strange problems, you have to clearly understand why do you do this and how it can interfere with your dataflow