You are viewing a plain text version of this content. The canonical link for it is here.
Posted to embperl@perl.apache.org by Robert <ro...@robert.cz> on 2001/05/02 14:13:41 UTC
Lost update question
Hi,
I need to solve the "lost update problem" (when two users are editing
the same database record, one's changes are lost when they both save
it). I'm thinking about putting original version of the record in the
hidden field (serialied by Storable) and upon saving do SELECT ... FOR
UPDATE, compare freshly SELECTed data with original data from the hidden
field and UPDATE&COMMIT if it corresponds or ROLLBACK otherwise. How
reasonable is it?
I'd expect a lot of people have the same problem, what do you do? Can
you think of any way how to make this generaly usable, maybe as some
general Perl modul? Or perhaps integrate it into Recordset? I happen to
use Recorset in this project: Gerald, how hard would it be to extend
Recordset with this feature?
- Robert
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by ___cliff rayman___ <cl...@genwax.com>.
Robert wrote:
> Hi,
>
> I need to solve the "lost update problem" (when two users are editing
> the same database record, one's changes are lost when they both save
> it). I'm thinking about putting original version of the record in the
> hidden field (serialied by Storable) and upon saving do SELECT ... FOR
instead of serializing, try a message digest such as MD5 on the same data
u would have serialized. then just pass the MD5 as a hidden field and do the
comparison. i like Gerald's idea better, since many databases offer an
increment field which auto increments at each update. this would be much
faster than a MD5 since you would not have to do two selects and two
MD5 computations for each update.
--
___cliff rayman___cliff@genwax.com___http://www.genwax.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Gerald Richter <ri...@ecos.de>.
>
> After a tiny bit of thinking maybe simple field
>
> last_update TIMESTAMP
>
> would be even simpler. It could even be automatically updated from a
> DB trigger, so I'd only need to modify UPDATEs as you proposed
>
Yes, this will work too, but it's database dependend.
> $set -> Update (\%params, { id => $fdat{id}, last_update =>
> $fdat{last_update} })
>
> Does Recordset's Update method return how many rows where updated? How
> will I know it failed?
>
Update should return the number of rows updated, (it's the return value you
get when you call $dbh -> do ('UPDATE .... ')), but the exact value maybe
database and DBD dependend.
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Robert <ro...@robert.cz>.
Gerald Richter wrote:
>
> >
> > This definitely saves the overhead of passing around another version
> > of data, but requires a modification of all table structures. I don't
> > see how it can be done with a Recordset filter, could you give me a
> > clue?
> >
>
> $db -> TableAttr ($tabname, '!Filter', { '\\updatecnt' => [ sub
> 'updatecnt_seq.nextVal' }, undef, DBIx::Recordset::rqUPDATE ] }) ;
>
After a tiny bit of thinking maybe simple field
last_update TIMESTAMP
would be even simpler. It could even be automatically updated from a
DB trigger, so I'd only need to modify UPDATEs as you proposed
$set -> Update (\%params, { id => $fdat{id}, last_update =>
$fdat{last_update} })
Does Recordset's Update method return how many rows where updated? How
will I know it failed?
- Robert
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Robert <ro...@robert.cz>.
Gerald Richter wrote:
>
> $db -> TableAttr ($tabname, '!Filter', { '\\updatecnt' => [ sub
> 'updatecnt_seq.nextVal' }, undef, DBIx::Recordset::rqUPDATE ] }) ;
>
> This assumes that you have a database that has sequences (Postgres, Oracle)
> and you take the next number out of the sequence. If your db doesn't have a
> sequence you can return a number from the DBIx::Recordset sequnce classes.
> The above filter will always be executed on every update so it makes sure
> you always get a new number.
>
> So now you select your record, pass the value of the updatecnt field to the
> next page. When it comes to an Update you simply say
>
> UPDATE ..... WHERE id = $id and updatecnt = $passed_updatecnt ;
>
> If the the updatecnt is still the same, it works, if not no record will be
> updated and you know somebodyelse has done so inbetween...
>
> What you think about this ?
Aha. I'll have to think this over, but it seems to be the simplest way
to go. Thanks, Gerald. Cliff, I like your MD5 idea esp. in cases where I
don't want to mess with the DB structure, but right now I'm going to
change the DB anyway and Postgres has decent support for sequences - and
I'm already using Recordset anyway...
- Robert
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Gerald Richter <ri...@ecos.de>.
>
> This definitely saves the overhead of passing around another version
> of data, but requires a modification of all table structures. I don't
> see how it can be done with a Recordset filter, could you give me a
> clue?
>
$db -> TableAttr ($tabname, '!Filter', { '\\updatecnt' => [ sub
'updatecnt_seq.nextVal' }, undef, DBIx::Recordset::rqUPDATE ] }) ;
This assumes that you have a database that has sequences (Postgres, Oracle)
and you take the next number out of the sequence. If your db doesn't have a
sequence you can return a number from the DBIx::Recordset sequnce classes.
The above filter will always be executed on every update so it makes sure
you always get a new number.
So now you select your record, pass the value of the updatecnt field to the
next page. When it comes to an Update you simply say
UPDATE ..... WHERE id = $id and updatecnt = $passed_updatecnt ;
If the the updatecnt is still the same, it works, if not no record will be
updated and you know somebodyelse has done so inbetween...
What you think about this ?
Gerald
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Robert <ro...@robert.cz>.
Gerald Richter wrote:
>
> > I need to solve the "lost update problem" (when two users are editing
> > the same database record, one's changes are lost when they both save
> > it). I'm thinking about putting original version of the record in the
> > hidden field (serialied by Storable) and upon saving do SELECT ... FOR
> > UPDATE, compare freshly SELECTed data with original data from the hidden
> > field and UPDATE&COMMIT if it corresponds or ROLLBACK otherwise. How
> > reasonable is it?
>
> Another idea would be to have a sequence field, which is automaticly
> incremented for every update. (You can do this without modifying your code
> with a DBIx::Recordset filter) and only passing this sequence number around
> and comparing it before updating the record.
This definitely saves the overhead of passing around another version
of data, but requires a modification of all table structures. I don't
see how it can be done with a Recordset filter, could you give me a
clue?
> > I'd expect a lot of people have the same problem, what do you do? Can
> > you think of any way how to make this generaly usable, maybe as some
> > general Perl modul? Or perhaps integrate it into Recordset? I happen to
> > use Recorset in this project: Gerald, how hard would it be to extend
> > Recordset with this feature?
> >
>
> Shouldn't be to hard to get this into Recordset, maybe it's enough to just
> define some filters
I guess one would add !Version parameter to tell Recordset the name of
the version field... what's next? Reread its value before update and die
if it differs? One still needs to add SELECT FOR UPDATE or lock the
record some other way.
- Robert
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: Lost update question
Posted by Gerald Richter <ri...@ecos.de>.
Hi,
>
> I need to solve the "lost update problem" (when two users are editing
> the same database record, one's changes are lost when they both save
> it). I'm thinking about putting original version of the record in the
> hidden field (serialied by Storable) and upon saving do SELECT ... FOR
> UPDATE, compare freshly SELECTed data with original data from the hidden
> field and UPDATE&COMMIT if it corresponds or ROLLBACK otherwise. How
> reasonable is it?
Another idea would be to have a sequence field, which is automaticly
incremented for every update. (You can do this without modifying your code
with a DBIx::Recordset filter) and only passing this sequence number around
and comparing it before updating the record.
> I'd expect a lot of people have the same problem, what do you do? Can
> you think of any way how to make this generaly usable, maybe as some
> general Perl modul? Or perhaps integrate it into Recordset? I happen to
> use Recorset in this project: Gerald, how hard would it be to extend
> Recordset with this feature?
>
Shouldn't be to hard to get this into Recordset, maybe it's enough to just
define some filters
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org