[Zope-DB] Re: DCOracle2 leaking Oracle sessions/connections?

[Bo M. Maryniuck]

On Wednesday 22 October 2003 17:30, Chris Withers wrote:
> So, latest CVS should stop connection/session leaking?
Hope, no. Well, connection uses volatile attributes. This means, that when you 
remove the connector, it still present in _v_attribute somewhy (that's other 
good question: sharing between threads, etc) and sometimes even flushing the 
cache can not help. Saying _v_connection = None is not enough. Besides, I 
have posted this several times to the mailing list.

Also Matt's code WRT StoredProcedures uses kind of recursive "magic", like 
caching their schema/package/name's. This means that each procedure know it's 
own cursor (and cursor its connection). =) IOW, once you call findproc() it 
will cache them and after dco2 even unloaded and current db.close() is 
closed, session still alive. That's why I use my own Procedure handling 
digged out from DCOracle2.py wrapper -- then no problem.

Other big caveat (and dozen workarounds to get those gotchas) is a ZODB vs. 
Oracle transaction machinery. This still does not works for me as I want, 
since each commit to ZODB is after each REQUEST. But sometimes I need ONE 
commit to the DB between several commits to the ZODB. I mean, commit stuff to 
the Oracle not only when Zope asked me for, but more rarely and at the same 
time be in "the current transaction" and ALSO do not cause problems for Zope 
(otherwise it will complain a lot and even hang). When it is all based on the 
SESSION and it just... crashed!.. :-( Then I call all this story quote 
shortly: "Aaarrrgghhh!!!".

> Don't quite understand what you're saying. Are you saying the latest CVS
> ZOracleDA still leaks Oracle sessions?
Well, not ZOracleDA, but whole approach. I guess other DB also suffer the same 
problem. At least I am sure for PostgreSQL.

> > 2Chris: I mean id(dco2) != id(dco2) in some cases.
> What would it take to find and solve that problem?
You might change ZOracleDA or might not. The main change should be done in the 
approach of DC Connection et al: DO NOT use volatile attributes for DB 
connectors. Well, I never use Z3 yet, but code says me that Z3 might have the 
same problems, though I don't care yet.

Also could be a problems for Oracle users, who PAY for each connection. Then 
Zope will cause a real pain. Currently connections are uncontrolled: they 
SHOULD be at least connection-per-thread, but in a long run, really they 
grows up even twice off the limit. Crappy enough... :-(

> What Oracle SQL do you use to see what active sessions there are?

SELECT
        sid,
        logon_time,
        username,
        status,
        schemaname,
        osuser,
        process,
        machine
FROM
        p$session

p$session is a view on v$session.

-- 
Regards, Bogdan

Where do you think you're going today?