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
