FreeTDS Development Group

["Brian Bruns" <camber AT ais.org>]

Hi,

First of all this is not related to FreeTDS.  Locking is a function of the
SQL statements sent to the server, and not a function of what sends them.

That being said, I think I can help.

If you do an exec sp_who while this blocking is happenning you'll see a
number in the "blk" column.  Which will be the spid number of the blocking
process (PHP).  Take that number and run an exec sp_lock <spid> and you'll see
the lock that is causing the problem.  you can then take the object_id and
run 'select object_name(<objid>)' to see the table, but you probably know this
already.

Setting REPEATABLEREAD should actually have a negative impact on
performance since it needs to hold the locks longer to satisfy a
repeatable read.

So, the big question is, what is the PHP script doing that is blocking
other processes...from your description it doesn't sound like the php
should be doing much in the way of insert/update activity to the table (if
it is just generating pdf).  If the cold fusion stuff is being blocked by
a reader process while trying to update, then giving a NOLOCK hint on the
reader process may do the trick.

If it is the update statement from below that is causing the problem, you
may need to check your index on LABEL_ID and make sure distribution
statistics are up to date (anyone know if SQL Server still uses update
stats?).

Brian

> Hello. My company has a SQL Server 7.0 SP3 servers, Windows NT 4.0
> servers running ColdFusion 4.0 that use M$'s ODBC drivers et al to drive
> a ColdFusion application. 
> 
> I have another process running on OpenBSD 2.9, FreeTDS 0.52, Apache
> 1.3.20 and PHP 4.0.6 that generates PDF files that outside users (in
> front of our firewall) use to download Clipping Labels to PDF files. The
> FreeTDS driver is using TDS 7.0 because many of the tables I need to
> print are in TEXT columns. None of the columns in the Clipping_Label
> table are type TEXT.
> 
> The portion of the application that is at issue right now is ColdFusion
> sublaunching Crystal Reports to print a Press Clipping Label. The issues
> I'm having are: When outside users are using the OpenBSD boxen to
> generate PDF files, the stored procedures and queries on the ColdFusion
> equipment stall for up to two minutes. ColdFusion doesn't seem to handle
> other (foreign) clients connecting and using tables while it's trying to
> do its updates and such. The issues are that we can't afford this kind
> of stall, and need stuff to be printed nonstop.
> 
> Network connectivity, database and the ColdFusion servers have been
> tested for performance bottlenecks, but this problem only occurs when
> the OpenBSD machines connect to the SQL Server and generate the PDF
> files. I have attempted to solve the problems by feeding the PHP script
> locking hints like so:
> 
> $query = "UPDATE Clipping_Label WITH(UPDLOCK, REPEATABLEREAD) SET
> Fax_Status = 'P' WHERE Label_ID IN ( $LABEL_ID )";
> 
> This has no effect on the performance of the ColdFusion stuff. 
> 
> I have also rewritten the stored procedures that the ColdFusion
> equipment runs to generate the Crystal Reports reports.
> 
> These haven't worked out. Any other suggestions on how I can muddle with
> the locking between NT's ODBC drivers and FreeTDS?
> 
> TIA,
> 
> Steven Campbell,
> Chief Developer,
> J&A Media Services,
> 300 John St.
> Suite 500,
> Thornhill, ON, Canada
> M5A4J3