[SQLObject] ideas about transactions and cache

[Cyril E. <cy...@de...>]

Hello,

We are now using sqlobject heavily in our application and everything 
works very well... well almost :-) Our biggest concern is about 
transactions. We can't use them and they are necessary, at least in our 
own case.

The problem is two sided, it's at the same time a DBMS connection pool 
problem and a (in fact lack of) shared cache problem.

I'll try to explain.

Our application server use one SQLObject connection by client connection 
(a session), this works and permit to use transactions beacause this way 
you have only one DBMS connection in the pool of each SLQLO dbconnection 
and don't risk to use the two different ones between the begin and the 
commit.

But the cache problem appears because if one client do a transaction in 
some objects and other clients already have these objects in their 
respective caches they won't see the modifications. Well known problem, 
and ZODB/ZEO uses a synchronization mechanism, for example, to 
invalidate the caches.

Of course we could use only one transaction for all our sessions but the 
pool cannot warrant that a complete transaction will use the same DBMS 
connection during its lifetime.

A side problem BTW is if you use a classic connection for reading object 
from the database and then instanciate a transaction to write back your 
modifications the classic connection won't see these modifications. The 
reason is the transaction uses its own cache and do not modify the one 
of its parent connection.

Of course we can disable the cache alltogether but the performance is 
awful, in our case 6 to 10 times slower, clearly not usable.

A possible solution.

Use a _common_ cache and remove the DBMS pool from the dbconnection, 
each one connect to the DBMS once instanciated and maintains it opened 
during its lifetime. BTW, I think that it would be interesting to have 
only Transaction connections and let the user apply begin and commits 
when necessary. If necessary a client code can instanciate as many 
connections as necessary (as we already do). In this way every 
modification done by a connection will be reported back to the _common_ 
cache and  everybody will see the changes.

That will not correct the problem of having multiple instance of 
SQLObject in different machines but at least, I think that will permit 
to use transactions with the cache.

Any comments?


Thanks in advance
Cyril Elkaim