[Zope] RE: ZODBC problem (was: Preventing WebDAV locks)

[Capesius, Alan]

Thanks Jeffrey, 
Where are these WebDAV.resource.lock() permissions?
I am running both 2.2 and 2.3 Zope and cannot find this anywhere.

The locks seem to be coincidental to the problem, with further testing I
have determined that the Zope server itself is not locking up, but rather
requests to the database (MS SQL7 via ZODBCDA) seem to be queueing up. 

I have several long running report queries that were recently added to the
server. They do totalling within DTML to provide accounting reports. If two
clients request these reports at the same time, all other SQL based access
is halted until one of the queries finishes.

Any query is unresponsive while the server is processing these long running
queries (30 secs +). Even very short queries will not respond until the
client processing the long queries frees up.

I've tried increasing ZODB pool_size, but this does not help.
The SQL Client is set up for TCP/IP connection to the SQL server on another
box.
Is there a limitation here. A fix?

Or perhaps another way to connect to the SQL server than ZODBCDA?

In addition, I've loaded two instances of Zope on the same server running
identical configurations. One instance will stop the other instance from
accessing the database. This leads me to believe it may be a bottleneck in
the SQL ODBC driver.

Alan Capesius
Technical Support Engineer
Sysmex Corp of America


>>>-----Original Message-----
>>>From: Jeffrey P Shell [mailto:jeffrey@Digicool.com]
>>>Sent: Wednesday, February 28, 2001 8:20 AM 
>>>To: zope@zope.org
>>>Cc: Capesius, Alan
>>>Subject: re: Preventing WebDAV locks.
>>>
>>>
>>>> ----- Forwarded message from "Capesius, Alan" 
>>><CapesiusA@Sysmex.com> -----
>>>> 
>>>> Subject: [Zope] Preventing WebDAV locks?
>>>> From: "Capesius, Alan" <CapesiusA@Sysmex.com>
>>>> To: zope@zope.org
>>>> Delivery-date: Tue, 27 Feb 2001 22:48:38 +0100
>>>> X-BeenThere: zope@zope.org
>>>> 
>>>> I have some pages configured to return Excel format to the 
>>>user using the
>>>> technique detailed at: http://twsite.bizland.com/twzop0014.htm
>>>> 
>>>> When executed from Netscape everything works fine, when 
>>>executed from IE
>>>> 5.5, if the user opens the file directly into Excel via 
>>>embedded OLE (clicks
>>>> open or has the XLS associated), the client is locking the 
>>>file on the
>>>> server, preventing other users from accessing the data.
>>>> 
>>>> Another client attempting to access the same file will not 
>>>receive a
>>>> response until the first user times out (3 minutes).
>>>> 
>>>> Zope is unresponsive until the lock is released.
>>>>
>>>> Is it possible to prevent these client locks from the 
>>>server? or to force
>>>> read-only in Excel?
>>>> The user is anonymous.
>>>
>>>If the user is Anonymous, they shouldn't even be actually 
>>>getting locks, as
>>>they're protected by the "WebDAV Lock items" permission.  
>>>This permission
>>>protects the webdav.Resource.LOCK() method itself, so the 
>>>client should be
>>>getting an unauthorized exception on the LOCK command 
>>>(unless you've granted
>>>the permission to Anonymous users).
>>>
>>>I set up a situation in GoLive 5 where the user had access to every
>>>permission *except* "WebDAV Lock items" and "WebDAV Unlock 
>>>items".  When I
>>>opened documents to edit, it would warn me about being 
>>>unauthorized to lock
>>>items, but open the document anyways.  While the document 
>>>was open, Zope was
>>>still responsive.  Even after I granted access to Locks, 
>>>Zope was still
>>>responsive while the Lock was in place.
>>>
>>>There seem to be some issues with IE 5.5 and 
>>>"Content-Disposition" or some
>>>other header or setting that seems to cause it not to close 
>>>connections
>>>properly on certain requests.
>>>
>>>> [27/Feb/2001:21:31:57 -0500] "LOCK 
>>>/IOTA/report_ordersmtd?format=xls
>>>> HTTP/1.1" 200 817 "" "Microsoft Data Access Internet 
>>>Publishing Provider
>>>> DAV"
>>>> [27/Feb/2001:21:32:21 -0500] "GET /IOTA/report_ordersmtd?format=xls
>>>> HTTP/1.1" 200 71207 "" "Microsoft Data Access Internet 
>>>Publishing Provider
>>>> DAV"
>>>> [27/Feb/2001:21:32:21 -0500] "UNLOCK 
>>>/IOTA/report_ordersmtd?format=xls
>>>> HTTP/1.1" 204 240 "" "Microsoft Data Access Internet 
>>>Publishing Provider
>>>> DAV"
>>>
>>>Jeffrey P Shell, jeffrey@Digicool.com
>>>http://www.digicool.com/ | http://www.zope.org
>>>
>>>