RE: Detecting data collisions (was Re: Preventing data collisions)

[John A. Sullivan III]

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 w

Detecting data collisions (was Re: Preventing data collisions)

[John A. Sullivan III]

>> 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]

Re: Preventing data collisions

[Brian Reichert]

On Mon, Nov 17, 2003 at 11:06:39PM -0600, David Dice wrote:
> I'm a newbie to MySQL so if this is a common question, forgive me, but I can't find 
> an answer.
> 
> 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.

  

> This is a database I'm converting from Foxpro, and there I would have just told User 
> B that User A had the data open, and prevented them from editing it.  I'm sure 
> there's a better way.  Thanks for your advice.
> 
> David Dice

-- 
Brian 'you Bastard' Reichert<[EMAIL PROTECTED]>
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA BSD admin/developer at large

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]