Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Yes indeed I agree thats what I was trying to say (row lock between 2nd select and update during which time a SQL comparision is made (as if its 2 queries (and PHP does the comparision in between) MSSQL will release the locks i believe). Thanks for your help and everyone else's, now I just need to figure out ROLLBACK! Cheers Ollie "Dl Neil" <[EMAIL PROTECTED]> wrote in message news:<02e301c1ac0c$f8116880$c200a8c0@jrbrown>... > Ollie, > > First off, apologies, this is the first mention of MS-SQL (that I have noted). I am not 'up' on the latest > versions/facilities offered. > > > Ah hang on just thought of a flaw in that - in between the second select, > > compare and update is enough time for another user to slip in - so I will > > still need some kind of logging. > > =Not too much so. If the entire table is locked (between the second SELECT and the UPDATE), how long will it be > locked for? - and is that a major issue within your operating parameters? If it is locked, then there is 'no' > time... > > > But it brings me nearer the solution! I think the solution is a combination > > of the 2 - start a transaction do a second select but do the comparision in > > SQL, then if all ok then start another transaction do the update do the > > update(s) of the joined tables and then commit both transactions - that way > > MSSQL will lock the required resources during the transaction (and can't > > slip in between the 2 sql queries). > > =the database transaction should not start until the second SELECT, for the reason stated. Most of the time > there will be (I assume the probability is v.low) no difference in the database between SELECTs. The only time > the user would be aware that what (s)he thinks of a transaction is not the same as the RDBMS' view! > > =dn > > > > > Or not? > > > > Ollie > > > > -----Original Message----- > > From: DL Neil [mailto:[EMAIL PROTECTED]] > > Sent: 02 February 2002 15:40 > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML > > Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a > > newbie question) > > > > > > Ollie, > > > > Probably easier to repeat the 'read' (from the first population of the user > > form) - and then do the > > comparison(s) in PHP - either way it will a SQL query that must be evaluated > > by PHP before the UPDATE is issued. > > > > ie put the interaction in a function and call it from the two locations in > > the code! That way you don't have > > essentially the same logic implemented in two places (fatal when you come to > > modify the code/db). > > > > Regards, > > =dn > > > > > > - Original Message - > > From: "Oliver Cronk" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]>; "Php-Db ML" <[EMAIL PROTECTED]> > > Sent: 01 February 2002 15:23 > > Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a > > newbie question) > > > > > > > Yes thats an interesting idea, and this compare-and-modify-if-unchanged > > bit > > > would be implemented via SQL IF statement(s) perhaps? > > > > > > Thanks > > > > > > Ollie > > > > > > "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message > > > news:<[EMAIL PROTECTED]>... > > > > > > > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > > > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > > 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). > > > > > > > > Just a thought - if you have the user form echo a copy of the original > > > data > > > > back (ie in addition to the modified data), you can compare it to the > > > > existing data and warn the user if the data has been changed in the > > > interim. > > > > You must make the [compare-and-modify-if-unchanged] atomic, but that's > > > okay, > > > > because it's all in the same script anyway - it becomes reasonable to do > > > it > > > > as a transaction. > > > > > > > > > > > > > > > > > > > > -- > > > 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] > > > > > -- 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]
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Ollie, First off, apologies, this is the first mention of MS-SQL (that I have noted). I am not 'up' on the latest versions/facilities offered. > Ah hang on just thought of a flaw in that - in between the second select, > compare and update is enough time for another user to slip in - so I will > still need some kind of logging. =Not too much so. If the entire table is locked (between the second SELECT and the UPDATE), how long will it be locked for? - and is that a major issue within your operating parameters? If it is locked, then there is 'no' time... > But it brings me nearer the solution! I think the solution is a combination > of the 2 - start a transaction do a second select but do the comparision in > SQL, then if all ok then start another transaction do the update do the > update(s) of the joined tables and then commit both transactions - that way > MSSQL will lock the required resources during the transaction (and can't > slip in between the 2 sql queries). =the database transaction should not start until the second SELECT, for the reason stated. Most of the time there will be (I assume the probability is v.low) no difference in the database between SELECTs. The only time the user would be aware that what (s)he thinks of a transaction is not the same as the RDBMS' view! =dn > Or not? > > Ollie > > -Original Message- > From: DL Neil [mailto:[EMAIL PROTECTED]] > Sent: 02 February 2002 15:40 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML > Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a > newbie question) > > > Ollie, > > Probably easier to repeat the 'read' (from the first population of the user > form) - and then do the > comparison(s) in PHP - either way it will a SQL query that must be evaluated > by PHP before the UPDATE is issued. > > ie put the interaction in a function and call it from the two locations in > the code! That way you don't have > essentially the same logic implemented in two places (fatal when you come to > modify the code/db). > > Regards, > =dn > > > - Original Message - > From: "Oliver Cronk" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; "Php-Db ML" <[EMAIL PROTECTED]> > Sent: 01 February 2002 15:23 > Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a > newbie question) > > > > Yes thats an interesting idea, and this compare-and-modify-if-unchanged > bit > > would be implemented via SQL IF statement(s) perhaps? > > > > Thanks > > > > Ollie > > > > "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message > > news:<[EMAIL PROTECTED]>... > > > > > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > 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). > > > > > > Just a thought - if you have the user form echo a copy of the original > > data > > > back (ie in addition to the modified data), you can compare it to the > > > existing data and warn the user if the data has been changed in the > > interim. > > > You must make the [compare-and-modify-if-unchanged] atomic, but that's > > okay, > > > because it's all in the same script anyway - it becomes reasonable to do > > it > > > as a transaction. > > > > > > > > > > > > > > -- > > 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] > > -- 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]
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Ah hang on just thought of a flaw in that - in between the second select, compare and update is enough time for another user to slip in - so I will still need some kind of logging. But it brings me nearer the solution! I think the solution is a combination of the 2 - start a transaction do a second select but do the comparision in SQL, then if all ok then start another transaction do the update do the update(s) of the joined tables and then commit both transactions - that way MSSQL will lock the required resources during the transaction (and can't slip in between the 2 sql queries). Or not? Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: "Oliver Cronk" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Php-Db ML" <[EMAIL PROTECTED]> Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) > Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit > would be implemented via SQL IF statement(s) perhaps? > > Thanks > > Ollie > > "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message > news:<[EMAIL PROTECTED]>... > > > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > 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). > > > > Just a thought - if you have the user form echo a copy of the original > data > > back (ie in addition to the modified data), you can compare it to the > > existing data and warn the user if the data has been changed in the > interim. > > You must make the [compare-and-modify-if-unchanged] atomic, but that's > okay, > > because it's all in the same script anyway - it becomes reasonable to do > it > > as a transaction. > > > > > > > > -- > 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]
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Thats the most elegant and probably easiest to implement, now I just need to figure out when to do a roll back I know the commands (COMMIT or ROLLBACK just don't know how to put it in my transaction - is it something like if(@@ERROR) ROLLBACK As I have read the manual (both MSSQL and PHP) but I am non- the wiser how to prevent an uncommited dependancy any quick example code you could provide would be really great or a T-SQL tutorial (I am familar with SQL just not the transaction bit). Thanks Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: "Oliver Cronk" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Php-Db ML" <[EMAIL PROTECTED]> Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) > Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit > would be implemented via SQL IF statement(s) perhaps? > > Thanks > > Ollie > > "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message > news:<[EMAIL PROTECTED]>... > > > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > 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). > > > > Just a thought - if you have the user form echo a copy of the original > data > > back (ie in addition to the modified data), you can compare it to the > > existing data and warn the user if the data has been changed in the > interim. > > You must make the [compare-and-modify-if-unchanged] atomic, but that's > okay, > > because it's all in the same script anyway - it becomes reasonable to do > it > > as a transaction. > > > > > > > > -- > 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]
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: "Oliver Cronk" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Php-Db ML" <[EMAIL PROTECTED]> Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) > Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit > would be implemented via SQL IF statement(s) perhaps? > > Thanks > > Ollie > > "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message > news:<[EMAIL PROTECTED]>... > > > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > 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). > > > > Just a thought - if you have the user form echo a copy of the original > data > > back (ie in addition to the modified data), you can compare it to the > > existing data and warn the user if the data has been changed in the > interim. > > You must make the [compare-and-modify-if-unchanged] atomic, but that's > okay, > > because it's all in the same script anyway - it becomes reasonable to do > it > > as a transaction. > > > > > > > > -- > 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]
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit would be implemented via SQL IF statement(s) perhaps? Thanks Ollie "Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>... > > "Oliver Cronk" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > 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). > > Just a thought - if you have the user form echo a copy of the original data > back (ie in addition to the modified data), you can compare it to the > existing data and warn the user if the data has been changed in the interim. > You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, > because it's all in the same script anyway - it becomes reasonable to do it > as a transaction. > > -- 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]
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
"Oliver Cronk" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > 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). Just a thought - if you have the user form echo a copy of the original data back (ie in addition to the modified data), you can compare it to the existing data and warn the user if the data has been changed in the interim. You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, because it's all in the same script anyway - it becomes reasonable to do it as a transaction. -- 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]
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Hi I don't think there can be a perfect solution, because you don't have a true persistent session between the user and the system. For the timeout watch a real user (or time yourself) and see how long it takes to do the update. If locking user2 out for any length of time causes problems go for a timeout close to the time it takes, and risk upsetting user1 sometimes. If user2 can easily go and do something else for five minutes set the timeout at 2 or 3 times the measured update time to allow for the slowest one finger typist! You could also log the system in use to see how many times user1's update gets refused, and adjust the timeout accordingly. I use a timeout system for restricted access user sessions, so I have most of the code you need, if you would like it. email me off list if you would like it. HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net tel. 0121-242-1473 --- > -Original Message- > From: Oliver Cronk [mailto:[EMAIL PROTECTED]] > Sent: 01 February 2002 08:30 > To: Peter Lovatt; [EMAIL PROTECTED] > Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a > newbie question) > > > 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 th
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
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 >
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]
[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]
