Re: [ADodb-general] ActiveRecord optimistic locking usingMySQL timestamps possible?

[Malcolm C. <me...@st...>]

In case anyone is following....

What we wound up doing is writing a new class to extend activeRecord.  It tests for the existence of a field named 'rowstamp' in the 
underlying table and, if it exists, require that it not be modified between initial SELECT and subsequent UPDATE.  We did NOT use 
MySQL timestamp for the datatype of our 'rowstamp' since the time granularity, being to the second, is logically insufficient to the 
need.  Rather, the extended class itself is providing new values for rowstamp, recycling a counter at 255.  This does not completely 
eliminate the exposure to contention, but was sufficient to our expected needs.

Regards,

Malcolm Cook

"Cook, Malcolm" <ME...@St...> wrote in message 
news:BD6...@ex......
> James et al,
>
> What I am hoping to find is an implementation in ADODB's ActiveRecord (http://phplens.com/lens/adodb/docs-active-record.htm) of 
> the Optimistic concurrency control pattern  (http://en.wikipedia.org/wiki/Optimistic_concurrency_control) that can be used with 
> MySQL / InnoDB.
>
> For instance, the documentation provides: ADOdb_Active_Record::Save()
>
>        Saves a record by executing an INSERT or UPDATE SQL statement as appropriate.
>        Returns false on unsuccessful INSERT, true if successsful INSERT.
>        Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was 
> executed.
>
> If ActiveRecord provided a generic implementation of the optimistic record pattern, I might expect to find a return code to 
> indicate that the UPDATE failed to VALIDATE in that the row underlying the record was modified by virtue of a timestamp on the row 
> having changed, and I might expect 'Save' to take an additional parameter, say, 'Overwrite BOOLEAN', which would implement the 
> (very poor man's) "conflict resolution" algorithm of overwriting prior updates even if the timestamp had changed.
>
> Similarly, I might expect methods to direct the ActiveRecord API which column is the timestamp column for a given table, similar 
> as the SetPrimaryKeys may be used to instruct ActiveRcord on which columns constitute the primaryKey.
>
> Any pointers welcome....
>
> Malcolm Cook
> Stowers Institute for Medical Research - Kansas City, Missouri
>
> -----Original Message-----
> From: ado...@li... [mailto:ado...@li...] On Behalf Of James 
> Neethling
> Sent: Monday, May 26, 2008 2:51 AM
> To: ado...@li...
> Subject: Re: [ADodb-general] ActiveRecord optimistic locking using MySQL timestamps possible?
>
>
>
> Cook, Malcolm wrote:
>> Is there a strategy to using mySQL timestamps to achieve optimistic row locking?
>>
>> Basically, in a web app, I would like ActiveRecord to ensure that the
>> row that is getting updated is the same row that was selected for
>> editting by the user, by initially selecting a timestamp column on the
>> table that has been declared as
>>
>> DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP
>>
>> when a (single row) update is done, the rows primary key and the old selected timestamp would be used to select the row to 
>> update. If ROW_COUNT() is not 1, then the row has been changed (or deleted) somehow else.
>>
>> Any approaches out there?
> I think that you have the essence of the optimistic off-line row locking pattern. I'm not sure what your question is about.
>
> Specific implementation?
>
> Alternatives?
>
> Regards,
> James
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> ADodb-general mailing list
> ADo...@li...
> https://lists.sourceforge.net/lists/listinfo/adodb-general
>
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/