Re: 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" <PHPml@DandE.HomeChoice.co.uk> 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:PHPml@DandE.HomeChoice.co.uk] > > Sent: 02 February 2002 15:40 > > To: oliver@cronky.net; hugh_bothwell@hotmail.com; 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" <ocronk@essex.ac.uk> > > To: <hugh_bothwell@hotmail.com>; "Php-Db ML" <php-db@lists.php.net> > > 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" <hugh_bothwell@hotmail.com> wrote in message > > > > > > news:<20020201133107.1062.qmail@pb1.pair.com>... > > > > > > > > "Oliver Cronk" <ocronk@essex.ac.uk> wrote in message > > > > > > > > news:NGENLNMKBJNBIAFJLPBHOEMPCBAA.ocronk@essex.ac.uk... > > > > > 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: php-db-unsubscribe@lists.php.net > > > For additional commands, e-mail: php-db-help@lists.php.net > > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > > For additional commands, e-mail: php-db-help@lists.php.net > > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > > >