FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: RCM2, Inc
> Sent: Tuesday, September 27, 2005 4:46 PM
> 
> 1) The only SQL statements where selects.  Three tables are in use
> and there are some joins across the tables.

SELECT statements can acquire locks that prevent updates.  

> 2) While I personally have not verified this, I am told that when
> the lockup occurs, stopping and starting he SQL service does not
> resolve the problem.

If true, then I don't understand "the problem".  Do you mean that after
restarting SQL Server, the Apache server cannot fetch data with SELECTs?
Do you mean other processes *still* cannot UPDATE data even after
restarting SQL Server?  That doesn't seem possible.  

> 3) I can verify that stopping and starting the Apache service on
> the Linux box will kill the SQL box.

By "kill" do you mean UPDATE statements can't be completed?  If locks
are the problem, it will be possible to update tables not associated
with the SELECT statements.  

> 4) I can verify that the ONLY traffic between the SQL box and Linux
> box is the TDS traffic.

Fine.  No firewall, right?  

> 5) I must please some degree of ignorance, I am unfamiliar with
> some of the flags available as part of the connection string; could
> someone give me a pointer.

I don't know what flags you mean, but I don't think this is a function
of how you connect.  From what you've said, it sounds like a matter of
locking.  If your DBA is complaining about the web server interfering
with SQL Server operation, he needs to be clearer (than what you've
provided here) about the state of the server.  

> My obvious conclusion is that we are doing something which is
> triggering a problem on the SQL side.  While is probably is a SQL
> Server bug

I don't see any evidence of a bug.  Blocked updates due to locking
contention is normal behavior.  You need to rule that out first.  

You said you were going to look closer at the SQL Server.  That would be
my advice.  You can use the SQL Profiler to watch the connection come in
from the Apache box.  Note its process ID.  Stop Apache.  Is the process
still there?  Check what locks it has.  Can you update anything with
Query Analyzer?  What exactly is being blocked?  Kill the SPID.
Everything should return to normal within SQL Server.  What exactly
you'll have to do on the Apache side, I don't know.  

If we can narrow this down to Stop Apache -> Kill SPID -> Restart
Apache, that would be progress.  Then we could look into why the second
step is necessary.  

HTH.

--jkl

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Lowden,
> James K
> Sent: Tuesday, September 27, 2005 2:58 PM
> To: FreeTDS Development Group Subject: Re: [freetds] MS SQL Server
> Lockup
> 
> > From: RCM2, Inc Sent: Tuesday, September 27, 2005 3:36 PM
> > 
> > We are running a Web management package (written in Perl) under
> > Apache on Lunux.  We are using FreeTDS to connect to a Microsoft
> > SQL 2000 Server.  The connect string is
> > dbi:Sybase:server=wbshost;database=testdb
> >  
> > If we stop and start Apache on the Linux box, the SQL server will
> > lockup; it will still do selects, but will not do any updates.
> 
> [Further to Brian's helpful reply.]
> 
> There's not much to go on here.  See if the connection maintained
> by your Apache server is being closed on the server.  If there's
> a SPID, does it go away when the server is stopped?  Also, is there
> any firewall between the web and database servers?  That could
> prevent the server from detecting the client's demise.
> 
> It is possible to disconnect rudely from a Microsoft server and
> leave the SPID in place.  I have seen this happen with Microsoft's
> drivers and with FreeTDS.
> 
> Like Brian, I suspect that the problem with not being able to do
> updates is that the server process begins an update, the Apache
> process ends prematurely, but the server process does not.  That
> would leave the transaction uncommitted and the locks in place,
> blocking further updates.   Your DBA should be able to confirm.

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.