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
