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]
