Linked Server Locks!

Hi,
I am connecting two sql servers together as linked servers, the servers are running on 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 connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
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) to 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 queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
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 conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's 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 ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.