I was thinking of more complex queries. Your method should work fine.
On Oct 6, 7:25 am, billf <[EMAIL PROTECTED]> wrote:
> I don't know postgresql plus I don't see how the "while True..."
> action would work.
>
> How would it determine that a second update was an exception if the
> action in "### perform your op" was just an update? I guess the
> alternative is to start a transaction, re-select the record, check it
> is the same as the record being updated by the user and if so update
> it then commit.
>
> As I see it, the benefit of the "where version=my_record.version"
> approach (optimistic locking) is that there is no need to re-select
> the record and check it, no actual locking at all hence there is no
> performance issue, no worry about a lock not being released and no
> database back-end dependency. The "optimism" is that the chance of a
> failure is minimal but the "where..." provides a failsafe.
>
> It would seem to be ideal as a feature built into the core DAL,
> perhaps as an option :-)
>
> I'm guessing that to implement this myself I need to do something like
> the following:
>
> db.define_table('user',
> SQLField('version','integer',default=1),
> SQLField('name'),
> SQLField('email'))
>
> if form.accepts(request.vars,session):
> db((db.user.id==form.vars.id) &
> (db.user.version==form.vars.version)).update(version=form.vars.version
> +1)
>
> Just tried it and it seems to work fine! I'm getting very +ve about
> web2py!
>
> On Oct 6, 7:50 am, mdipierro <[EMAIL PROTECTED]> wrote:
>
> > Good points.
>
> > web2py wraps each controller action in one transaction. The type of
> > transaction depends on the configuration of the database back-end.
>
> > If you use postgresql for example you can do:
>
> > db.executesql("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
>
> > (optimistic locking)
> > and in you actions you can also do
>
> > while True:
> > try:
> > ### perform you op
> > db.commit()
> > break
> > except OperationalError:
> > time.sleep(random.random()*1000) ### optional
> > db.rollback()
>
> > This should be equivalent to optimistic lock with retry.
> > There are many options. It depends on the database-backend. With
> > postgresql you have a lot of flexibility.
>
> > Massimo
>
> > On Oct 6, 12:48 am, billf <[EMAIL PROTECTED]> wrote:
>
> > > Hi - I am v.new to web2py (and python) and I am trying to find if
> > > there is any reason I should not try it as an alternative to Java
> > > servlets. I think I appreciate the savings I could make but I don't
> > > want to invest a lot of time and then find an unacceptable
> > > constraint. My apps are commercial, need to be robust but are not
> > > high volume enough for things like load balancing, etc.
>
> > > My concern at the moment is how to prevent updates being lost if 2
> > > users are simultaneously updating the same row. This may seem
> > > unlikely but I have always included a version number or
> > > last_action_timestamp in every table to allow update table set....
> > > where id=my_record.id and version=my_record.version.
>
> > > In my jargon this is "optimistic locking".
>
> > > How should I implement this in web2py?
>
> > > web2py always includes an id column (which is what I would do).
> > > Depending on the answer to the above question, would it be a good idea
> > > to also include a version number or last action timestamp and
> > > automatically check that.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
