• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 861
  • Last Modified:

Insert Timeout

I have an insert statement I am running from a .NET application, it runs through a stored proc.

It's a simple insert and it hangs and times out. I've set the timeout limit higher, and now it just runs and never completes. When I run it through Management studio with the same parameters it adds in 1 second.

I can't figure this out.

I think it's being blocked by something.


This brings the following results:
SELECT * FROM sys.dm_exec_requests;
57	2	2012-09-06 08:20:26.423	suspended	SELECT	0x0200000051224531F654C2B7F5206D6C811136D86C91C3810000000000000000000000000000000000000000	0	-1	0x06000700512245311058B9FF0100000001000000000000000000000000000000000000000000000000000000	7	1	ADE0AC9E-0E51-49D7-A5D8-D054A54D5D5B	0	ASYNC_NETWORK_IO	631	ASYNC_NETWORK_IO		0	1	399395	0x	0	0	0	102416	1	0x00000001F89D30C8	0	0	0	-1	us_english	mdy	7	1	0	1	0	1	1	1	1	2	-1	0	0	0	0	67224	0
119	0	2012-09-06 08:21:20.470	suspended	INSERT	0x03000700B982EE5A65A08001C3A0000001000000000000000000000000000000000000000000000000000000	1978	2732	0x05000700B982EE5AC03EAFE10100000001000000000000000000000000000000000000000000000000000000	7	1	9225A619-F1E3-44FE-BD5E-A3765832DCA4	57	LCK_M_IX	48370	LCK_M_IX	PAGE: 7:1:2333 	2	1	400422	0x	0	0	0	48371	3	0x00000001EFB16CF8	0	0	3	-1	us_english	mdy	7	1	0	1	0	1	1	1	1	4	-1	0	0	0	1	0	0

Open in new window




So I'm seeing suspended and LCK_M_IX on th Insert, so is this a user issue?
0
poweraddict
Asked:
poweraddict
  • 3
  • 2
1 Solution
 
vastoCommented:
run sp_who2 and check BlkBy column to see if there are blocks
0
 
poweraddictAuthor Commented:
blkby 58 on my insert statement
0
 
mastooCommented:
58 is the spid of the blocking  process, so also look in the sp_who2 for that spid.  This leads to the user/system causing the block.  In the simplest case, you ask them to disconnect or close their transaction.  In the more complicated case, it is the same application as the insert and you need to study the blocking condition to come up with a solution.,
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
poweraddictAuthor Commented:
For 58 I see my login name I use in my sql connection string from my .net application
58    SUSPENDED                      gsxraddict                                         BW-PC   .   PPDatabase SELECT           0       0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           234     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           218     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           218     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2    
58    SUSPENDED                                                                         BW-PC   .   PPDatabase SELECT           187     0      01/01 00:00:00 .Net SqlClient Data Provider                                       58    2

Open in new window

0
 
mastooCommented:
And you're running a single copy?  Make sure both the blocked and blocking spid have the same host name, login name, and application name.

Assuming yes, then the application has multiple connections open with a select operation holding a lock that is preventing another connection from doing an insert and the fix requires some study with a developer making a change.  Or an ugly work-around would be to close the app and try again to get to the insert without hitting the piece that does the select.
0
 
poweraddictAuthor Commented:
Thanks, I created a temporary table and it worked, I was modifying the table inside of a datareader.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now