Thanks for that answer, you filled in some of the blanks for the table /
logging solution, but I am now looking at row locking instead of a seperate
table (and then doing things similar to what you outlined).

The main problem is the darn timeout - how long should it be etc? And if I
use row locking and don't unset the locks / use a timeout then the db will
get completely locked up if the user doesn't update I would imagine!

Darn users!!  This script is complicated enough without this!!

Cheers

Ollie

-----Original Message-----
From: Peter Lovatt [mailto:[EMAIL PROTECTED]]
Sent: 01 February 2002 08:10
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a
newbie question)



Hi

Interesting problem! Systems would be so much easier to build if we didn't
have to allow for users :)

Two suggestions, depending on how you want the data dealt with.

A table of rows in use, with a time stamp and an owner. When user1 opens the
record, stamp it with owner and time. If user2 wants to use the record,
check when it was 'locked' and apply a timeout based on how long it takes to
edit. For example if the record was opened 3 mins ago, and the timeout is 5,
the user2 gets a message saying 'Record in use try again in 2 minutes' If it
was opened 6 minutes ago set the owner of the locked record to user2, and
reset the timestamp.

If / when user1 submits, refuse the update, and inform user1, and whatever
handing you need after that.

If no user2 has tried to open the record, then user1 can still submit,
because they still own it, even if there is a timeout.

If you are feeling flash maybe a JavaScript timer that pops up 1 minute
before timeout and warns user1 to save (update record and reload for more
editing)?

Probably more hassle than its worth, but you could also take a snapshot of
the data, when user1 starts, and if more than one user tries to edit the
record, save the updates in a temp table, compare the updated record with
the original snapshot, and do some sort of intelligent amalgamation.

HTH

Peter


> -----Original Message-----
> From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
> Sent: 31 January 2002 23:09
> To: [EMAIL PROTECTED]
> Subject: [PHP-DB] Ensuring users don't overwrite each other (NOT a
> newbie question)
>
>
>
> Hi there, currently writing an e-CRM system for an intranet using PHP on
> Win32 and MS-SQL.  This system needs to be scalable but more importantly
> there will be anything up to 400 users (unlikely, but the max
> amount) using
> the same records (updating information about customers etc) and I
> worry that
> whilst one user has a form open (via one of my PHP scripts) that another
> user could also be making changes to the same record and if they post it
> before the other one they could overwite each others changes.  For info:
> database is normalised to 3NF so that side of things should be okay.
>
> I have thought of a couple of solutions:
>
> Row Locking when a user has a record - and if another user wants
> to use that
> record PHP tells them its in use.  But if the forst user doesn't make any
> changes how will the db know to unlock the row and there might be
> potential
> deadlock issues.  Also I'm not sure of the SQL for row locking
> (do you use a
> SELECT with a ROWLOCK hint?).
>
> Another idea was to have a log or temp table - that would get written into
> when ever some opens a record but this has the same issues as the first
> solution I think.
>
> An another idea is T-SQL and transactions but I'm not sure if that will
> solve the problem (and I've never used T-SQL before - therefore
> I'm not sure
> of its capabilities)
> eg:
> When the script is started by the first user (to bring up the existing
> record) perhaps a transaction is started (if they can persist between
> batches?):
>
> $tranname = "@tran".$id;
> $sqlstr = "TRANSACTION $tranname
>
> SELECT rows from CASES
> WHERE id = $id
> GO
>
> /* maybe find the date / time from a system table sp_something of the last
> time the row was modified?? */
>
> START TRANSACTION $tranname
> GO
> ";
>
> But that probably won't work thinking about it (and looking at the stupid
> senseless code I have written above!!!!) The transcation probably
> need to be
> around the update SQL doesn't it?  And then do a rollback if it finds
> another user has updated lately?  And then reload the data and
> send it back
> to the form for the user to check (then they can update - after
> checking the
> other users data?)
>
> Anybody have a solution /views on this?  Anybody had to fix a similar
> problem?  Or is all this paranoia (will the DB handle this problem on it
> own? - I very much doubt that last comment!)
>
> Any help would be most appreciated, I don't need all of the PHP code just
> the concepts will do (I have been using PHP/MS-SQL for a while) or some
> example T-SQL if you think thats the solution I should go for.
>
> Thanks very much in advance...
>
> Oliver Cronk
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to