Re: Design Question - long transaction
Friday, May 4, 2007 10:33 PM
No thoughts on implementation details (your problem is beyond the toy projects I've backed with a DB so far) but could you create or utilize a "temporary" table in the database, and to prevent user collision, have some indicator in your regular table (foreign key pointing to row in the temp table) to indicate an edit is still pending. Use "commits" and the info from the temp table is used to update the real table, the temp table is cleared, and the foreign key is cleared to indicate the data is now saved. Now the question becomes what to do if the user breaks connection without explicitly rolling back their pending changes (connection loss, app crashes, etc.) ... no immediate answer to that. However, thinking another way, this is the sort of problem that source code revision systems solve: lock one developer out of a file while another developer is working on it, but provide a way to steal back the "lock", so to speak. You might see what conceptual patterns and solutions they have already thought of and map them to your database/app.