Thank you. That sounds great but I have a few concerns. Within the overall user edit session (I suppose this would be one big transaction held for potentially several hours if I use only a single database and row versioning), some series of changes must be handled as their own transaction -- a nested transaction. How would I do this if the entire edit session is wrapped in a transaction? I suppose I could use savepoints. However, although I see that savepoints are supported in InnoDB 4.0.14 and 4.1.1, I did not see anything about savepoints being available in the MySQL implementation of InnoDB. Are they? I'm a little concerned about the index locking. I suppose I could live with users not being able to change fields in a record another user has changed even if the other user has not changed those specific fields; that could be dangerous anyway. But I am concerned about having insertions blocked around a record that has been changed. The literature recommends committing transactions often but, in my application, using row level locking to control concurrent multi-user access over the entire potentially multi-hour edit session seems to contradict this advice. Am I misapplying this caution from the InnoDB folks? Please don't take these questions negatively. Being able to use this rather than what we have already done would be great. I just don't want to create more problems than I solve by misapplying the technology. We had originally considered using it instead of creating separate edit databases but dismissed it because of the above concerns. I would be very happy to be wrong. Thanks - John
On Tue, 2003-11-18 at 16:10, Jon Frisby wrote: > Have you considered looking at InnoDB's row versioning? The primary > "drawback" is that once a row has been modified by a user, that user has > an exclusive write-lock on the row, so another user cannot modify the > same row -- however the user can READ the row, and will see the old > version of the row until the new one is committed. This exclusion > prevents the synchronization problems you're talking about, allows users > to see their own consistent view of the entire policy set, and makes > rolling back changes easy (issue a ROLLBACK). Just be sure to set the > connection timeout value very high to avoid unexpected dropped > connections (I think it defaults to 8 hours). > > -JF > > > -----Original Message----- > > From: John A. Sullivan III [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 18, 2003 12:54 PM > > To: Brian Reichert; [EMAIL PROTECTED] > > Subject: Detecting data collisions (was Re: Preventing data > > collisions) > > > > > > >> How do I prevent data collision in the following scenario: > > >> User A and User B are examining the same data (say a > > customer's >name > > and address). User A modifies something (say the customer's >phone > > number) and saves the transaction. User B modifies something > > >different > > at the same time. How can I prevent User B's modification >from being > > saved? > > > > > >Under MySQL, you can lock the a table during sensitive transactions. > > >If you're using InnoDB tables, you can use row locking. > > > > May I ask for some input on taking this a step further. We are > > developing a complex network security management application > > - something > > akin to Solsoft, SmartPipes, or Checkpoint's Provider1 only > > with a more > > highly abstracted policy server > > (http://iscs.sourceforge.net). We need > > to ensure that > > multiple users distributed anywhere around the world can > > make extensive changes to the database and evaluate the > > security impact > > of those changes before committing the changes. These edit > > sessions can > > last from several minutes to hours and all the changes must > > be evaluated > > together. Thus it is not practical to lock the database for > > hours at a > > time. I'm not sure that the solution I have implemented is the best > > approach to this problem as I am truly a newbie and would > > like any idea > > on how to do this better. > > In our current design, when a user starts the > > application, we make a > > copy of the production database (called the edit database) > > and the user > > makes all changes to their own personal edit database. All of the SQL > > statements that alter the database are recorded in a StringList. We > > make sure that every field that has been changed is part of the where > > clause. > > When the user is satisfied with their changes and > > requests a commit, we > > begin a transaction on the production database and execute > > all the saved > > SQL statements. If there is either an error (e.g., a > > duplicate key) or > > zero items changed (implying that we did not match on a where > > statement > > that describes a record we thought existed) on any statement, > > we assume > > that another user made a conflicting change while this user was making > > their edits, rollback the transaction and throw away the edit session. > > Is there a better way to reconcile such large, time intensive, > > multi-user database edits? Thanks - John > > > > -- > > John A. Sullivan III > > Chief Technology Officer > > Nexus Management > > +1 207-985-7880 > > [EMAIL PROTECTED] > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > > -- John A. Sullivan III Chief Technology Officer Nexus Management +1 207-985-7880 [EMAIL PROTECTED] --- If you are interested in helping to develop a GPL enterprise class VPN/Firewall/Security device management console, please visit http://iscs.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]