Linked Server Lockes - StressTesting

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 transact
ion 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 deadl
ock 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 curre
nt 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 tra
nsaction, 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 tha
t 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 serv
ers. 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' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, 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 SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
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.