Timeouts on Update Statements

Question

Timeouts on Update Statements

jstroud

Grasshopper

Points: 10
More actions
January 22, 2009 at 8:03 pm

#130325

This is the deal: a simple SELECT statement on a table works fast and fine. An INSERT/UPDATE/DELETE on the same table times out. Just to provide more info, we are using the broker for SqlCacheDependency on this database and (as far as I can tell) all the tables we are having trouble with are part of a full-text index on an indexed view. Searches using that full-text index work perfectly and deleting has no affect on the problem.
If it was a lock that would definitely affect the SELECT, but the SELECT works fine. Anyone have any ideas?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004686 More actions · Answer 1

SELECT usually uses some form of shared lock unless it's in an explicit transaction along with one of the other statements. Inserts and Updates use anything from a rowlock to a full table lock depending on lock escalation. You can try adding the WITH ROWLOCK hint to your inserts and updates to prevent such escalation (still might not work), but I believe you'll find the real problem in the Indexed View whose rows get recalculated... if you have aggragations in that view, it could become a bit of a problem if not done correctly.

Also, have you looked to see if there's any triggers involved?

And, it could be the broker. I've never used SB, but I've heard that it's had a couple of problems. You are running at least SP2, aren't you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jstroud Grasshopper Points: 10 More actions · Answer 2

Thanks for the response. I've narrowed the problem down to a particular query that I am using SqlCacheDependency with. The query has a primary table with many joins to various reference tables. The query actually works fine under the many rules around query structure for use with SqlCacheDependency, but nevertheless, when the cache for that query invalidates, it does something to all tables involved and locks up INSERT/UPDATE/DELETE statements...although SELECT statements to all tables still work just fine.

Because I could not figure out why this would happen, I just removed my usage of SqlCacheDependency for this query and went the more standard method of just timing out the cached results on a specified interval. I lose the auto-update of the cache when underlying tables are modified, but I fixed my problem.

If anyone has any ideas about how I could get the problem I've described, please feel free to post a response. Thanks!