[Zope] RE: [Zope-dev] RE: ZODBC problem

[Bryan Baszczewski]

Alan, I am having a similar lock-out problem while performing a complex
query on SQL Server 2K.  All other hits cannot connect until the query
returns the results.  My research has so far taken me here:

http://www.zope.org/Members/petrilli/DARoadmap
Which is telling me I am probably using at most a Level-2 DA with my ZODBC.

And then here:

http://www.zope.org/Members/djay/ZmxODBC/ZmxODBC_0_0_2.tgz/README
Which is a Level-3 mxZODBC DA (multithreaded).

If anyone sees a problem with what I am about to do, let me know.  What do
you think?



-----Original Message-----
From: zope-dev-admin@zope.org [mailto:zope-dev-admin@zope.org]On Behalf
Of Capesius, Alan
Sent: Wednesday, March 07, 2001 12:31 PM
To: zope@zope.org
Cc: zope-dev@zope.org
Subject: [Zope-dev] RE: ZODBC problem


Thanks Dieter, I was kinda looking for a solution, I reviewed the mailing
lists but do not see any solutions. I've done some more testing here:

Summary: The user selects an involved financial report. The report takes
about 55 seconds for Zope to prepare. The first second is the SQL server
request and reply. The remaining time is Zope processing (dual 200mhz NT
box).

My queries are SELECT JOINS. No tables are modified at the server.

The problem I see here is that Zope/ZODBCDA is holding the connection to SQL
open via ODBC until all processing is complete and then issuing an SQL
statement:  IF @@TRANCOUNT>0 COMMIT TRAN

Zope could free up this connection after one second. (at least in this case)
Until Zope does the commit, other users are locked out.

For read only queries, there is no need to have the request under
transaction control. In any case, Zope should be able to early release the
SQL connection and then process on the results.

Certainly increasing processor speed will mask this problem and provide
better response to users, but I'm hoping for a real fix.

Thanks,
Alan

Network Monitor trace  Wed 03/07/01 10:51:50  c:\cap1.TXT

Frame   Time    Src MAC Addr   Dst MAC Addr   Protocol
1       4.413   006008F6062B   COMPAQ851F04   TCP
2       4.413   006008F6062B   COMPAQ851F04   TCP
3       4.413   006008F6062B   COMPAQ851F04   TCP
[....]
193     5.072   006008F6062B   COMPAQ851F04   TCP
194     5.073   COMPAQ851F04   006008F6062B   TCP
195     5.090   COMPAQ851F04   006008F6062B   TCP
196     5.090   006008F6062B   COMPAQ851F04   TCP
197     5.090   006008F6062B   COMPAQ851F04   TCP
198     5.091   COMPAQ851F04   006008F6062B   TCP
199     5.109   COMPAQ851F04   006008F6062B   TCP
200     5.127   COMPAQ851F04   006008F6062B   TCP // SQL server goes idle
after this

// python DTML processing occurs here


201     10.834  006008F6062B   COMPAQ851F04   TCP // not sure what this is,
Data is 0xA8
202     10.834  COMPAQ851F04   006008F6062B   TCP // reply, data is empty

// page returns to user at 60 second mark
// then Zope closes the connection
203     60.385  COMPAQ851F04   006008F6062B   TCP
        SQL: IF @@TRANCOUNT>0 COMMIT TRAN
204     60.386  006008F6062B   COMPAQ851F04   TCP
205     60.601  COMPAQ851F04   006008F6062B   TCP


>Capesius, Alan writes:
> > ...
> > 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.

>My memory tells me faintly that such an issue has already been
>discussed in the mailing lists (zope, zope-dev).
>When I remember right, ZODBCDA synchronizes all database requests
>--> searchable list archives...


>Dieter

_______________________________________________
Zope-Dev maillist  -  Zope-Dev@zope.org
http://lists.zope.org/mailman/listinfo/zope-dev
**  No cross posts or HTML encoding!  **
(Related lists -
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope )