Re: [SQLObject] collision detection strategy

[Bud P. B. <bu...@si...>]

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.  

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...

--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)
\-----------------------------------------------------------------