Re: [SQLObject] collision detection strategy

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

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