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

$tranname = "@tran".$id;
$sqlstr = "TRANSACTION $tranname

SELECT rows from CASES
WHERE id = $id

/* maybe find the date / time from a system table sp_something of the last
time the row was modified?? */


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]

Reply via email to