This is a good point. I do not think this should be dealt with at the DB level because, if I understand it is an app workflow problem. If I understand....
For every user there are two HTTP requests (one to generate the update form, one to submit the form) and each is executed in its own transaction. So the only problem is when the data changes in DB for the record in question in between the two requests. This cannot be solved at the DB level because you do not want to lock the record in one request and unlock in the second request since the second may never arrive (user reload page, click back button, connection is dropped, etc.). This much be resolved by an explicit check that the record content in between requests has not changed. I have once posted an example of how to do it with web2py but now I cannot find it. Anyway, this can be done easily at the app level by adding a timestamp to the record and checking onvalidation that the record timestamp did not change in between the two requests. This should not be default web2py behaviors since the check depends on the model and, moreover, it may be slow and un-necessary in the general case. Like we have onaccept=crud.archive, we could have a onvalidate=curd.unmodified to provide a standard API to perform this check. I will put it in my todo list. Massimo On Apr 4, 11:59 pm, Iceberg <iceb...@21cn.com> wrote: > Yarko, you suggest using a full-featured db instead of sqlite. But > sqlite's internal reader/writer lock is one thing, the "overwrite" > problem is another. No matter what kind of db we are using, we still > need to deal with the "overwrite" problem. > > Scenario: > 9:00am clerk Adam read the balance of a client's account, it is $100 > 9:01am clerk Bob read the balance of same client, it is $100 > 9:02am clerk Adam plans to deduct $10 from the account, and updating > the new balance as $90 > 9:03am NOT knowing what Adam had done, Bob plans to deduct $20 from > the account, and updating the new balance as $80 > > There is no physical concurrency here. But we need some measure to > refuse Bob's last attempt. There is this kind of measures, such as > optimistic lock mechanism in the app level. What I want to discuss is > that, do we need implement optimistic lock in our every web2py app, or > can/need we do something in DAL level, such as PyDbLite did by the > __version field, to benefit all web2py app? > > Ideas? Thanks. > iceberg > > On Apr3, 8:18pm, Iceberg <iceb...@21cn.com> wrote: > > > Yarko, you also raise a very good point. And this makes me feel > > nervous, because all my previous web app are vulnerable to this > > "overwrite" issue. :-/ > > > Besides the "locking" plugin, which is optional and not aware by > > everyone, do you think we can add some built-in, anti-overwrite > > protection inside DAL? For example, if the table contains a field > > named "__version__", then DAL is responsible for automatically perform > > version check before every update(). The idea is borrowed from > > PyDbLite (http://pypi.python.org/pypi/PyDbLite/2.3). And perhaps DAL > > can do more, by raise exception when concurrent modification is > > detected during crud.update(). > > > Quoted fromhttp://www.pydblite.net/en/PyDbLite.html > > > - "another internal field called __version__ is also managed by the > > database engine. It is a integer which is set to 0 when the record is > > created, then incremented by 1 each time the record is updated. This > > is used to detect concurrency control, for instance in a web > > application where 2 users select the same record and want to update it > > at the same time" > > > Regards, > > Iceberg > > > On Apr2, 10:44pm, Yarko Tymciurak <resultsinsoftw...@gmail.com> wrote: > > > > The first thing I thought of when I read your post was > > > this:http://web2py.com/plugins/default/locking > > > > and I wondered how you implemented your solution. > > > > I could imagine, if two users open a form, and both submit form.id 2, > > > one will "overwrite" the other's. > > > > (hope this is helpful) > > > Regards, > > > - Yarko > > > > On Apr 2, 5:00 am, Sven <svenstrin...@gmail.com> wrote: > > > > > Hi, > > > > > I programmed a psychological experiment in flash and store answers/ > > > > results in a sqlite database using web2py (behind nginx server + > > > > fastcgi) and pyamf. Everything seems to be all right when I test it, > > > > but with as few as 2 simultaneous users I (sometimes) run into > > > > trouble. A try with seven simultaneous users resulted in only 2 > > > > succesfully stored experimental data.sets > > > > I find it very hard to debug, since there are no tickets in web2py. > > > > Flash does sometimes produce a remoting error in that situation, but > > > > without too much information. > > > > > I seems to me there is some kind of concurrency problem, but I assumed > > > > web2py/sqlite would take care of that for me. Or should I explicitly > > > > deal with this? Explicitly commit, check if inserts and updates were > > > > successful and if not try again? > > > > > Any suggestions, comments ideas would be really appreciated. > > > > > Thanks. > > > > > Sven -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web...@googlegroups.com. To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
