Re: [SQLObject] collision detection strategy

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

On Wed, 2003-05-21 at 00:22, Bud P. Bruegger wrote:
> Thanks for the feedback!
> 
> Actually, in my ongoing implementation, I have used a GUID generator that I
> found in the Python Cookbook.  It uses time (up to the second???) but
> also mixes in the IP of the machine and a random number.  That way,
> problems with lack of time synch across machines, second-only time,
> and similar can be avoided. 

Sounds like a reasonable solution.

You'd have to be really unlucky to get problems now - 2 users, both
using a web site from behind the same masquerading firewall and are both
given the same random number ;-).

>  
> 
> Do you see any need to take out a lock for the update query?  I suppse
> it is highly unlikely (in my app, at least), that two updates on the
> same row happen at the same time...

Can you explain what you mean by "take out a lock"?

> 
> --b
> 
> On 21 May 2003 00:06:58 +0100
> Matt Goodall <ma...@po...> wrote:
> 
> > I've used this technique (optimistic locking) before and it works very
> > well. It's the only way for web work.
> > 
> > The only thing I would say is that a timestamp is not the ideal
> > indicator of change. It might be unlikely, but it is possible for a
> > database record to be read and updated without the timestamp's value
> > changing. In fact, some database timestamps (Oracle 8i, IIRC) only have
> > a 1 second accuracy which increases the chance of missed collisions
> > considerably.
> > 
> > An incrementing integer version number is possibly a better choice. The
> > only problem with this is when the number gets to big for the column -
> > PostgreSQL, for instance, considers it an error.
> > 
> > Cheers, Matt
> > 
> > On Tue, 2003-05-20 at 23:29, Bud P. Bruegger wrote:
> > > Had an idea for collision detection (see
> > > http://www.agiledata.org/essays/concurrencyControl.html) and it seems
> > > quite a bit simpler to what is described in the above essay.  Can
> > > someone comment on whether the following is sound?
> > > 
> > > * The shadow data of an object includes a timestamp (or better GUID)
> > > 
> > > * every INSERT and UPDATE query changes this timestamp.
> > > 
> > > * The (in memory) python object keeps the timestamp information from
> > >   the last select, insert, or update query.
> > > 
> > > * When the state of an object is written to the dbms (UPDATE query),
> > >   collision detection is necessary to make sure noone else modified
> > >   the object since...
> > > 
> > > * this can be done by the following query:
> > >   UPDATE table SET 
> > >      col1=val1, 
> > >      ..., 
> > >      colN = valN, 
> > >      timeStamp = <newTimeStamp> 
> > >   WHERE id=<id> 
> > >   AND   timeStamp = <oldTimeStamp>
> > > 
> > > This works only if none else has modified the record (since otherwise,
> > > timeStamp is not equal <oldTimeStamp>).
> > > 
> > > With the DB-API, it seems the way to determine whether the update was
> > > successful or a collision prevented the database from being changed is
> > > to look at the rowcount attribute of the cursor:  1 means success, 0
> > > means collision.  
> > > 
> > > I checked this with psycopg and it seems to work.  
> > > 
> > > I suppose it would also be sound to wrap this in a transaction...
> > > 
> > > Does anyone see any problems with this approach?
> > > 
> > > many thanks
> > > --b
> > > 
> > >      
> > > 
> > > /-----------------------------------------------------------------
> > > | Bud P. Bruegger, Ph.D. 
> > > | Sistema (www.sistema.it)
> > > | Via U. Bassi, 54
> > > | 58100 Grosseto, Italy
> > > | +39-0564-411682 (voice and fax)
> > > \-----------------------------------------------------------------
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.net email is sponsored by: ObjectStore.
> > > If flattening out C++ or Java code to make your application fit in a
> > > relational database is painful, don't do it! Check out ObjectStore.
> > > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > > _______________________________________________
> > > sqlobject-discuss mailing list
> > > sql...@li...
> > > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> > 
> > 
> > -------------------------------------------------------
> > This SF.net email is sponsored by: ObjectStore.
> > If flattening out C++ or Java code to make your application fit in a
> > relational database is painful, don't do it! Check out ObjectStore.
> > Now part of Progress Software. http://www.objectstore.net/sourceforge
> > _______________________________________________
> > sqlobject-discuss mailing list
> > sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> > 
> 
> 
> /-----------------------------------------------------------------
> | Bud P. Bruegger, Ph.D. 
> | Sistema (www.sistema.it)
> | Via U. Bassi, 54
> | 58100 Grosseto, Italy
> | +39-0564-411682 (voice and fax)
> \-----------------------------------------------------------------