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]
