I do not disagree with your general approach to the problem area. But I would not want to solve this use case with transactions as I think the overheads too great and I (obviously) think the suggested solution is a fix at negligible cost. Re your specific points (second point first):
"one could argue that the desired/correct outcome should be to keep both changes as they reference unrelated columns." You're probably right but it is impossible to know the desired outcome and therefore implement it. The best you can do is prevent the ambiguity which is what I am suggesting. 1) " Imagine 50 people trying to make an order of a product that has the 'items in stock' column...." The logic here is not "has the product changed?" it is "has the stock level got to zero before my order is processed?" So user A sees the stock level as sufficient and submits order. User B has already got in and bought the last widget. The order logic must check to determine if stock is left. It could use a transaction lock and select/check/update or it could update "where stock >= order amount". Most developers would recognise this scenario and cater for the condition. The simple situation of updating an entity's more boring attributes does not, for most people, get the same attention. If it did then I think the simple change proposed would be one of the first features provided in a DAL (after the auto 'id' column which I think is great). Bill On Oct 15, 5:07 pm, achipa <[EMAIL PROTECTED]> wrote: > The trouble is, if you don't go the way of transactions, every > solution will be a solution to a particular use scenario with a lot of > hidden gotcha's and is really a question of interpretation whether > it's what you want or not. Imagine 50 people trying to make an order > of a product that has the 'items in stock' column. Every time someone > actually makes an order, all the remaining users that had that item > suddenly have their checkout process invalidated because of mismatched > row versions. Oops. Also, in your example, one could argue that the > desired/correct outcome should be to keep both changes as they > reference unrelated columns. That's why I say it's more application > logic and am not sure what web2py can do about it as there are a > thousand scenarios. > > On Oct 15, 5:46 pm, billf <[EMAIL PROTECTED]> wrote: > > > achipa/vihang: I think you are both looking at more complex scenarios > > than I have in mind. I am not talking about multi-page transactions > > (in the sense of one user's transaction spanning several pages). I > > agree that would be the responsibility of the application. > > > The following, which I posted in another thread (see 'delete > > vulnerability?'), demonstrates the problem case. > > > User A selects record with id=99 that has the following columns/ > > values: "name" = "Massimo", "town" = "Chicago". > > > User B selects the same record, id=99, "name" = "Massimo", "town" = > > "Chicago". > > User B updates the name to "Massimo di Pierro", leaves "town" = > > "Chicago" and submits the form. > > The database is updated. > > > User A (on the form displayed prior to user B's action) updates the > > "town" to "New York" and submits the form. > > As the id is still 99, the database will be updated and the version on > > the database will be: > > id=99, name="Massimo", town="New York" > > i.e. unknown to both users A and B, user B's action has been silently > > undone. > > > To simply prevent the second update there is no need for additional > > complex transactions - just the addition of the proposed 'where' will > > prevent it. There is no transaction involved (although the update > > could, I think, be part of a transaction without causing a problem). > > > Probably an underestimate but I reckon: > > 2 lines in SQLTable to conditionally add the column to a table. > > 10 lines in SQLFORM to save the column in self, conditionally show on > > form (like showid), check whether it has been tampered with (not > > really necessary - any changes to id and new columns could just be > > ignored as 'illegal') and include condition in update and delete > > statements. > > > :-) Bill > > > On Oct 15, 2:48 pm, achipa <[EMAIL PROTECTED]> wrote: > > > > Upon further thought, wouldn't it be better to implement his in a > > > playback fashion ? I mean, you would handle the first pages like an > > > intentionally failed/rolled back transaction. The further the user > > > goes in the process, the further you get with the queries (and you > > > will notice any changes made by other users as then would influence > > > the selects). That way you have no locks, and only the final input of > > > the user does a commit, which is consistent, and requires no > > > additional columns. Am I right if I say this is basically a special > > > case of a multi-page form consistency problem ? > > > > On Oct 15, 3:22 pm, mdipierro <[EMAIL PROTECTED]> wrote: > > > > > I do not think belongs to web2py but it could be a next T2 feature. > > > > > Massimo > > > > > On Oct 15, 4:24 am, billf <[EMAIL PROTECTED]> wrote: > > > > > > I would like to request the ability to include a column in every table > > > > > to be used to avoid overlapping updates, i.e. where a record has been > > > > > updated by user B between user A selecting a record and updating/ > > > > > deleting it. When this occurs user B's updates are invisibly undone. > > > > > > I know that this can be hand-coded but I believe it would be a useful > > > > > and relatively minor addition to the core - as an option on > > > > > define_table? version=False being the default? > > > > > > The column could be either a version_number (simpler) or a > > > > > last_action_timestamp (perhaps more useful but more complicated?) and > > > > > would be used to prevent a record being updated/deleted if it had been > > > > > changed since selected. This could be achieved in SQLFORM.accepts() > > > > > by conditionally adding " and self.table.version==version" or similar > > > > > to the update and delete statements. The version (or timestamp) would > > > > > be handled in much the same way as 'id'. > > > > > > I accept that the probability of overlapping updates is small but in a > > > > > business situation it is important for web2py to be rigorous as > > > > > possible and this approach has negligible overhead. > > > > > > Do people think this is desirable, do-able or dumb? > > > > > > Bill --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
