Linked server problem

Hi,

I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.

However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.

The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.

Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.

I do highly appreciate any help or support on this.

Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.

My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!

-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?