Linked Server Lockes, mass testing

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ali Salem (AliSalem_at_discussions.microsoft.com)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 10:15:06 -0700

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 Salem

Relevant Pages

  • Re: Setting up Linked server to MsAccess
    ... This will set the security so anyone using the linked server ... At the database level I have added my Server login ...
    (microsoft.public.sqlserver.security)
  • Re: MAS90
    ... To set up a linked server from SQL Server 2000 to MAS90 using Enterprise ...
    (comp.databases.ms-access)
  • Re: Linked Server to MS Access DB: Login Mapping Error
    ... >connected to a different SQL server in Query Analyzer. ... Under "Linked Server" I put the name I wanted to reference it by. ... Put path to location of database in "Data Source" (in this case, ... >made without using a security context" option was checked. ...
    (microsoft.public.sqlserver.security)
  • Re: Linked Server
    ... SQL instance to be able to delegate on your behalf to the 2nd SQL Server ... [Create Linked Server Object on Middle Server] ... set up its login to use delegation. ...
    (microsoft.public.sqlserver.server)
  • RE: Linked server 7302 error
    ... Is "this DB" a SQL Server DB or a Oracle DB? ... 280106 How to set up and troubleshoot a linked server to ... related to security. ...
    (microsoft.public.sqlserver.connect)