Re: [SQLObject] Expiring Cache

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

On Monday, October 27, 2003, at 03:35 PM, Sidnei da Silva wrote:
> On Mon, Oct 27, 2003 at 03:16:51PM -0600, Ian Bicking wrote:
> | I think there's maybe some confusion about the different objects.
> |
> | First, caching happens per-connection.  Which means each transaction
> | has its own cache, if you are using transactions.  If you create an
> | object by a certain ID in a transaction, any attempt to get that 
> object
> | by that ID from the same transaction will give you back the same 
> object
> | you got before, guaranteed.  But in a different transaction you will
> | get a different object, since the two transactions are isolated.
>
> Ok, I think I'm starting to understand what fits where now. I think
> that the problem is that I was using the same connection for different
> transactions here.

The terminology is confusing, I have to change it sometime.  A 
DBConnection can be used directly.  Or you can create a transaction 
from the DBConnection.  The DBConnection still controls that 
transaction, but the transaction takes the place of a connection for 
the SQLObject instances.  "Connection" is a confusing word -- each 
transaction is a connection.  Each transaction is also incidentally 
tied to its own database connection, but they all share one 
DBConnection (subclass) instance.  This makes it sound complicated, but 
it's just the poor terminology.

> | It just occurred to me there's some sloppiness when it comes to
> | committing or rolling back a transaction.  The objects stick around,
> | but once you've finalized the transaction I don't know what use they
> | are.  Those instances should actually be invalid.  Unless you assume
> | that by committing or rolling back a transaction, you are implicitly
> | restarting the transaction and can keep using it.  Anyway, I don't
> | think this applies to this circumstance, but it should be worked out.
>
> If by 'those instances' you mean the instances involved in the current
> transaction, I think that there are two cases, assuming that
> both commit and rollback start a new transaction implicitly, which I
> think it's a good thing (albeit a bit magical):
>
> 1. The transaction is rolled back, the objects are in invalid state
> and should be purged from cache

This basically happens.  Instances aren't purged from the 
connection-level cache, but column values are purged from each 
instances' personal cache (that's what .expire() does).

> 2. Transaction is commited, the objects are valid and the cache is
> valid.

Yes.

> | So -- attributes changes immediately update the database.  They also
> | update the internal cache of that column.  Other instances that are 
> in
> | other transactions are not effected.  They aren't effected when you
> | commit the transaction.  It seems like at some point they should be
> | effected (expired, perhaps), but I'm not sure where.
>
> Ok, so this is what I had in mind, and where it starts getting
> tricky. A situation like this would cause a conflict error in ZODB:
>
> - transaction 1 starts, with object x
> - transaction 2 starts, with object x
> - transaction 1 changes object x and commits
> - transaction 2 changes object x and tries to commit
> - before commiting the state of object x from transaction 2 is
>   tested against the object commited and its detected that the
>   state changed during transaction 2
> - a ConflictError is raised
>
> 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?).

I'm not sure how bit a change this would be -- probably not that bad.

> <snip dubious cache interpretation :) />
>
> | SQLObject should already handle this, at least for an individual
> | instance.  It knows when you change an attribute and updates its own
> | cache.
>
> Ok, so based on this, and on the fact that there is a cache per
> connection, and another for transaction, is it safe to assume that as
> long as I have *exactly* one and *the same* connection being used
> during a single transaction, creating an SQLObject instance multiple
> times should have minimal effect on performance? I feel like  I've been
> doing a lot of confusion with connections x transactions x instances.

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.

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