>> 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/[EMAIL PROTECTED]
