Re: [SQLObject] Expiring Cache

[Ian B. <ia...@co...>]

On Monday, October 27, 2003, at 04:47 PM, Sidnei da Silva wrote:
> The tricky part for me is that I'm not using the SQLObject Transaction
> class, but instead, I'm wrapping DBConnection on a Zope-specific
> Transaction class. :)

Okay, just be sure to look at how SQLObject's Transaction class works.  
You could probably subclass from it.  It's not too complicated, but it 
is a little funny (maybe in a bad way, in particular 
Transaction.__getattr__).

> | >AFAICS, in the current implementation, the state of 'x' from
> | >transaction '2' would override the state of 'x' as saved by
> | >transaction '1'. Does that sound correct?
> |
> | To clarify, we're talking about "object with id x", which is actually
> | two instances.
> |
> | Yes, transaction 2 will just clobber 1.  This is what other people 
> have
> | talked about as optimistic locking, and it requires adding special
> | support in SQLObject.
> |
> | I think it will mean something like a magic last_changed column in
> | SQLObject tables, which gets updated each time a change is made (but
> | you can't update it manually).  Then each time you do an update you 
> add
> | "WHERE id = <my ID> AND last_changed = <my expectation of
> | last_changed".  Then you test how many rows your query effected
> | (cursor.rowcount I think, but this might be database driver 
> specific),
> | and if you get 0 then you raise an exception.  last_changed could
> | probably be an integer, with the value of time.time()*1000 (will that
> | get too big for databases?).
>
> Probably yes. I think that requiring a last_changed column in each
> table may not be a good idea though. As long as we just care for
> changes that happen when two transactions overlap, we could just have
> a module-level registry of objects that changed on a transaction, then
> we wouldn't need any change to the databases at all. Note that if
> there are two separate transactions which dont overlap, last_changed
> is meaningless, so not useful.
>
> <snip part about connections x transactions >

Yes, that would be a lot easier.  The disadvantage being if you want 
non-SQLObject code to update the database, or if you are using multiple 
processes.  But that's mostly just something to note in the 
documentation for a feature like this -- the expedient solution doesn't 
preclude the option of a database solution.

If so, then the records should go in the DBConnection object (which all 
the related transactions will share).

> | Yes, fetching the same object within the same transaction will give 
> you
> | back the object that has already been created, and is pretty quick to
> | do.  If a transaction implicitly is restarted after a commit or
> | rollback, then you can even reuse the transaction and have the 
> benefit
> | of a valid cache.
>
> Amazing. Theres one last question now: currently the transaction is
> implicitly restarted or not?

I don't think so.  It doesn't do a BEGIN, at least.

Probably what should happen is that commit or rollback should "close" 
the transaction.  But in doing that, you can put the transaction back 
into a pool of transactions, and DBConnection.transaction can fetch 
from that pool.

No, actually it should be separate.  You can commit (or rollback) a 
transaction, then continue using the transaction (assuming a BEGIN is 
sent).  But once Zope ends its transaction, the SQLObject transaction 
should be "closed", which returns it back to a pool of transactions 
which can be reused.  An option to DBConnection can control whether 
transactions are pooled, or just thrown away when they are returned via 
a close.

> OT: I changed my code to have a connection cache of one connection per
> thread, so I guess this is going to be just enough for my needs *and*
> still have the benefit of a connection cache that lasts through
> transactions. I have to check more throughly that the rollback is
> clearing the instance cache though, because I'm using my own
> Transaction class.

One-per-thread would also be an alternative to pooling.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org