[SQLObject] collision detection strategy

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

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