Technically you can ask web2py not to close a db connection and store the connection in cache ram so that another request would pick it up. Yet I cannot recommend anybody doing this. It keeps the database locked, at least the records that are being modified will be locked until commit, resulting in other requests being dropped. Moreover there is no guarantee that it will ever commit because the network problems may prevent follow-up request from completing the transactions.
The issue is not whether this can be done. The issue is whether this should be done and my answer is no. Another issue is how to solver Adam's problem. I would suggest queue the tasks using an auxiliary db table and have a background process perform complete the tasks when enough information is available. massimo On Mar 28, 11:20 am, "Fiuk, Adam" <[email protected]> wrote: > Hi Dave, > > Thanks for the answer and hints, I will analyze it deeper later, but to > answer you now - please make sure you know the person before you suggest him > buying a book ;) I am working currently on RDBMS with terrabytes of data, up > to 2-3 thousands concurrent users and I have to live with many different > issues. Including locking for example. I am not allowed to send you design > documents since its confidential, however whenever I keep 'persistent > session' i am sure there is no locking or any other issue - by design. For > example: > - the data that is being updated is secured and no other user can edit it > (by design - application lock, used for other purposes as well) > - the data is inserted only so nobody can see it before commit > - RDBMS use row locking, not table locking > - there is a strict rule that the session is killed if user will not commit > it for 10 mintues > And many more. > Of course - there might be resource or performance issue however it also was > analyzed and even consulted in one particular case with Oracle support to > make sure they do not see any problems here > > Every use case must be analyzed seperately. In many cases - yes, 100% right, > user input should never be allowed in the middle of the session. But it's > not the rule... > > Its something like with the principle: "explicit is better than implicit". > Let me quote someone from this group :) > > "web2py understands that it is a general principle, not a rigid rule to be > applied mindlessly without regard to trade-offs with other sometimes > conflicting principles (such as "don't repeat yourself," "simple is better > than complex," and "there should be one obvious way to do it")." > > I fully agree with the above and I believe its the same with DB transactions > ;) > > Anyway - you have 100% rights to dislike and criticize my design, I respect > it :) But I don't think this is the real issue - so let's concentrate on > Web2py. If you and others don't like the idea of 'persistent transactions' > that means that for this kind of design I don't use Web2py, thats all. I > will stick to Java where the language and framework doesn't force me how I > should design the solution :) > I always prefer tools that obey me - instead of tools that I have to obey > ;) > > But - on the other hand - I don't know if it will be the case, as I said - > for now I am learnign Web2py, I don't know if, where and when I will use it > for real projects. > > Thanks alot for you valuable answer! > Adam > > 2011/3/28 Dave Warnock <[email protected]> > > > > > > > > > Adam, > > > I am quite surprised that the database administrator was happy with you > > using a transaction in that way. > > > It goes against the normal practice that a database should never be stalled > > mid transaction while waiting for user input. > > > If you have a table locking database this will mean the database becomes > > essentially single-user while this business function is being used. > > > Even with row locking there is a significant possibility of being unable to > > commit the tranbsaction due to locking conflicts. > > > It might be ok with a versioning database but even then there are plenty of > > potential solutions that neither lock the database while waiting for a user > > nor use sessions. > > > I suggest you look for a book on SQL database design rather than expect > > Web2py to support this design. > > > If Web2py were to support persistant transactions as you request then I > > suggest it would make it a much harder sell into large dbms environments as > > any dba will have huge objections. > > > > a) There were an analytical application that had to display a table data > > (a few hundreds of rows, 8 columns). > > > Application user had a number of fields that he could change its values - > > and after submitting - he could see changed database data. > > > However the changes must not be committed until the results was approved > > by user - he could play with it number of times until the results were ok, > > than finally - submit it (commit data). In other words - it's kind of "live > > spreadsheet" based on database. > > > Some options: > > i) create the data in a separate table. Commit copies the data from the > > "uncommitted" table to the "committed" table. > > > ii) have a flag column "committed" which is set to false while they are > > experimenting. The commit simply changes the flag. > > > iii) add a transaction number column, in a transaction table keep track of > > the transaction status > > > > b) There were a simple application for a very small factory that was > > assisting in scheduling of production. > > > It was really simple however there were a thousands possible product > > components, one schedule had to contain a few products to be produced. > > > It would be really painful and basically it would make no sense to solve > > it with session - it would be close to storing a database in session. Even > > if DB small it's still better that session for storage ;) > > > Model the schedule in the dbms, give the schedule a status. > > > > Of course both cases could be done with different approach - that our job > > to find a solution even if its seems there is a dead end :) > > > I am not sure our job is to give the dba a heart attack :-) > > > > But the easiest one for me is just using database without commiting > > transaction (to make sure the data integrity is not broken). > > > Using database for some intermediate storage is just a "design pattern" - > > it can be done in other ways but this is also a good solution. > > > Waiting for user input in a database transaction is not a good idea, check > > any dbms tutorial. > > > > Please don't underestimate the issue: even in very simple scenario it can > > be useful: > > > - lest say we have application with tasks and for every task user can add > > comments > > > - user create task and then create comment > > > - comment is on new page > > > - before he submits comment he decides to CANCEL the whole operation.. > > > > I would just rollback data, but without such possibility you need to > > delete the task.. :) Sometimes leaving task without comment is not so nice - > > it was created by mistake for wrong project for example... Stupid example > > but very real use case for 'rollback'. > > > Sorry but this is not a need for a persistant transaction. Don't put the > > task into the dbms until it is ready. > > > > But I really believe that even if Web2py does not support it - it should > > be quite easy to make such improvement. But its just intuition - maybe I > > cannot see some issue... > > > My 2cents. Please do not add this to Web2py. > > > Dave
