1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tys
  • 3,592
  • 9
  • 49
  • 71
  • If there is a way to hint our updating back end application in a way that it helps us to prevent from these locking errors, that would be interesting as well. The most important thing is that our frontend can perform fast searches. Speed is also more important than data consistency. – Tys Feb 18 '12 at 16:04
  • 2
    If you are able to edit the queries directly, you can use this statement to set isolation for the current session: `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` – J Cooper Feb 18 '12 at 16:09
  • 1
    I bet that replacing your cursor solution w/ a set based solution will cut down on your locking issues and probably perform better. – J Cooper Feb 18 '12 at 16:10
  • I've done some tests, but with the amount of data we have, the cursor based solution performs faster than the set based equivalent. – Tys Feb 20 '12 at 17:30
  • For the rest, the Telerik ORM doesnt allow us to edit the SQL queries directly, so that trick is not going to work. – Tys Feb 20 '12 at 17:32
  • really? performs better with a large amount of data? I guess that's bound to happen every once in a while when the stars line up right... :) – J Cooper Feb 21 '12 at 01:31
  • 1
    Read some on stored procedures that have to do a lot of calculations / aggregations and do some testing. After a while your stars will automatically form a light bulb shape ;) – Tys Feb 21 '12 at 22:52

1 Answers1

2

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

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • I know all about the 'strange problems' that might happen, so that why i clearly stated that i'm overseeing and willing to take these 'risks'. If you know what the risks are, then they are not that big anymore :) – Tys Feb 20 '12 at 17:34