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