Re: [SQLObject] collision detection strategy

[Matt G. <ma...@po...>]

On Wed, 2003-05-21 at 00:41, Ian Bicking wrote:
> On Tue, 2003-05-20 at 17:57, Luke Opperman wrote:
> > The other thought I'm having right now is that SQLObject seems to simplify some
> > of this if you're using cached objects, because now there is only one possible
> > object, so it's all on the python side to deal with locking as the developer
> > sees fit. but I haven't entirely thought this through.
> 
> Yes, in the case where there's one multi-threaded process that's in
> total control of the database, SQLObject makes it possible to do safe
> locking on the Python side.  Programming in Webware (i.e., a
> multi-threaded application server), this covers a large portion of my
> usage.
> 
> I had some various locks in there and thought of more, but now I feel
> like the programmer needs to be explicit about their locking, because
> there's no automated way to do it.

Unless the check is done during the update then there is no way to
guarantee that the optimistic lock is honoured. Unless, that is, the
object's row is locked using "select ... for update" (or something
similar) just before the update.

Of course, having a locking attribute with a small chance of error is
better than no locking at all.

> 
> For instance, with the timestamp, what is the object's starting point? 
> Let's say you do:
> 
> obj = Something(someID) # op A
> x = obj.a               # op B
> y = obj.a               # op C
> obj.a = x + 10          # op D
> 
> What is the concurrency expectation?  I.e., when is does optimistic
> locking fail?  All of A, B, and C are potential times when the timestamp
> could be fetched from the database.

I may be missing something obvious but why would reading the timestamp
ever make an opt lock fail? The only time it matters is when the
database is updated isn't it?

>   With caching and multithreading,
> it's even more complicated (though I think if you want to do this
> locking you'll have to either not use threading, or use per-thread
> instances).
> 
> So if we have to be explicit about locking, how do we express that?  How
> do you say what you are locking?  Columns, rows, tables?  All are valid
> options, and none particularly more difficult than the others.

Agh! Not table locking, please! The MySQL manual is the only place that
sort of talk is acceptable ;-).

I would imagine optimistic locking would take place at the object level
which generally means row level in the database.

- Matt