yes, we sometimes get deadlocks in the database, net outages and general misbehavior of DCO2. This is on zope2.6; we have not been able to use ChrisW's latest modifications to the the python side of DCO2, because for our use cases it hanged even worse. This was some two years ago, and now that I have gotten permission to work on the site again, the current state of affairs being acceptable, I have not researched the subject. I don't remember exactly but code from connection_leak branch or something like that didn't work for me too.
If everything goes the way I want, we will not be using neither ZSQL not DCO2 connections in the future; we will use SQLAlchemy to create a standard python library that connects to the database with cx_Oracle, and zope will only see Python objects from our custom library. The library will manage the connections to Oracle, and be independent of ZODB transactions (more or less). Nice, but I'm curious how fast is this and how it deals with complicated procedures etc. I have a lot of logic in database sometimes.
The connection checking comes from the fact that that each ZSQL method is bound to a particular connection object. But 'particular connection object' still may have (and usually has) few oracle connections opened.
Yes, but my purpose is not so fine grained. What I do is to call a ZSQL method and see if it executes a piece of SQL successfully. If it does then everything is OK. Not necessarily because some other connections for 'particular connection object' may be broken, so script may execute correctly while different person will execute same ZSQLMethod and will get errors.
It works for us (we have six nodes, so we can "afford" to do that, and we also have quite a lot of traffic, so errors show themselves pretty quickly). If this works then it is acceptable solution :)
This presents a problem with sessions, if they are not shared across all nodes, either by being stored in the RDBMS, for instance, or stored on the ZEO-server (there are options to configure zope to use the ZEO server to storet the temp database). I checked a bit setup with session shared between ZEO Clients and with Pound load balancer. Worked really nice. Restart of one zeo client was invisible for the user.
The only current Zope database adapter that I know of for oracle is only for Zope3, and that one uses cx_oracle (IIRC). We have cxOracleDA for Zope 2.8.x but it was not tested too much yet.
-- Maciej Wisniowski