RE: [PHP-DB] Concurrent update to database (PostgreSQL or MySQL) ??
Thanks a lot for all these answers. The discussion between Doug and Thomas was really usefull. I think I'm gonna give a try to the timestamp/checksum method, which seems to best fit my needs. Best Regards, Nicolas -- 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] Concurrent update to database (PostgreSQL or MySQL) ??
Hi, How can I avoid two or more users to update the same record at the same time ? Ideally, I would like to lock the record when a user open it for modification. So if another user try to open the same record he'll be warned or get record values as read only. Is this possible and how to do it with PHP ?? How can I know that the user that has locked the record has finished with it, if he never commits his changes ? Is there an FAQ about this ? Regards, Nicolas -- 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] Concurrent update to database (PostgreSQL or MySQL) ??
This is a database, not a PHP question. Different databases have different locking/buffering schemes. So which are you using, and what do the documents say about locking? Do they lock records or pages? Generally you never attempt to lock a record until you are ready to write to it. In general terms, if this is a record that would be hit by a lot of users, here's a fairly foolproof method: Establish a loop which checks to see if the record is locked. This avoids failure of attempting to lock an already locked record. You may have to provide for timing out if the record is never freed. When record becomes free, lock it. Execute your write. Unlock the record. On the other hand. Hope this helps - Miles PS You almost never lock a record for a read. At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote: Hi, How can I avoid two or more users to update the same record at the same time ? Ideally, I would like to lock the record when a user open it for modification. So if another user try to open the same record he'll be warned or get record values as read only. Is this possible and how to do it with PHP ?? How can I know that the user that has locked the record has finished with it, if he never commits his changes ? Is there an FAQ about this ? Regards, Nicolas -- 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] Concurrent update to database (PostgreSQL or MySQL) ??
On 18 Apr 2001, at 13:47, Nicolas Guilhot wrote: Don't want to start another religion war, but... Hi, How can I avoid two or more users to update the same record at the same time ? Ideally, I would like to lock the record when a user open it for modification. So if another user try to open the same record he'll be warned or get record values as read only. Is this possible and how to do it with PHP ?? How can I know that the user that has locked the record has finished with it, if he never commits his changes ? Is there an FAQ about this ? Rather than a FAQ, you should find some good textbook on cuncurrent programming. The problem of concurrent updates of data is far too large to be tackled with a brief doc (and even less with a mail, I'm afraid). But to be short: Generally, Every DBMS out there can guarantee you that there will be no 2 users trying to update the same data together (i.e. querys are serialized). When it comes to multiple updates, however, or updates that touch different tables the problem gets harder, and the doubt you put in the subject can comport big difference in the way to handle it. In the MySQL paradigm, it's up to the programmer to guarantee data integrity at application level. In PostgreSQL (and in the rest of the DBMS world) paradigm, this work is still left to the DBMS system, thru isolated transactions. It's up to you to decide which way to go... /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [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] Concurrent update to database (PostgreSQL or MySQL) ??
As others have mentioned, this is a tricky thing to do. What others haven't mentioned is a way to do it. Let's say you just showed the update form to two users. It contains the fields that they are allowed to update and the values in those fields had been retreived from the database. Both users see a form like this: Today's High Temperature City: Detroit Temp (degrees F): 47 ( Submit ) The city field is not available for update (presumably the editor/user selected it off of a previous menu), and their task is to update today's high temperature for Detroit. One of the editors has information that the high temperature reached 49 degrees F, and the other editor has information that the high temperature has reached 50. (Perhaps one of the editors is overworked and hadn't gotten to enter the new high, or the temperature is changing very quickly.) So one of the people enter 50 and the other enters 49. Both hit submit. What needs to be done right at that moment to protect it from changing the value to 50 degrees (which is the correct, most recent, data) and then immediately changing the value to 49 degrees (which is now out of date)? Basically, you have to pass the original value to the script that is the ACTION of the HTML form. This way, you have the original value that may have been updated and the new value. The first thing the HTML form's ACTION script has to do is get the record from the database again. (Use SELECT ... FOR UPDATE if available, so the RDBMS might lock the row.) If the value you get from the SELECT is the same as the original value, go ahead and run the UPDATE to change the value to the new value. If not, then generate and display a screen telling the editor that the value has been updated by someone else...you can show the value and ask if they want to proceed with the update, but that's all up to you and/or your interface designers. This is all similar to how folks design old fashioned screen-oriented database systems (like with CICS). Good luck, Doug At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote: Hi, How can I avoid two or more users to update the same record at the same time ? Ideally, I would like to lock the record when a user open it for modification. So if another user try to open the same record he'll be warned or get record values as read only. Is this possible and how to do it with PHP ?? How can I know that the user that has locked the record has finished with it, if he never commits his changes ? Is there an FAQ about this ? Regards, Nicolas -- 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]
AW: [PHP-DB] Concurrent update to database (PostgreSQL or MySQL) ??
Another way is to keep another unique value inside the table to be updated and remember it. When needed, I add a second unique column to the table (in my case a char(64)) which is filled with the current timestamp and some md5 checksum. I select this value before the update, pass it along with the HTML form, and, before updating, I re-select the row to be updated and compare the keys. If the comparision fails, the user is presented a warning message, else I do the update (the user's data and a new generated stamp-value) with the primary key _and_ the original stamp in the where clause. Then I check if my new stamp made it to the table, or present another warning. A sample: Table atable: id int not null primary key stamp char(64) not null unique avalue int select id,stamp,avalue from atable where id=1 (select data for update) build html form with "id" and "stamp" as hidden values select stamp from atable where id=the_id if stamp(form) != stamp(db) error (e.g. start from beginning) else construct new_stamp update atable set avalue=new,stamp=new_stamp where id=the_id AND stamp=old_stamp select stamp from atable where id=the_id if stamp(db) != new_stamp error endif endif It's a bit of work, but it had never let me down. Thomas id INT NOT NULL AUTO_INCREMENT PRIMARY KEY Is what I use in the WHERE clause to update data ts CHAR(32) NOT NULL UNIQUE I -Ursprngliche Nachricht- Von: Doug Semig [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 18. April 2001 20:48 An: [EMAIL PROTECTED] Betreff: Re: [PHP-DB] Concurrent update to database (PostgreSQL or MySQL) ?? As others have mentioned, this is a tricky thing to do. What others haven't mentioned is a way to do it. Let's say you just showed the update form to two users. It contains the fields that they are allowed to update and the values in those fields had been retreived from the database. Both users see a form like this: Today's High Temperature City: Detroit Temp (degrees F): 47 ( Submit ) The city field is not available for update (presumably the editor/user selected it off of a previous menu), and their task is to update today's high temperature for Detroit. One of the editors has information that the high temperature reached 49 degrees F, and the other editor has information that the high temperature has reached 50. (Perhaps one of the editors is overworked and hadn't gotten to enter the new high, or the temperature is changing very quickly.) So one of the people enter 50 and the other enters 49. Both hit submit. What needs to be done right at that moment to protect it from changing the value to 50 degrees (which is the correct, most recent, data) and then immediately changing the value to 49 degrees (which is now out of date)? Basically, you have to pass the original value to the script that is the ACTION of the HTML form. This way, you have the original value that may have been updated and the new value. The first thing the HTML form's ACTION script has to do is get the record from the database again. (Use SELECT ... FOR UPDATE if available, so the RDBMS might lock the row.) If the value you get from the SELECT is the same as the original value, go ahead and run the UPDATE to change the value to the new value. If not, then generate and display a screen telling the editor that the value has been updated by someone else...you can show the value and ask if they want to proceed with the update, but that's all up to you and/or your interface designers. This is all similar to how folks design old fashioned screen-oriented database systems (like with CICS). Good luck, Doug At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote: Hi, How can I avoid two or more users to update the same record at the same time ? Ideally, I would like to lock the record when a user open it for modification. So if another user try to open the same record he'll be warned or get record values as read only. Is this possible and how to do it with PHP ?? How can I know that the user that has locked the record has finished with it, if he never commits his changes ? Is there an FAQ about this ? Regards, Nicolas -- 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]
