UPDATE on TEXT Column leads to blocking & timeouts

[MissyEd]

On a fairly busy website, we are attempting to capture the HTML when specific conditions are met.

Each time a page is visited, a SP is called to determine if those conditions are being met. If they are, details are logged to a table, with a auto incrementing integer clustered primary key. This key is returned to the .NET code, which will send the HTML page if the returned ID is not null to another SP that updates the log table with the HTML the visitor is actually seeing into a TEXT field.

The reason this is done in 2 steps is to reduce network traffic incurred by sending the HTML page every time when it will only be needed possibly 1% of the time.

I noticed immediately the code went live that timeouts began to occur in unrelated Stored Procedures i.e SP's that access other tables other than the conditions or log tables mentioned above. The only relationship appears to be that the SP's that timed out use the tempdb for temporary tables / table variables at some point.

Am I barking up the wrong tree here?

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[genomon]

Difficult to say without code to view; at first blush I would suggest using the WITH(rowlock) locking hint on your UPDATEs to avoid page locking while the code executes.
If this doesn't help, please post back a sample snippet of the offending sp's.....

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[MissyEd]

Hi

I tried the WITH ROWLOCK hint but it didnt appear to resolve the issue. We've found that setting the column to an empty string as default then updating using UPDATETEXT has *touch wood* resolved the issue. The largest amount of data I've found in the text field so far is 288bytes. In the meantime, I will stick a trace on to make absolutely sure Im not missing something or storing up grief using the UPDATETEXT syntax.

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter