0

I am running into a problem which I have a table that can be edited by many thread, each thread creates a transaction to work with this table, if a transaction takes so long to complete, other threads will throw the exception "Query Timeout Expired". I guess there is a timeout setting somewhere for transactions.

I could use a thread lock to deal with this; however, is there a way to do this on the server side? Can I create a lock in SQL server ? Any helps would be appreciated.

stoney78us
  • 195
  • 2
  • 5
  • 17

1 Answers1

1

The timeout you are receiving is not a "transaction" timeout. It is a general query timeout. This query could be a Select, Update, Delete etc. The timeout value is generally controlled in the connection string.

I don't believe you can specifically lock rows, pages or tables in SQL Server, which is probably a good thing. Frankly, this isn't the crux of the problem. Your transactions are taking way too long to execute. They should never take so long as to cause other queries to timeout (probably at least 30 seconds). You are going to bring your server to its knees with transactions taking this long. A transaction should be kept to a couple seconds, at the very most.

I would suggest you first take a look at the statements requiring the transaction (Update, Delete or Insert), run them through Query Analyzer in SSMS, and make sure no table or index scans are being done. My guess is this is happening, and the tables involved in the transaction are on the large side (hundred of thousands of rows or more).

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
  • Actually, the table that i work with has hundred thousands of records, so a thread might take 5 or 10 mins to update that table, in other words to finish its transaction. I suspect this is the reason that cause other threads couldn't work on this table and throw the exception. – stoney78us Dec 19 '12 at 17:30
  • Well there you go. That's the problem. You need to find a way to reduce the transaction time to a couple seconds or less, or re-architect your database (which probably is necessary). – Randy Minder Dec 19 '12 at 17:32
  • thanks for ur answer. I think i figured out what is happening here. I use IsolationLevel.ReadCommitted for the transaction and this blocks any attempts to access to the table from other threads and cause other thread query timeout. Using IsolationLevel.UnReadCommitted helps but might lead to dirty read. – stoney78us Dec 19 '12 at 18:00
  • Right. You'll have to decide if the possibility of reading dirty data ia acceptable. But, the real solution is to re-architect your transactions. – Randy Minder Dec 19 '12 at 18:08
  • 2
    5 or 10 minutes to update a table that only has a few hundred thousand rows is a very long time. I would suggest that rather than resorting to NOLOCK you spend time optimizing the current workload. If you're hitting the query timeout limits, you are already in trouble and NOLOCK will only delay your day of reckoning. – DeanGC Dec 19 '12 at 18:32
  • i wonder how long does it normally take to insert 300k of records ? – stoney78us Dec 19 '12 at 20:25