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

[James N. <Jne...@we...>]

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

We use postgres which has much higher timestamp granularity. Interesting 
to note that the Mysql implementation makes this so difficult.

Just to clarify:
UserA selects a piece of data to manipulate - including the timestamp + 
counter.
UserB selects the same piece of data - including the timestamp + counter.
Both users manipulate the data.

UserA hits submit 1st and their query correctly updates the database, 
including the timestamp

UserB hits submit and their query fails as the timestamp + counter that 
they submitted != timestamp + counter retrieved when the webapp  
'find()'s the ActiveRecord to update. The ActiveRecord returned by 
'find()' would have the timestamp+counter written in by UserA's submit.




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